Using CUBE to generate rows
When I find a cool trick like the one I am going to tell you about here, I immediatly post it on my blog so that I can find it later when I need it, and also share it with the rest of you. I just love such tricks. A few days ago, I talked about generating rows from DUAL; There is another way to generate rows, using CUBE.
So, how can CUBE generate rows? CUBE will produce POWER(2,x) records, where x is the number of dimensions listed in the parameterlist. For example:
select 1 from dual group by CUBE (1,2,3,4)
will generate 2 to the power of 4 = 2*2*2*2 = 16 rows. Note that CUBE (1,1,1,1) or CUBE (a,a,a,a) would give the same result; 1,2,3,4 explicitly shows you the number of records that will be returned.
Using this ‘row generator’ in an in-line view with where clause on rownum we can control exactly how many records are produced. This can be used in many ways. For example, list the days of the week (knowing that 21st november 2004 is a Sunday:
select to_char( days.day +
to_date('21-11-2004','DD-MM-YYYY'),
'DAY') day
from (
select rownum day
from (
select 1
from dual
group by cube (1,2,3)
)
where rownum <8) days
The result:
DAY
---------
MONDAY
TUESDAY
WEDNESDAY
THURSDAY
FRIDAY
SATURDAY
SUNDAY
Perhaps slightly more elegant is the following incarnation of this query:
with nums as (
select rownum num
from (
select 1 num
from dual
group by cube (1,2,3)
)
)
select to_char( days.day +
to_date('21-11-2004','DD-MM-YYYY'),
'DAY') day
from (
select num day
from nums
where num <8) days
Of course, the WITH clause used in the query above was introduced as a new feature in Oracle 9i. So, it won’t work in versions prior to 9i. (via)
Related articles:
Filed in Oracle, Tips on 07 Jul 05 | Tags: cube, function, sql
