SQL Snippets ™: Tutorials for Oracle Developers

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.

select rownum
from
(
  select 1
  from   dual
  group by cube( 1, 2 )
) ;
 

    ROWNUM
----------
         1
         2
         3
         4
 

select rownum
from
(
  select 1
  from   dual
  group by cube( 1, 2, 3 )
) ;
 

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
 

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
 

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