Tuesday, 15 January 2008

Oracle: CUBE

ROLLUP and CUBE are extensions to the GROUP BY functionality of the Select statement. The following select statement demonstrates basic use of CUBE:

select soh_cucode, soh_owner, sum(soh_net) from guser.f_sohdr
where soh_date between '01 JUN 2007' and '30 JUN 2007'
group by cube (soh_cucode, soh_owner)

This statement will give output that looks like the following:

The output is very similar to the output from ROLLUP, with the exception that CUBE also performs a subtotal by Salesperson at the end of the results set, followed by the total for all Customers, the same as the ROLLUP option.

Between them CUBE and ROLLUP add an extra an very useful dimension to your SQL reporting toolkit. For more information or to explore the options further, consult the Oracle documentation.

Software Development * Security * Database * Technology * Training * Networking * Virtualisation
Technology Consultancy from GENeSYS Solutions

"What counts is not necessarily the size of the dog in the fight - it's the size of the fight in the dog." - Dwight D Eisenhower

No comments: