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.<\/p>\n
Here’s an example of a common table expression to generate a set of dates:<\/p>\n
with<\/span> dates as<\/span>
(
select<\/span> date<\/span> '2012-01-01'<\/span> as<\/span> DATE_VALUE from<\/span> dual
union<\/span> all<\/span>
select<\/span> date<\/span> '2012-01-02'<\/span> as<\/span> DATE_VALUE from<\/span> dual
union<\/span> all<\/span>
select<\/span> date<\/span> '2012-01-03'<\/span> as<\/span> DATE_VALUE from<\/span> dual
)
select<\/span> * from<\/span> dates;<\/pre>\n<\/div>\n
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.<\/p>\n
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:<\/p>\n
\nwith<\/span> dates as<\/span>
(
select<\/span> date<\/span> '2012-01-01'<\/span> as<\/span> DATE_VALUE from<\/span> dual
union<\/span> all<\/span>
select<\/span> d.DATE_VALUE + interval<\/span> '1'<\/span> day<\/span> as<\/span> DATE_VALUE
from<\/span> dates d
where<\/span> d.DATE_VALUE < date<\/span> '2013-01-01'<\/span>
)
select<\/span> * from<\/span> dates;
<\/pre>\n<\/div>\nIn 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.<\/p>\n
The first time I tried this technique, I got the following error:<\/p>\n
ORA-32039: recursive WITH clause must have column alias list<\/font> <\/p>\nSome 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: <\/p>\n
\nwith<\/span> dates ( DATE_VALUE ) as<\/span>
(
select<\/span> date<\/span> '2012-01-01'<\/span> as<\/span> DATE_VALUE from<\/span> dual
union<\/span> all<\/span>
select<\/span> d.DATE_VALUE + interval<\/span> '1'<\/span> day<\/span> as<\/span> DATE_VALUE
from<\/span> dates d
where<\/span> d.DATE_VALUE < date<\/span> '2013-01-01'<\/span>
)
select<\/span> * from<\/span> dates;
<\/pre>\n<\/div>\n
And now, it just works:<\/div>\n<\/p>\n\nSQL>
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
:<\/pre>\n<\/div>\n
Aside<\/strong>: Here’s how to do the same query in PostgreSQL:<\/p>\n\nwith<\/span> recursive<\/span> dates ( DATE_VALUE ) as<\/span>
(
select<\/span> date<\/span> '2012-01-01'<\/span> as<\/span> DATE_VALUE
union<\/span> all<\/span>
select<\/span> cast<\/span>(d.DATE_VALUE + interval<\/span> '1'<\/span> day<\/span> as<\/span> date<\/span>) as<\/span> DATE_VALUE
from<\/span> dates d
where<\/span> d.DATE_VALUE < date<\/span> '2013-01-01'<\/span>
)
select<\/span> * from<\/span> dates; <\/pre>\n<\/div>\n
The nice thing about this technique is that we can control the end date, and the increment size, by variables: <\/p>\n
\ndefine _increment = '5'<\/span>;
define _start = date<\/span> '2012-01-01'<\/span>;
define _end = date<\/span> '2012-02-01'<\/span>;
with<\/span> dates ( DATE_VALUE ) as<\/span>
(
select<\/span> &_start as<\/span> DATE_VALUE from<\/span> dual
union<\/span> all<\/span>
select<\/span> d.DATE_VALUE + interval<\/span> '&_increment'<\/span> day<\/span> as<\/span> DATE_VALUE
from<\/span> dates d
where<\/span> d.DATE_VALUE < &_end
)
select<\/span> * from<\/span> dates;
<\/pre>\n<\/div>\n
This gives us a list of dates 5 days apart.<\/p>\n<\/p>\n
Using this technique in a PL\/SQL block<\/h3>\n
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: <\/p>\n
\ndeclare<\/span>
p_start_time date<\/span>;
p_interval_days integer<\/span>;
p_end_date date<\/span>;
--<\/span>
l_interval interval day to second;<\/span>
begin<\/span>
l_interval := numtodsinterval( p_interval_days, 'day'<\/span> );
insert into<\/span> My_List_Of_Dates
with<\/span>
dates ( DATE_VALUE ) as<\/span>
(
select<\/span>
p_start_time as<\/span> DATE_VALUE
from<\/span> dual
union<\/span> all<\/span>
select<\/span>
DATE_VALUE + l_interval as<\/span> DATE_VALUE
from<\/span> dates d
where<\/span> d.END_TIME < p_end_date
)
select<\/span> DATE_VALUE from<\/span> dates;
end<\/span>;<\/pre>\n<\/div>\n<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,16],"tags":[],"class_list":["post-16","post","type-post","status-publish","format-standard","hentry","category-oracle","category-sql-syntax","post-preview"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts\/16","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/comments?post=16"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts\/16\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/media?parent=16"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/categories?post=16"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/tags?post=16"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}