"Mike Carroll" <mcarroll (AT) pobox (DOT) com> wrote:
It is called "WITH", and was first implemented in the SQL standard
fashion in DB2. Most SQL implementations don't support it yet...

The notion is thus...

WITH recursive_tab (col1, col2, col3) as
(select col1, col2, col3 from some_table root
where [conditions indicating "root" nodes]

union all

select children.col1, children.col2, children.col3
from some_table children, recursive_tab
where [conditions that connect children to elements already in
recursive_tab]
)
select * from recursive_tab;

Notice that in the second subquery, recursive_tab refers to itself;
that's where the recursion takes place.

This is very much like the LET form found in Lisp and ML, and could
presumably be used nonrecursively to short-hand initializations for
queries...

Oracle has had a "CONNECT BY" structure to handle network/graph
problems; it has never been standardized.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/slony.html
"...make -k all to compile everything in the core distribution. This
will take anywhere from 15 minutes (on a Cray Y-MP) to 12 hours."
-- X Window System Release Notes

On 2006-03-19, Christopher Browne <cbbrowne (AT) acm (DOT) org> wrote:
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).

Bart Wakker wrote:
[...]


You are confused -- the way 'connect by' behaves in your examples is
just the opposite to what you've described.

On 2006-03-21, vc <boston103 (AT) hotmail (DOT) com> wrote:

Indeed.

The DB2 solution using WITH, doesn't work for oracle b.t.w.: the result is
ORA-32031: illegal reference of a query name in WITH clause.

Bart Wakker wrote:
That's because Oracle does not implement the standard recursive query
(a la DB2).