## Integer Series Generators

### CUBE Method

This tutorial demonstrates how to generate a series of integers using the CUBE clause of the SELECT statement. Other techniques are discussed in the tutorials listed in the menu to the left.

Here are some examples that generate a series of integers using CUBE.

 To return 4 rows (2^2): ```select rownum from ( select 1 from dual group by cube( 1, 2 ) ) ;  ``` ``` ROWNUM ---------- 1 2 3 4  ``` To return 8 rows (2^3): ```select rownum from ( select 1 from dual group by cube( 1, 2, 3 ) ) ;  ``` ``` ROWNUM ---------- 1 2 3 4 5 6 7 8  ``` To return 16 rows (2^4): ```select rownum from ( select 1 from dual group by cube( 1, 2, 3, 4 ) ) ;  ``` ``` ROWNUM ---------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16  ``` To return 9 rows: ```select rownum from ( select 1 from dual group by cube( 1, 2, 3, 4 ) ) where rownum <= 9 ;  ``` ``` ROWNUM ---------- 1 2 3 4 5 6 7 8 9  ```

We can apply this technique to our day of the week scenario with this query.

```with days_of_the_week as
(
select rownum - 1 as day_of_week
from   ( select 1 from dual group by cube( 1, 2, 3 ) )
where  rownum <= 7
)
select
day_of_week ,
t.val
from
days_of_the_week
left outer join t
using ( day_of_week )
order by
day_of_week
;
```
```DAY_OF_WEEK        VAL
----------- ----------
0
1        100
2
3        300
4        400
5        500
6
```

For more details about how this method works see CUBE - Explained.

#### Gotchas

##### Number of Arguments in CUBE

Ensure the numeric literal in the WHERE clause is less than or equal to 2^(number of CUBE arguments), otherwise you will not get the correct number of rows, as in this example which attempts to generate 7 integers but only succeeds in generating 4.

```select
rownum as integer_value
from
(
select 1
from   t2
group by cube ( 1, 2 ) -- will only generate 2^2 rows
)
where
rownum <= 7 -- can only be <= 4, 3, 2, or 1 in this query, not 5, 6, 7, ...
;
```
```INTEGER_VALUE
-------------
1
2
3
4
```
##### Inline View

Attempting to use rownum without the inline view will cause errors or incorrect results.

```select rownum
from   t2
group by cube( 1, 2 ) ;
select rownum
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression

```
```select rownum
from   t2
group by rownum, cube( 1, 2 ) ;
```
```    ROWNUM
----------
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-11822.html]SQL Snippets: Integer Series Generators - CUBE Method[/url]