Re: transitive closure

Marshall Spight wrote:
> So in this case we have a definition of a relation that includes
> itself it its definition. (Hence "recursive." :-) I'm not sure
> I'm parsing that statement fully, though. I don't see anyplace
> where the type of "num" is declared. "Integers" is the name of
> the relation, yes? Is this recursion allowed only inside a
> "with" clause?
>
> Suggestions for further reading about with/recursive with?

There is a pretty good chapter in DB2 Cookbook by Graeme Birchall (available online).

> > There is a zillion of ways to generate integers in oracle, and I yet to
> > see a case that is handled by recursive "with" and not handled by
> > "connect by".
>
> Did you perhaps mean that in the other order?

Proving that "connect by" query is expressed via "recursive with" is easy:

select col1, col2, level from tab
start with <start_with_clause>
connect by <connect_by_clause>

is

with TC (col1, col2, level) as
( select col1, col2, 0 from tab
  where <start_with_clause>
   union all
  select col1, col2, TC.level+1 from TC, tab   where <connect_by_clause rewritten as join of TC and tab> )
select * from TC

There might be minor differences like the order of hierarchy traversal, but the idea that "recursive with" appears to be more powerful still holds. Received on Tue Jul 19 2005 - 20:12:08 CEST