Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchy problem (11.2.0.3)
Hierarchy problem [message #669991] Mon, 28 May 2018 19:26 Go to next message
Amine
Messages: 371
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 #669992 is a reply to message #669991] Mon, 28 May 2018 21:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If it does not matter how the my_path column is displayed, then:

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

4 rows selected.
Re: Hierarchy problem [message #670061 is a reply to message #669992] Thu, 31 May 2018 21:26 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

lovely Barbara ! Thanks a lot for your availability, you're awesome !
Good solution. But it could be perfect if we can add the last row (i.e. 10 <-- 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 <-- 9                                  1          9
Re: Hierarchy problem [message #670062 is a reply to message #670061] Fri, 01 June 2018 00:43 Go to previous message
Barbara Boehmer
Messages: 9077
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.
Previous Topic: DB Port Error
Next Topic: Oracle.ucp.jdbc.HarvestableConnection error
Goto Forum:
  


Current Time: Thu Mar 28 05:00:52 CDT 2024