Hierarchy problem [message #669991] |
Mon, 28 May 2018 19:26 |
Amine
Messages: 376 Registered: March 2010
|
Senior Member |
|
|
Hi guys,
Here is a table where id represents a person.
A person at a time can be replaced by another person.
drop table mvts;
create table mvts
(
id number ,
id_replaced_by number
)
;
alter table mvts add constraint UK_id unique (id);
alter table mvts add constraint UK_id_replaced_by unique (id_replaced_by);
insert into mvts values (2, 1);
insert into mvts values (1, null);
insert into mvts values (3, 4);
insert into mvts values (4, 5);
insert into mvts values (5, 6);
insert into mvts values (6, null);
insert into mvts values (7, null);
insert into mvts values (8, null);
insert into mvts values (9, 10);
Now, I want to get all the replacements.
column my_path format a30
select *
from
(
with v as
(
select
id
, sys_connect_by_path(id, ' --> ') my_path
, level lvl
, max(level) over (partition by id) max_lvl
, connect_by_root id id_root
, connect_by_root id_replaced_by id_root_id_replaced_by
from mvts
where 1 = 1
connect by id(+) = prior id_replaced_by
order by id, lvl
)
select *
from v
where 1 = 1
--and lvl = max_lvl
)
;
ID MY_PATH LVL MAX_LVL ID_ROOT
--------- ------------------------------ --------- --------- ---------
1 --> 1 1 2 1
1 --> 2 --> 1 2 2 2
2 --> 2 1 1 2
3 --> 3 1 1 3
4 --> 4 1 2 4
4 --> 3 --> 4 2 2 3
5 --> 5 1 3 5
5 --> 4 --> 5 2 3 4
5 --> 3 --> 4 --> 5 3 3 3
6 --> 6 1 4 6
6 --> 5 --> 6 2 4 5
6 --> 4 --> 5 --> 6 3 4 4
6 --> 3 --> 4 --> 5 --> 6 4 4 3
7 --> 7 1 1 7
8 --> 8 1 1 8
As you can see, there is redundoncy on the output and I want to get this output (i. e. the longest path for a replacement).
ID MY_PATH LVL MAX_LVL ID_ROOT
--------- ------------------------------ --------- --------- ---------
1 --> 2 --> 1 2 2 2
6 --> 3 --> 4 --> 5 --> 6 4 4 3
7 --> 7 1 1 7
8 --> 8 1 1 8
Thanks in advance
Amine
|
|
|
|
|
Re: Hierarchy problem [message #670062 is a reply to message #670061] |
Fri, 01 June 2018 00:43 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Amine wrote on Thu, 31 May 2018 19:26...But it could be perfect if we can add the last row (i.e. 10 <-- 9).
You would need an additional row in the table, as shown below.
SCOTT@orcl_12.1.0.2.0> insert into mvts values (10, null)
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> select * from mvts
2 /
ID ID_REPLACED_BY
---------- --------------
2 1
1
3 4
4 5
5 6
6
7
8
9 10
10
10 rows selected.
SCOTT@orcl_12.1.0.2.0> select connect_by_root id as id,
2 sys_connect_by_path (id, ' <-- ') as my_path,
3 level as lvl,
4 id as id_root
5 from mvts
6 where connect_by_isleaf = 1
7 start with id_replaced_by is null
8 connect by prior id = id_replaced_by
9 /
ID MY_PATH LVL ID_ROOT
---------- ------------------------------ ---------- ----------
1 <-- 1 <-- 2 2 2
6 <-- 6 <-- 5 <-- 4 <-- 3 4 3
7 <-- 7 1 7
8 <-- 8 1 8
10 <-- 10 <-- 9 2 9
5 rows selected.
|
|
|