Re: Enumerating a hierarchy?

SQL> select empno
  2 from emp
  3 where empno != 7369
  4 start with empno = 7369
  5 connect by prior mgr = empno;

     EMPNO

      7902
      7566
      7839

SQL> spool off;

HTH,
Eric Geng

"Brad McFarlane" <brad.mcfarlane_at_alcatel.com> 写入消息新闻 :3E639B01.5FE833D_at_alcatel.com...
> 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
Received on Tue Mar 04 2003 - 12:12:00 CST