Integer Series Generators

CUBE Method - Explained

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.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11823.html]SQL Snippets: Integer Series Generators - CUBE Method - Explained[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11823.html">SQL Snippets: Integer Series Generators - CUBE Method - Explained</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Integer Series Generators - CUBE Method - Explained
  • URL (href): http://www.sqlsnippets.com/en/topic-11823.html