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  |
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);
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 #689784 is a reply to message #689780] |
Sun, 21 April 2024 00:38  |
 |
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 #689787 is a reply to message #689784] |
Mon, 22 April 2024 08:19 |
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)
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  not for me !
|
|
|
|
Goto Forum:
Current Time: Fri May 03 18:06:34 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
