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