Re: Need help with hierarchical query

From: VC <boston103_at_hotmail.com>
Date: Tue, 25 May 2004 22:57:16 GMT
Message-ID: <gbQsc.115458$xw3.6806658@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

"Phil Bewig" <pbewig_at_swbell.net> wrote in message news:455f7154.0405250718.51b0700_at_posting.google.com... > "Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:<40b24e21$0$3033$636a15ce_at_news.free.fr>...
> > "Phil Bewig" <pbewig_at_swbell.net> a écrit dans le message de
> > news:455f7154.0405241028.50b6ce2c_at_posting.google.com...
> > >
> > > 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?

> >

> > Cut up the SYS_CONNECT_BY_PATH string.

>
> That was my first thought.  But the problem is that the hierarchical query
> returns too many records.  I think the solution will likely involve some

set
> of nested sub-queries, one nesting for each level of the hierarchy, but I'm

> having trouble working out the details.
>
> Phil

Received on Tue May 25 2004 - 17:57:16 CDT