Re: SELECT & recursion

From: Bart Wakker <bhawa_at_web.de>
Date: Mon, 20 Mar 2006 07:38:16 +0000
Message-ID: <slrne1smv8.j4d.bhawa@bhawa.web.de>

On 2006-03-19, Christopher Browne <cbbrowne_at_acm.org> wrote:

> Oracle has had a "CONNECT BY" structure to handle network/graph
> problems; it has never been standardized.

Which looks like this:

select * from hier_tab start with id=x
connect by prior id=parent

'prior id' means the one coming 'before' the child, which is the parent, i.e. in this fashion you search from child to parent.

you can also search from parent to all its descendants (children, grand children etc):

select * from hier_tab start with id=x
connect by prior parent=id

will give you the entire tree below some parent

I didn't know about the proposed recursive definition using WITH, I'll sure give that a try (have used oracles construct for several years). Received on Mon Mar 20 2006 - 01:38:16 CST