Re: Need help with hierarchical query

"VC" <boston103_at_hotmail.com> a écrit dans le message de news:gbQsc.115458$xw3.6806658_at_attbi_s04...
> Hi Phil,
>
> Well, it's rather simple. What Michel meant was probably this:
>
> select employee_id, last_name,
> substr(path, 2, instr(path, '.', 1, 2) - 2)
> level1,
> substr(path, instr(path, '.', 1, 2) + 1, instr(path, '.', 1, 3) -
> instr(path, '.', 1, 2) - 1) level2,
> substr(path, instr(path, '.', 1, 3) + 1, instr(path, '.', 1, 4) -
> instr(path, '.', 1, 3) - 1) level3,
> substr(path, instr(path, '.', 1, 4) + 1, instr(path, '.', 1, 5) -
> instr(path, '.', 1, 4) - 1) level4,
> substr(path, instr(path, '.', 1, 5) + 1, instr(path, '.', 1, 6) -
> instr(path, '.', 1, 5) - 1) level5
> from (
> select employee_id, last_name, sys_connect_by_path(employee_id,'.')||'.'
> path from employees
> connect by prior employee_id=manager_id
> start with manager_id is null
> )
> order by 1
>
>
> -- result:
>
> EMPLOYEE_ID LAST_NAME LEVEL1 LEVEL2 LEVEL3 LEVEL4 LEVEL5
> 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
>
> ... et cetera.
>
> VC
>

Yes this was exactly what i meant but i had no time to make a test.

Thanks
Michel Cadot Received on Tue May 25 2004 - 23:59:09 CDT