Re: How to get all ancestors

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 28 Apr 2004 09:01:18 +0200
Message-ID: <408f56d4$0$20151$636a15ce@news.free.fr>

"Steve" <sprobst_at_yahoo.com> a écrit dans le message de news:ddc1a9fc.0404271229.23bcccc5_at_posting.google.com...
> I am new to the use of "connect by prior". I have a standard parent
> child table (id, parentId) where the data graphically might look like
> this:
>
> Great Grandpa
> ...Grandpa
> ......Dad
> .........Son
>
> Using connect by prior (or otherwise), I would like to find all the
> ancestors nodes for son (grouped by son). I don't want the tree
> flattened into one row as SYS_CONNECT_BY_PATH does.
>
> Rather, I would like the resultset grouped:
> -------------------------------------
> Son Dad
> Son Grandpa
> Son Great Grandpa
>
> Is there a way to do this with 'connect by prior'?

For instance,

SQL> l
  1 select substr(sys_connect_by_path(last_name,'/'),2,

  2                instr(sys_connect_by_path(last_name,'/'),'/',2)-2) son,
  3         last_name parent, level-1 "LEVEL", sys_connect_by_path(last_name,'/') hierarchy

  4 from employee
  5 where level>1
  6 connect by employee_id = prior manager_id   7* start with last_name='DOUGLAS'
SQL> /
SON PARENT LEVEL HIERARCHY

--------------- --------------- ---------- ------------------------------
DOUGLAS         FISHER                   1 /DOUGLAS/FISHER
DOUGLAS         ALBERTS                  2 /DOUGLAS/FISHER/ALBERTS
DOUGLAS         KING                     3 /DOUGLAS/FISHER/ALBERTS/KING

3 rows selected.

Regards
Michel Cadot Received on Wed Apr 28 2004 - 02:01:18 CDT