Home » SQL & PL/SQL » SQL & PL/SQL » Getting ancestors in hierarchies (11.2.0.3.0)
Getting ancestors in hierarchies [message #689780] Sat, 20 April 2024 13:37 Go to next message
Amine
Messages: 375
Registered: March 2010
Senior Member

Hi all,
I have this schema :
create table t_hierarchy
(
	id		number	,
	id_sup		number	,
	-- ----
	id_type	number
)
;
insert into t_hierarchy values (1, null, 1);
insert into t_hierarchy values (2, 1, 1);

insert into t_hierarchy values (3, 2, 0);
insert into t_hierarchy values (4, 3, 0);
insert into t_hierarchy values (5, 4, 0);
-- ----
insert into t_hierarchy values (6, 2, 1);

insert into t_hierarchy values (7, 6, 0);
insert into t_hierarchy values (8, 7, 0);
-- ----
insert into t_hierarchy values (9, 2, 1);

insert into t_hierarchy values (10, 9, 0);
insert into t_hierarchy values (11, 10, 0);
-- ----
insert into t_hierarchy values (12, 2, 1);

insert into t_hierarchy values (13, 12, 0);
insert into t_hierarchy values (14, 13, 0);
insert into t_hierarchy values (15, 14, 0);

insert into t_hierarchy values (16, 12, 0);
insert into t_hierarchy values (17, 16, 0);
insert into t_hierarchy values (18, 17, 0);
I want to get the last ancestor for each id that has id_type equals to 0.
id	id_sup	id_type	last_ancestor
--	------	-------	-------------
1		1		
2	1	1	
3	2	0	3
4	3	0	3
5	4	0	3
6	2	1		
7	6	0	7
8	7	0	7
9	2	1		
10	9	0	10
11	10	0	10
12	2	1		
13	12	0	13
14	13	0	13
15	14	0	13
16	12	0	16
17	16	0	16
18	17	0	16

Thanks in advance,
Amine
Re: Getting ancestors in hierarchies [message #689781 is a reply to message #689780] Sat, 20 April 2024 13:44 Go to previous messageGo to next message
John Watson
Messages: 8932
Registered: January 2010
Location: Global Village
Senior Member
https://www.orafaq.com/forum/m/686743
Re: Getting ancestors in hierarchies [message #689782 is a reply to message #689781] Sat, 20 April 2024 13:55 Go to previous messageGo to next message
Amine
Messages: 375
Registered: March 2010
Senior Member

Thanks for the reply John.
The provided solution in the post you've mentioned does not meet my requirement.

The provided solution deals with nodes that have the same type.
In the actual post, it's not the case. We have, at least, two (02) types of nodes. (id_type in (0,1) may be more).
Re: Getting ancestors in hierarchies [message #689783 is a reply to message #689782] Sat, 20 April 2024 15:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9096
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> select  id, id_sup, id_type,
  2  	     decode (id_type, 0, connect_by_root id, null) as last_ancestor
  3  from    t_hierarchy t1
  4  start   with not exists
  5  	       (select *
  6  		from   t_hierarchy t2
  7  		where  t1.id_sup = t2.id
  8  		and    t1.id_type = t2.id_type)
  9  connect by prior id = id_sup and
 10  		prior id_type = id_type
 11  order   by id
 12  /

        ID     ID_SUP    ID_TYPE LAST_ANCESTOR
---------- ---------- ---------- -------------
         1                     1
         2          1          1
         3          2          0             3
         4          3          0             3
         5          4          0             3
         6          2          1
         7          6          0             7
         8          7          0             7
         9          2          1
        10          9          0            10
        11         10          0            10
        12          2          1
        13         12          0            13
        14         13          0            13
        15         14          0            13
        16         12          0            16
        17         16          0            16
        18         17          0            16

18 rows selected.
Re: Getting ancestors in hierarchies [message #689784 is a reply to message #689780] Sun, 21 April 2024 00:38 Go to previous messageGo to next message
mathguy
Messages: 107
Registered: January 2023
Senior Member
Clarification needed.

In your sample data, if a node is type 1 then its parent is always also of type 1. (Equivalently, if a node is type 0, then its children - if any - are all of type 0.)

Is that also true in your real-life data? Or could you have a sub-hierarchy where 101 is parent of 102 who is parent of 103 who is parent of 104, and 101 and 103 are type 1 while 102 and 104 are type 0?

And, if such alternation of type between 1 and 0, as you descend from node to node (or as you ascend from node to node), are possible, WHAT do you actually need to find? Suppose in my example you start with 104; do you stop immediately when you find that its parent, 103, has type 1? Or do you search further, and discover that 103's parent, which is 102, has type 0, and that must be considered too?

If it's the latter, the problem is a bit more interesting; if it's the former (stop as soon as a parent is type 1, don't look further) then the problem is trivial. Filter out all the nodes of type 1 first, and then apply a standard hierarchical query.
Re: Getting ancestors in hierarchies [message #689785 is a reply to message #689784] Sun, 21 April 2024 01:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9096
Registered: November 2002
Location: California, USA
Senior Member
I assumed that the desired output was what was posted.  If you only want to display the ones with id_type 0, then you can use the following:



SCOTT@orcl_12.1.0.2.0> with t as
  2    (select *
  3  	from   t_hierarchy
  4  	where  id_type = 0)
  5  select  id, id_sup, id_type,
  6  	     connect_by_root id as last_ancestor
  7  from    t t1
  8  start   with not exists
  9  	       (select *
 10  		from   t t2
 11  		where  t1.id_sup = t2.id)
 12  connect by prior id = id_sup
 13  order   by id
 14  /

        ID     ID_SUP    ID_TYPE LAST_ANCESTOR
---------- ---------- ---------- -------------
         3          2          0             3
         4          3          0             3
         5          4          0             3
         7          6          0             7
         8          7          0             7
        10          9          0            10
        11         10          0            10
        13         12          0            13
        14         13          0            13
        15         14          0            13
        16         12          0            16
        17         16          0            16
        18         17          0            16

13 rows selected.
Re: Getting ancestors in hierarchies [message #689786 is a reply to message #689783] Sun, 21 April 2024 02:29 Go to previous messageGo to next message
Amine
Messages: 375
Registered: March 2010
Senior Member

Thanks WonderWoman !
This is the expected result.

Thanks again
Re: Getting ancestors in hierarchies [message #689787 is a reply to message #689784] Mon, 22 April 2024 08:19 Go to previous message
Amine
Messages: 375
Registered: March 2010
Senior Member

Quote:
Clarification needed.

In your sample data, if a node is type 1 then its parent is always also of type 1. (Equivalently, if a node is type 0, then its children - if any - are all of type 0.)

Is that also true in your real-life data?
not necessarily. Here is an example :
 
drop table t_hierarchy;
create table t_hierarchy
(
	id		number	,
	id_sup	number	,
	id_type	number
);

insert into t_hierarchy values (1, null, 1);

insert into t_hierarchy values (2, 1, 0);
insert into t_hierarchy values (3, 2, 0);
insert into t_hierarchy values (30, 3, 0);
insert into t_hierarchy values (31, 3, 0);
insert into t_hierarchy values (32, 30, 0);

insert into t_hierarchy values (4, 2, 1);
insert into t_hierarchy values (5, 4, 0);

insert into t_hierarchy values (6, 2, 1);
insert into t_hierarchy values (7, 6, 0);

insert into t_hierarchy values (8, 2, 1);
insert into t_hierarchy values (9, 8, 0);

insert into t_hierarchy values (10, 2, 1);
insert into t_hierarchy values (11, 10, 0);

insert into t_hierarchy values (12, 2, 1);
insert into t_hierarchy values (13, 12, 0);

LIB_ID
---------------
-1 (1)
--2 (0)
---3 (0)
----30 (0)
-----32 (0)
----31 (0)
---4 (1)
----5 (0)
---6 (1)
----7 (0)
---8 (1)
----9 (0)
---10 (1)
----11 (0)
---12 (1)
----13 (0)
As you can see, the parent of 4(1) is 2(0).
 

Quote:

Or could you have a sub-hierarchy where 101 is parent of 102 who is parent of 103 who is parent of 104, and 101 and 103 are type 1 while 102 and 104 are type 0?
Yes, we can have this type of hierarchy.

Quote:

And, if such alternation of type between 1 and 0, as you descend from node to node (or as you ascend from node to node), are possible, WHAT do you actually need to find?
Actually, I have a hierarchy composed of multiple node types (0 and 1). In general, I don't care about nodes with type 1. I'm intersted about nodes that have node type 0 to regroup them with the latest ancestor.

Quote:

Suppose in my example you start with 104; do you stop immediately when you find that its parent, 103, has type 1? Or do you search further, and discover that 103's parent, which is 102, has type 0, and that must be considered too?
I stop immediately when I find its parent 103 (1).
I do not search further then that.

Quote:

If it's the latter, the problem is a bit more interesting; if it's the former (stop as soon as a parent is type 1, don't look further) then the problem is trivial. Filter out all the nodes of type 1 first, and then apply a standard hierarchical query.
May be trivial for you Smile not for me !
Previous Topic: Oracle ORA-00918: column ambiguously defined
Next Topic: Convert input from user into UPPERCASE
Goto Forum:
  


Current Time: Sun May 19 21:53:35 CDT 2024