Home » SQL & PL/SQL » SQL & PL/SQL » Dad, Grand Pa and Great Grand Pa ! (11.2.0.3)
| Dad, Grand Pa and Great Grand Pa ! [message #686743] |
Fri, 09 December 2022 15:10  |
Amine
Messages: 371
Registered: March 2010
|
Senior Member |
|
|
Hi all,
create table my_item_hierarchy
(
id_item number ,
id_item_sup number ,
ordre number
)
;
insert into my_item_hierarchy values (1, null, 1);
insert into my_item_hierarchy values (2, 1, 2);
insert into my_item_hierarchy values (3, 2, 3);
insert into my_item_hierarchy values (4, 3, 4);
insert into my_item_hierarchy values (5, 3, 5);
insert into my_item_hierarchy values (6, 4, 6);
insert into my_item_hierarchy values (7, 4, 7);
insert into my_item_hierarchy values (8, 2, 8);
insert into my_item_hierarchy values (9, 1, 9);
insert into my_item_hierarchy values (10, 9, 10);
with v00 as
(
select
id_item
, id_item_sup
, level lvl
, sys_connect_by_path(id_item, '/') my_path
from my_item_hierarchy
connect by
prior id_item = id_item_sup
start with id_item = 1
order by level, id_item
), v01 as
(
select
id_item
, lvl
, my_path
, instr(my_path, '/', -1, 1) pos_slash1
, instr(my_path, '/', -1, 2) pos_slash2
, instr(my_path, '/', -1, 3) pos_slash3
, instr(my_path, '/', -1, 4) pos_slash4
from v00
)
select id_item
, my_path
, substr(my_path
, pos_slash2 + 1
, pos_slash1 - pos_slash2 - 1
) id_item_sup1
-- ---
, substr(my_path
, pos_slash3 + 1
, pos_slash2 - pos_slash3 - 1
) id_item_sup2
-- ---
, substr(my_path
, pos_slash4 + 1
, pos_slash3 - pos_slash4 - 1
) id_item_sup3
from v01
;
Thanks in advance
Amine
|
|
|
|
| Re: Dad, Grand Pa and Great Grand Pa ! [message #686756 is a reply to message #686743] |
Tue, 20 December 2022 17:02  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
select id_item,
sys_connect_by_path(id_item,'/') my_path,
to_number(regexp_substr(sys_connect_by_path(id_item,'/'),'/(\d+)/\d+$',1,1,null,1)) id_item_sup1,
to_number(regexp_substr(sys_connect_by_path(id_item,'/'),'/(\d+)/\d+/\d+$',1,1,null,1)) id_item_sup2,
to_number(regexp_substr(sys_connect_by_path(id_item,'/'),'/(\d+)/\d+/\d+/\d+$',1,1,null,1)) id_item_sup3
from my_item_hierarchy
start with id_item_sup is null
connect by prior id_item = id_item_sup
order by level,
id_item
/
ID_ITEM MY_PATH ID_ITEM_SUP1 ID_ITEM_SUP2 ID_ITEM_SUP3
---------- ---------- ------------ ------------ ------------
1 /1
2 /1/2 1
9 /1/9 1
3 /1/2/3 2 1
8 /1/2/8 2 1
10 /1/9/10 9 1
4 /1/2/3/4 3 2 1
5 /1/2/3/5 3 2 1
6 /1/2/3/4/6 4 3 2
7 /1/2/3/4/7 4 3 2
10 rows selected.
SQL>
|
|
|
|
| Re: Dad, Grand Pa and Great Grand Pa ! [message #686758 is a reply to message #686756] |
Wed, 21 December 2022 07:54 |
_jum
Messages: 577
Registered: February 2008
|
Senior Member |
|
|
Since ORACLE 11.2 you could use Recursive Subquery Factoring too:
WITH rsq ( id, my_path, pid, ppid, pppid) AS
(SELECT id_item, '/'||id_item, id_item_sup, NULL, NULL
FROM my_item_hierarchy
WHERE id_item_sup is null
UNION ALL
SELECT i.id_item, my_path||'/'||i.id_item, r.id, r.pid, r.ppid
FROM rsq r
JOIN my_item_hierarchy i
ON r.id = i.id_item_sup)
SELECT id, my_path, pid, ppid, pppid
FROM rsq;
ID MY_PATH PID PPID PPPID
---------------------------------------------
1 /1
2 /1/2 1
9 /1/9 1
3 /1/2/3 2 1
8 /1/2/8 2 1
10 /1/9/10 9 1
4 /1/2/3/4 3 2 1
5 /1/2/3/5 3 2 1
6 /1/2/3/4/6 4 3 2
7 /1/2/3/4/7 4 3 2
|
|
|
|
|
|
| Re: Dad, Grand Pa and Great Grand Pa ! [message #686938 is a reply to message #686937] |
Sun, 05 February 2023 16:50 |
 |
mathguy
Messages: 106
Registered: January 2023
|
Senior Member |
|
|
Assuming you didn't actually need the path (you only used it to get the parent, grand-parent and great-grand-parent of each node), you could do this with a CONNECT BY query running in the opposite direction and then a PIVOT operation to get the result in rows (one row per node). Like this:
with h (id_item, sup, lvl) as
(
select connect_by_root id_item, id_item_sup, level
from my_item_hierarchy
connect by id_item = prior id_item_sup and level <= 3
)
select id_item, id_item_sup1, id_item_sup2, id_item_sup3
from h
pivot (max(sup) for lvl in (1 as id_item_sup1, 2 as id_item_sup2, 3 as id_item_sup3))
;
ID_ITEM ID_ITEM_SUP1 ID_ITEM_SUP2 ID_ITEM_SUP3
---------- ------------ ------------ ------------
1
6 4 3 2
2 1
4 3 2 1
5 3 2 1
8 2 1
3 2 1
7 4 3 2
9 1
10 9 1
|
|
|
|
|
|
| Re: Dad, Grand Pa and Great Grand Pa ! [message #686940 is a reply to message #686939] |
Mon, 06 February 2023 04:34 |
Amine
Messages: 371
Registered: March 2010
|
Senior Member |
|
|
In the same way
create table my_node
(
id_node number ,
node_type number
)
;
insert into my_node values (1, 100);
insert into my_node values (2, null);
insert into my_node values (3, 200);
insert into my_node values (4, null);
insert into my_node values (5, 300);
insert into my_node values (6, 400);
insert into my_node values (7, 500);
insert into my_node values (8, null);
insert into my_node values (9, null);
insert into my_node values (10, null);
insert into my_node values (11, null);
insert into my_node values (12, null);
insert into my_node values (13, null);
insert into my_node values (14, null);
insert into my_node values (15, null);
insert into my_node values (16, null);
create table my_node_hierarchy
(
id_node number ,
id_node_sup number
)
;
insert into my_node_hierarchy values (1, null);
insert into my_node_hierarchy values (2, 1);
insert into my_node_hierarchy values (3, 2);
insert into my_node_hierarchy values (4, 3);
insert into my_node_hierarchy values (5, 4);
insert into my_node_hierarchy values (6, 4);
insert into my_node_hierarchy values (7, 4);
insert into my_node_hierarchy values (14, 5);
insert into my_node_hierarchy values (15, 5);
insert into my_node_hierarchy values (16, 5);
insert into my_node_hierarchy values (11, 6);
insert into my_node_hierarchy values (12, 6);
insert into my_node_hierarchy values (13, 6);
insert into my_node_hierarchy values (8, 7);
insert into my_node_hierarchy values (9, 7);
insert into my_node_hierarchy values (10, 7);
column path format a15
column n1 format 99
column n2 format 99
column n3 format 99
select *
from
(
with t1(id_node, id_node_sup, node_type, lvl, path) as
(
select nh.id_node, nh.id_node_sup
, my_node.node_type
, 1 lvl
, '/' || nh.id_node path
from my_node_hierarchy nh, my_node
where 1 = 1
and nh.id_node = my_node.id_node
and id_node_sup is null
union all
select t2.id_node, t2.id_node_sup
, my_node.node_type
, lvl + 1
, t1.path || '/' || t2.id_node
from my_node_hierarchy t2, t1, my_node
where 1 = 1
and t2.id_node = my_node.id_node
and t2.id_node_sup = t1.id_node
)
search
depth
first by lvl, id_node_sup set order1
cycle id_node set cycle to 1 default 0
select
id_node
, id_node_sup
, path
from t1
order by order1
)
;
ID_NODE ID_NODE_SUP PATH N1 N2 N3
---------- ----------- --------------- --- --- ---
1 /1
2 1 /1/2 1
3 2 /1/2/3 1
4 3 /1/2/3/4 3 1
5 4 /1/2/3/4/5 3 1
14 5 /1/2/3/4/5/14 5 3 1
15 5 /1/2/3/4/5/15 5 3 1
16 5 /1/2/3/4/5/16 5 3 1
6 4 /1/2/3/4/6 3 1
11 6 /1/2/3/4/6/11 6 3 1
12 6 /1/2/3/4/6/12 6 3 1
13 6 /1/2/3/4/6/13 6 3 1
7 4 /1/2/3/4/7 3 1
8 7 /1/2/3/4/7/8 7 3 1
9 7 /1/2/3/4/7/9 7 3 1
10 7 /1/2/3/4/7/10 7 3 1
I loved the way in this post, and i'll appreciate any other solution.
Thanks in advance
|
|
|
|
| Re: Dad, Grand Pa and Great Grand Pa ! [message #689539 is a reply to message #686938] |
Wed, 07 February 2024 10:35 |
Amine
Messages: 371
Registered: March 2010
|
Senior Member |
|
|
Thanks for the valuable reply.
I've added this table, so each item will have its denomination in french and english :
drop table my_item_lib;
create table my_item_lib
(
id_item number ,
lib_fr varchar2(100) ,
lib_en varchar2(100)
)
;
insert into my_item_lib values (1, '1_fr', '1_en');
insert into my_item_lib values (2, '2_fr', '2_en');
insert into my_item_lib values (3, '3_fr', '3_en');
insert into my_item_lib values (4, '4_fr', '4_en');
insert into my_item_lib values (5, '5_fr', '5_en');
insert into my_item_lib values (6, '6_fr', '6_en');
insert into my_item_lib values (7, '7_fr', '7_en');
insert into my_item_lib values (8, '8_fr', '8_en');
insert into my_item_lib values (9, '9_fr', '9_en');
insert into my_item_lib values (10, '10_fr', '10_en');
drop table my_item_hierarchy;
create table my_item_hierarchy
(
id_item number ,
id_item_sup number ,
ordre number
)
;
insert into my_item_hierarchy values (1, null, 1);
insert into my_item_hierarchy values (2, 1, 2);
insert into my_item_hierarchy values (3, 2, 3);
insert into my_item_hierarchy values (4, 3, 4);
insert into my_item_hierarchy values (5, 3, 5);
insert into my_item_hierarchy values (6, 4, 6);
insert into my_item_hierarchy values (7, 4, 7);
insert into my_item_hierarchy values (8, 2, 8);
insert into my_item_hierarchy values (9, 1, 9);
insert into my_item_hierarchy values (10, 9, 10);
select *
from
(
with v00 as
(
select
a.id_item
, lib.lib_fr
, lib.lib_en
-- --
, a.id_item_sup
, lib_sup.lib_fr lib_fr_sup
, lib_sup.lib_en lib_en_sup
from my_item_hierarchy a
, my_item_lib lib
, my_item_lib lib_sup
where 1 = 1
and a.id_item = lib.id_item
and a.id_item_sup = lib_sup.id_item(+)
), h as
(
select
connect_by_root id_item id_item
, id_item_sup
, level lvl
from v00
where 1 = 1
connect by
id_item = prior id_item_sup
and level <= 3
)
select id_item
, id_item_sup1, id_item_sup2, id_item_sup3
from h
pivot (max(id_item_sup) for lvl in (1 as id_item_sup1, 2 as id_item_sup2, 3 as id_item_sup3))
order by 1
)
;
ID_ITEM ID_ITEM_SUP1 ID_ITEM_SUP2 ID_ITEM_SUP3
---------- ------------ ------------ ------------
1
2 1
3 2 1
4 3 2 1
5 3 2 1
6 4 3 2
7 4 3 2
8 2 1
9 1
10 9 1
Now, how can I modify the query so I can get this output :
ID_ITEM ID_ITEM_SUP1 ID_ITEM_SUP2 ID_ITEM_SUP3 LIB_EN_SUP1 LIB_EN_SUP2 LIB_EN_SUP3
---------- ------------ ------------ ------------ --------------- --------------- ---------------
1
2 1 1_en
3 2 1 2_en 1_en
4 3 2 1 3_en 2_en 1_en
5 3 2 1 3_en 2_en 1_en
6 4 3 2 4_en 3_en 2_en
7 4 3 2 4_en 3_en 2_en
8 2 1 2_en 1_en
9 1 1_en
10 9 1 9_en 1_en |
|
|
|
Goto Forum:
Current Time: Wed Apr 17 19:34:10 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
