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 |
|
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 |
|
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 #619028 is a reply to message #619008] |
Thu, 17 July 2014 08:06 |
|
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 |
|
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 |
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.
|
|
|
|
Goto Forum:
Current Time: Fri Mar 29 07:22:02 CDT 2024
|