## 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]