pink cylinders

More than Four

...there's an axis for that.

Using recursive Common Table Expressions to generate lists

Say we want create a generated list of values - sequential dates, for example - as a set of rows. We can do this using recursive common table expressions.

Here's an example of a common table expression to generate a set of dates:

with dates as
(
select date '2012-01-01' as DATE_VALUE from dual
union all
select date '2012-01-02' as DATE_VALUE from dual
union all
select date '2012-01-03' as DATE_VALUE from dual
)
select * from dates;

Using this technique, we could create a set of rows with sequential date values... but it is not very flexible. Ideally, we want a technique that expresses the intent algorithmically.

In 11g R2 and later, we can use a reference to the outer table recursively from inside the definition! This sounds crazy but it works:

with dates as
(
select date '2012-01-01' as DATE_VALUE from dual
union all
select d.DATE_VALUE + interval '1' day as DATE_VALUE
from dates d
where d.DATE_VALUE < date '2013-01-01'
)
select * from dates;

In this example, we are UNIONING the first row with a sub-select against itself, adding a new row that increments the date value by 1.

The first time I tried this technique, I got the following error:

ORA-32039: recursive WITH clause must have column alias list

Some research on the web gave me a page that explained what the problem was. The error message is actually very clear: we need to add a list of columns at the top of the WITH <alias> clause:

with dates ( DATE_VALUE ) as
(
select date '2012-01-01' as DATE_VALUE from dual
union all
select d.DATE_VALUE + interval '1' day as DATE_VALUE
from dates d
where d.DATE_VALUE < date '2013-01-01'
)
select * from dates;

And now, it just works:
 
SQL>
DATE_VALUE
-----------
2012-01-01
2012-01-02
2012-01-03
2012-01-04
2012-01-05
2012-01-06
2012-01-07
2012-01-08
2012-01-09
2012-01-10
2012-01-11
:

Aside: Here's how to do the same query in PostgreSQL:

with recursive dates ( DATE_VALUE ) as
(
select date '2012-01-01' as DATE_VALUE
union all
select cast(d.DATE_VALUE + interval '1' day as date) as DATE_VALUE
from dates d
where d.DATE_VALUE < date '2013-01-01'
)
select * from dates;

The nice thing about this technique is that we can control the end date, and the increment size, by variables:

define _increment = '5';
define _start = date '2012-01-01';
define _end = date '2012-02-01';

with dates ( DATE_VALUE ) as
(
select &_start as DATE_VALUE from dual
union all
select d.DATE_VALUE + interval '&_increment' day as DATE_VALUE
from dates d
where d.DATE_VALUE < &_end
)
select * from dates;

This gives us a list of dates 5 days apart.

 

Using this technique in a PL/SQL block

If you're in a PL/SQL block, trying to generate a list dynamically based on parameter values, the code will need to look slightly different:

declare
p_start_time date;
p_interval_days integer;
p_end_date date;
--
l_interval interval day to second;
begin
l_interval := numtodsinterval( p_interval_days, 'day' );
insert into My_List_Of_Dates
with
dates ( DATE_VALUE ) as
(
select
p_start_time as DATE_VALUE
from dual
union all
select
DATE_VALUE + l_interval as DATE_VALUE
from dates d
where d.END_TIME < p_end_date
)
select DATE_VALUE from dates;
end;

 

 


 

Add comment

Loading