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 Go to next message
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);
I wrote this query to get, the dad, the grand pa and the great grand pa:

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
;
Is there a better way to do this in Oracle 11g (11.2.0.3) ?

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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
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>
SY.
Re: Dad, Grand Pa and Great Grand Pa ! [message #686758 is a reply to message #686756] Wed, 21 December 2022 07:54 Go to previous messageGo to next message
_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 #686937 is a reply to message #686758] Sun, 05 February 2023 14:29 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Wonderfull answer !

Thanks a lot !

This feature is really amazing !
Re: Dad, Grand Pa and Great Grand Pa ! [message #686938 is a reply to message #686937] Sun, 05 February 2023 16:50 Go to previous messageGo to next message
mathguy
Messages: 105
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 #686939 is a reply to message #686938] Mon, 06 February 2023 04:32 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Thanks a lot !
Re: Dad, Grand Pa and Great Grand Pa ! [message #686940 is a reply to message #686939] Mon, 06 February 2023 04:34 Go to previous messageGo to next message
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
)
;
I want to write query to get this :

   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
Actually, starting from each tree node, we look for its ancestors that have node_type not null.

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 Go to previous message
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
Previous Topic: oracle10g and utl_http
Next Topic: Querying table permissions to see which users have them
Goto Forum:
  


Current Time: Thu Feb 29 00:20:57 CST 2024