Need help with hierarchical query

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 Received on Mon May 24 2004 - 13:28:32 CDT