Recursive WITH Clauses

I’ve known about the “WITH” clause for quite a while, but today I learned that it’s significantly more powerful than I had thought.

For the uninitiated: the WITH clause allows you to define an inline view before your SELECT clause, rather than buried in your SQL. I use this frequently because it helps keep my SELECT/FROM/WHERE clauses from looking too ugly, and is also useful if you need to reference the same inline view multiple times. A very simple (and pointless) example:

with t as ( select sysdate as now from dual )
select t.now from t;

Today, I was trying to build a query which would traverse a tree (stored as many parent/child records in a table) and calculate a sum based on two of the columns. My first implementation used a simple recursive PL/SQL function, but this failed once the tree reached more than 50 levels. My next thought was to use hierarchical SQL, but I was at a loss for how to get a running total per level; Oracle doesn’t seem to have a function for that (the best I could think of was SYS_CONNECT_BY_PATH, with works well if you want to concatenate strings, but doesn’t support math on numbers).

Then I stumbled upon a solution that seemed to work, but which looked like it shouldn’t. The query used a WITH clause to define an inline view, but the inline view referenced itself. I’d never seen such a thing. It turns out that this was added in 11gR2, and that adding column aliases to the WITH clause allows for a recursive definition.

Here’s the SQL I ended up with (slightly obfuscated):

with dt_hier ( child_id, parent_id, start_offset, due_offset )
as (
    select d.id child_id,
        d.parent_id,
        d.start_offset,
        d.due_offset
    from date_table d
    where d.parent_id is null
    union all
    select d.id child_id,
        d.parent_id,
        dh.start_offset + dh.due_offset start_offset,
        d.due_offset
    from date_table d,
        dt_hier dh
    where dh.child_id = d.parent_id
)
select child_id, parent_id, start_offset, due_offset
from dt_hier

In this example, the dt_hier inline view references itself recursively. You can see in the first line that I had to proactively declare aliases for the columns in the view; if you don’t, you get a “ORA-32039: recursive WITH clause must have column alias list” error.

Note that, like all good recursive code, there are two paths–a base case and the recursive one (which eventually terminates in the base case). And the data has to be acyclic; I’m not sure what happens if you try to use this on data that loops back on itself, but I can’t imagine it giving you an answer that makes sense.