Re: transitive closure

From: Marshall Spight <>
Date: 19 Jul 2005 10:32:02 -0700
Message-ID: <>

Mikito Harakiri wrote:
> It's a hack that happens to work OK. One question that has been
> repeatedly asked (but didn't seem to trigger much of an interest) is
> whether its expresive power is equivalent to recursive "with". The
> latter has an advantage more easily extending the relation with new
> pseudocolumns. Look at this integer generator, for example
> with Integers (num) as
> ( values(1)
> union all
> select num+1 from Integers
> where num < 100
> )
> select * from Integers


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 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?

Marshall Received on Tue Jul 19 2005 - 19:32:02 CEST

Original text of this message