Re: Recursive tree structure in Oracle

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<gcednXy8Atck3_bdRVn-tA_at_comcast.com>...
> "D" <bbcrock_at_hotmail.com> wrote in message
> news:22e171df.0403310912.5f98baa3_at_posting.google.com...
> | I am well aware of Joe Celko's solution, but I have an existing
> | ColdFusion application where the old DBA put the parentid in the table
> | with the main record.
> |
> | you know:
> | employeeid
> | managerid
> | etc
> |
> | I need to run a query that will run once a week to create a listing of
> | all these relationships. I would prefer to write this in SQL,
> | possibly a PL/SQL SP but could write it in ColdFusion as well. I
> | don't know the pitfalls of trying this. I need this done by the end
> | of the week, so I don't have a lot of time here and can't initiate a
> | change to the legacy system.
> |
> | any takers?
> |
> | thanks,
> |
> | Don
>
>
> unless i'm missing something, this is exactly what oracle's CONNECT BY ...
> START WITH hierarchical query syntax is there for
>
> ie,
>
> select ...
> from ...
> where prior employeeid = managerid
> start with ...
>
>
> ;-{ mcs

And if you look in the (version 9.2) SQL manual there is a new function sys_connect_by_path that may be of interest depending on how you want to format the results.

HTH -- Mark D Powell -- Received on Thu Apr 01 2004 - 20:04:53 CEST