Home » SQL & PL/SQL » SQL & PL/SQL » Rebuild tree with fullpath - connect by (?) (11gR2)
Rebuild tree with fullpath - connect by (?) [message #618996] Thu, 17 July 2014 04:13 Go to next message
ric90
Messages: 42
Registered: May 2011
Location: Belfort
Member
Hi all,

I'm looking for a way to rebuild tree with full path.
With a table containing ID, PARENT_ID, NAME is it easy to build fullpath

SELECT empno,ename,SYS_CONNECT_BY_PATH(ename, '/') root
FROM employee
START_WITH empno=100
CONNECT_BY NOCYCLE prior empno = mgr
/
     EMPNO ENAME      ROOT
---------- ---------- --------------------
       100 JOHN       /JOHN
       110 KATE       /JOHN/KATE
       160 KING       /JOHN/KATE/KING
       180 HICK       /JOHN/KATE/KING/HICK
       170 SUEZ       /JOHN/KATE/SUEZ
       120 MILLER     /JOHN/MILLER
       140 LARRY      /JOHN/MILLER/LARRY
       150 EDWIN      /JOHN/MILLER/EDWIN
       130 JOHNY      /JOHN/JOHNY
source http://psoug.org/definition/SYS_CONNECT_BY_PATH.htm



But how about retrieve parent_id only with path ?

WITH t as(
select 100 id, '/JOHN' path from dual UNION ALL 
select 110 id, '/JOHN/KATE' path from dual UNION ALL 
select 160 id, '/JOHN/KATE/KING' path from dual UNION ALL 
select 180 id, '/JOHN/KATE/KING/HICK' path from dual UNION ALL 
select 170 id, '/JOHN/KATE/SUEZ' path from dual UNION ALL 
select 120 id, '/JOHN/MILLER' path from dual UNION ALL 
select 140 id, '/JOHN/MILLER/LARRY' path from dual UNION ALL 
select 150 id, '/JOHN/MILLER/EDWIN' path from dual UNION ALL 
select 130 id, '/JOHN/JOHNY' path from dual)
Select * from t ....


How to retrieve the parent for each line .... ?

Thank you for helping.
Re: Rebuild tree with fullpath - connect by (?) [message #619002 is a reply to message #618996] Thu, 17 July 2014 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> WITH t as(
  2  select 100 id, '/JOHN' path from dual UNION ALL 
  3  select 110 id, '/JOHN/KATE' path from dual UNION ALL 
  4  select 160 id, '/JOHN/KATE/KING' path from dual UNION ALL 
  5  select 180 id, '/JOHN/KATE/KING/HICK' path from dual UNION ALL 
  6  select 170 id, '/JOHN/KATE/SUEZ' path from dual UNION ALL 
  7  select 120 id, '/JOHN/MILLER' path from dual UNION ALL 
  8  select 140 id, '/JOHN/MILLER/LARRY' path from dual UNION ALL 
  9  select 150 id, '/JOHN/MILLER/EDWIN' path from dual UNION ALL 
 10  select 130 id, '/JOHN/JOHNY' path from dual)
 11  select -- path,
 12         substr(path, instr(path, '/', -1)+1) ename,
 13         decode(instr(path, '/', -1), 1,null,
 14                substr(path, instr(path,'/',-1,2)+1, instr(path,'/',-1)-instr(path,'/',-1,2)-1)
 15               ) mgr
 16  from t
 17  /
ENAME                MGR
-------------------- --------------------
JOHN
KATE                 JOHN
KING                 KATE
HICK                 KING
SUEZ                 KATE
MILLER               JOHN
LARRY                MILLER
EDWIN                MILLER
JOHNY                JOHN

Re: Rebuild tree with fullpath - connect by (?) [message #619004 is a reply to message #619002] Thu, 17 July 2014 04:55 Go to previous messageGo to next message
ric90
Messages: 42
Registered: May 2011
Location: Belfort
Member
Thank you for your reply.
In fact, i am looking for the id of each mgr...
I was trying with connect by, but maybe it is not necessary...
Re: Rebuild tree with fullpath - connect by (?) [message #619008 is a reply to message #619004] Thu, 17 July 2014 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Try to do it from what I posted.
Hint: add id to the query I gave, you then have the id of all employee and so to get mgr id from his name...

Re: Rebuild tree with fullpath - connect by (?) [message #619028 is a reply to message #619008] Thu, 17 July 2014 08:06 Go to previous messageGo to next message
ric90
Messages: 42
Registered: May 2011
Location: Belfort
Member
Not sure that the connect by is really necessary, but it works too
WITH t as(
select 100 id, 'JOHN' path from dual UNION ALL 
select 110 id, 'JOHN/KATE' path from dual UNION ALL 
select 160 id, 'JOHN/KATE/KING' path from dual UNION ALL 
select 180 id, 'JOHN/KATE/KING/HICK' path from dual UNION ALL 
select 170 id, 'JOHN/KATE/SUEZ' path from dual UNION ALL 
select 120 id, 'JOHN/MILLER' path from dual UNION ALL 
select 140 id, 'JOHN/MILLER/LARRY' path from dual UNION ALL 
select 150 id, 'JOHN/MILLER/EDWIN' path from dual UNION ALL 
select 130 id, 'JOHN/JOHNY' path from dual)
Select t2.id root_id, t1.id, root_path, t1.path  from(select
substr(path, 1,instr(path,'/', -1, level)-1)  root_path,  id,level, path from t 
connect by  path = substr(path, 1,instr(path,'/', -1, level)-1)) t1, 
t t2
where t2.path=t1.root_path;
Re: Rebuild tree with fullpath - connect by (?) [message #619030 is a reply to message #619028] Thu, 17 July 2014 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With my prior query, with the same columns then employee table you can do:
SQL> WITH t as(
  2  select 100 id, '/JOHN' path from dual UNION ALL 
  3  select 110 id, '/JOHN/KATE' path from dual UNION ALL 
  4  select 160 id, '/JOHN/KATE/KING' path from dual UNION ALL 
  5  select 180 id, '/JOHN/KATE/KING/HICK' path from dual UNION ALL 
  6  select 170 id, '/JOHN/KATE/SUEZ' path from dual UNION ALL 
  7  select 120 id, '/JOHN/MILLER' path from dual UNION ALL 
  8  select 140 id, '/JOHN/MILLER/LARRY' path from dual UNION ALL 
  9  select 150 id, '/JOHN/MILLER/EDWIN' path from dual UNION ALL 
 10  select 130 id, '/JOHN/JOHNY' path from dual),
 11  data as (
 12  select id,-- path, 
 13         substr(path, instr(path, '/', -1)+1) ename,
 14         decode(instr(path, '/', -1), 1,null,
 15                substr(path, instr(path,'/',-1,2)+1, instr(path,'/',-1)-instr(path,'/',-1,2)-1)
 16               ) mgrname
 17  from t)
 18  select a.id, a.ename, b.id mgr
 19  from data a, data b
 20  where b.ename (+) = a.mgrname
 21  order by 1
 22  /
        ID ENAME             MGR
---------- ---------- ----------
       100 JOHN
       110 KATE              100
       120 MILLER            100
       130 JOHNY             100
       140 LARRY             120
       150 EDWIN             120
       160 KING              110
       170 SUEZ              110
       180 HICK              160

Re: Rebuild tree with fullpath - connect by (?) [message #619035 is a reply to message #618996] Thu, 17 July 2014 08:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
WITH t as(
          select 100 id, '/JOHN' path from dual UNION ALL 
          select 110 id, '/JOHN/KATE' path from dual UNION ALL 
          select 160 id, '/JOHN/KATE/KING' path from dual UNION ALL 
          select 180 id, '/JOHN/KATE/KING/HICK' path from dual UNION ALL 
          select 170 id, '/JOHN/KATE/SUEZ' path from dual UNION ALL 
          select 120 id, '/JOHN/MILLER' path from dual UNION ALL 
          select 140 id, '/JOHN/MILLER/LARRY' path from dual UNION ALL 
          select 150 id, '/JOHN/MILLER/EDWIN' path from dual UNION ALL 
          select 130 id, '/JOHN/JOHNY' path from dual
         )
select  t1.id,
        substr(t1.path,instr(t1.path,'/',-1) + 1) ename,
        t2.id mgr
  from  t t1,
        t t2
  where t2.path(+) = substr(t1.path,1,instr(t1.path,'/',-1) - 1)
  order by t1.id
/

        ID ENAME         MGR
---------- ------ ----------
       100 JOHN
       110 KATE          100
       120 MILLER        100
       130 JOHNY         100
       140 LARRY         120
       150 EDWIN         120
       160 KING          110
       170 SUEZ          110
       180 HICK          160

9 rows selected.

SQL> 


SY.
Re: Rebuild tree with fullpath - connect by (?) [message #619121 is a reply to message #618996] Fri, 18 July 2014 03:25 Go to previous message
ric90
Messages: 42
Registered: May 2011
Location: Belfort
Member
Thank you all for your replies.
Previous Topic: View is invalid
Next Topic: Using Case statement after "AND"
Goto Forum:
  


Current Time: Fri Mar 29 07:22:02 CDT 2024