SQL Snippets ™: Tutorials for Oracle Developers

To understand how the integer series generator described in the CUBE Method tutorial works we will start with a simple query, transform it into a query that uses CUBE the traditional way, and then turn it into an integer series generator for the values 1 to 4. (Read the Oracle manual page on the CUBE grouping operation first if you are not already familiar with this feature.)

set null "(null)"
 

select *
from   t2 ;
 

C1     C2             C3
------ ------ ----------
x      y              42
 

select c1, c2, sum( c3 ) as sum_c3
from   t2
GROUP BY C1, C2 ;
 

C1     C2         SUM_C3
------ ------ ----------
x      y              42
 

select c1, c2, sum( c3 ) as sum_c3
from   t2
group by CUBE( c1, c2 );
 

C1     C2         SUM_C3
------ ------ ----------
(null) (null)         42
(null) y              42
x      (null)         42
x      y              42
 

select c1, c2, 1 AS ANY_LITERAL
from   t2
group by cube( c1, c2 );
 

C1     C2     ANY_LITERAL
------ ------ -----------
(null) (null)           1
(null) y                1
x      (null)           1
x      y                1
 

SELECT 1
from   t2
group by cube ( c1, c2 ) ;
 

         1
----------
         1
         1
         1
         1
 

select 1
from   t2
group by CUBE ( 1, 2 ) -- see Note 1
;
 

         1
----------
         1
         1
         1
         1
 

select
  ROWNUM AS INTEGER_VALUE
from
( select 1
  from   t2
  group by cube ( 1, 2 )
);
 

INTEGER_VALUE
-------------
            1
            2
            3
            4
 

select
  rownum as integer_value
from
(
  select 1
  from   t2
  group by cube ( 1, 2 )
)
where
  ROWNUM <= 3 ;
 

INTEGER_VALUE
-------------
            1
            2
            3
 

Note 1: In this technique it does not matter what literals you use in the arguments to CUBE. You could use arguments like 1, 1 or 'a','b' and still get the same number of rows. The important part is how many literals you include.

Two literals will give you four rows (2^2), three literals will give you eight rows (2^3), four literals will give you sixteen rows (2^4), etc. I like to use arguments like 1,2,3,4,5,6,7 because it is easier to tell there are 7 arguments (which produce 2^7 rows) with this approach than with an argument list like 1,1,1,1,1,1,1.