Re: Enumerating a hierarchy?

> I have a hierarchical data structure represented in Oracle (8i,
> 8.1.7.0.0), and I've been playing with hierarchical queries using
> CONNECT BY and START WITH, but they don't quite cover what I want.
>
> In particular, I'd like to have a query that gave me the full
> enumeration of the containment of an object, i.e., its "owner" (e.g.,
> manager), the owner's owner, the owner's owner's owner, and so on, all
> the way up to the top-level owner.
>
> Using the "emp" SQL example table in the Oracle manual (Oracle8 Server
> SQL Reference), I'd like a query that, when written (something like
> this):
>
> select
> empno, mgr_no
> from
> group_enumeration
> where
> empno = 7369;
>
> gave:
>
> 7369 7902
> 7369 7566
> 7369 7839
>
> where "group_enumeration" was some table/view (PL/SQL?) that did the
> enumeration.
>
> I've seen that Oracle 9i has something called "SYS_CONNECT_BY_PATH" that
> looks like it does what I want, except it's 9i, and it comes back as a
> delimited string, rather than a set of records.
>
> Any help would be appreciated.
>
> Thanks,
> Brad

Hi Brad

I don't have an 8i at hand, so I am not 100% sure if this works in your environment. It works with 9i. Anyway, try it out. If there is an improvement, let me know.

create global temporary table stack(lvl number, val number) on commit delete rows;

create table group_enumeration (empno number(4), mgr_no number(4));

begin

  for r in (
    select
      level, empno, mgr
    from
      emp
    start with mgr is null
    connect by prior empno=mgr)
  loop

    delete from stack where lvl >= r.level;

    insert into group_enumeration select       r.empno,val from stack;

    insert into stack values(r.level,r.empno);

  end loop;

end;
/

select
  empno, mgr_no
from
  group_enumeration
where
  empno = 7369;

hth
Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html