Category: SQL Syntax

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;

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;

I found that I needed to convert some raw column names (i.e. THIS_IS_MY_COLUMN) into a human-readable “Proper” or “Title” case form, e.g. This Is My Column.

Scouring the web, I didn’t find out I could steal, so here’s my implementation:

   function Proper(
      p_string varchar2
   ) return varchar2
   is
      l_string varchar2(200);
      l_proper varchar2(200);   
   begin
      l_proper := '';
      -- change any underscores to spaces:
      l_string := replace(lower(p_string), '_',' ' );

      for i in 1..length(l_string)
      loop
         -- obviously the first character is UPPER:
         if i =1 then
            l_proper := l_proper || upper( substr( l_string, i, 1 ) );
         else
         -- if the character is preceded by a space, UPPER it:
            if substr( l_string, i-1, 1 ) = ' ' then
               l_proper := l_proper || upper( substr( l_string, i, 1 ) );
            else
               l_proper := l_proper || substr( l_string, i, 1 );
            end if;            
         end if;   
      end loop;
      return l_proper;
   end;