Re: Need help with hierarchical query

"Phil Bewig" <pbewig_at_swbell.net> a écrit dans le message de news:455f7154.0405241028.50b6ce2c_at_posting.google.com...
> I need help writing a particular type of hierarchical query. Using the
> EMPLOYEES table from Oracle as a sample, I want a select statement that
> returns one row for each row in the employees table showing the entire
> hierarchy for that row:
>
> eid last_name lvl1 lvl2 lvl3 lvl4 lvl5
> --- --------- ---- ---- ---- ---- ----
> 100 King 100 null null null null
> 101 Kochhar 100 101 null null null
> 102 De Haan 100 102 null null null
> 103 Hunold 100 102 103 null null
> 104 Ernst 100 102 103 104 null
> 105 Austin 100 102 103 105 null
> 106 Pataballa 100 102 103 106 null
> 107 Lorentz 100 102 103 107 null
> 108 Greenberg 100 101 108 null null
> 109 Faviet 100 101 108 109 null
> 110 Chen 100 101 108 110 null
> ...
> a total of 107 records
>
> What I am trying to do seems similar to the SYS_CONNECT_BY_PATH string
> that Oracle provides, but I need to put the elements of the hierarchy
> into fields, not a string. I also note that queries using CONNNECT BY
> PRIOR return one row for each input row at each level of the hierarchy,
> which is not what I need.
>
> This sounds simple, but I am stumped. Can anyone help?
>
> Phil

Cut up the SYS_CONNECT_BY_PATH string.

Regards
Michel Cadot Received on Mon May 24 2004 - 14:34:51 CDT