Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchy again and again and again ! (11.2.0.3)
Hierarchy again and again and again ! [message #684172] Wed, 14 April 2021 18:28 Go to next message
Amine
Messages: 336
Registered: March 2010
Senior Member

Hi all,
I have this table :

drop table hierarchy;
create table hierarchy
(
	id		int	,
	id_sup 		int
)
;

insert into hierarchy values (1,null);
insert into hierarchy values (2,1);
insert into hierarchy values (3,2);

insert into hierarchy values (4,1);
insert into hierarchy values (5,4);
 
insert into hierarchy values (6,1);
Then I run this query :
SQL> select 
  2  id
  3  , id_sup
  4  , connect_by_root id id_root
  5  , connect_by_root id_sup id_sup_root
  6  from hierarchy
  7  where 1 = 1
  8  connect by 
  9  prior id = id_sup
 10  start with id_sup = 1
 11  ;

       ID    ID_SUP   ID_ROOT ID_SUP_ROOT
--------- --------- --------- -----------
        2         1         2           1
        3         2         2           1
        4         1         4           1
        5         4         4           1
        6         1         6           1

SQL> 
The problem is that this query does not offer this row :

       ID    ID_SUP   ID_ROOT ID_SUP_ROOT
--------- --------- --------- -----------
        1                  1           1
How can I write my query to get this row added ?

Thanks in advance,
Re: Hierarchy again and again and again ! [message #684174 is a reply to message #684172] Thu, 15 April 2021 00:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67842
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select id, id_sup, connect_by_root id id_root,
  2         nvl(connect_by_root id_sup, connect_by_root id) id_sup_root
  3  from hierarchy
  4  connect by prior id = id_sup
  5  start with id_sup is null
  6  /
        ID     ID_SUP    ID_ROOT ID_SUP_ROOT
---------- ---------- ---------- -----------
         1                     1           1
         2          1          1           1
         3          2          1           1
         4          1          1           1
         5          4          1           1
         6          1          1           1
Re: Hierarchy again and again and again ! [message #684175 is a reply to message #684174] Thu, 15 April 2021 04:42 Go to previous messageGo to next message
Amine
Messages: 336
Registered: March 2010
Senior Member

Wonderful Michel. Thanks a lot. Please could you put this topic on sqo plsql. I did a mistake.
Re: Hierarchy again and again and again ! [message #684177 is a reply to message #684175] Thu, 15 April 2021 05:02 Go to previous message
Michel Cadot
Messages: 67842
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Done.

Previous Topic: how to remove html tags from the text
Next Topic: upper case in create table
Goto Forum:
  


Current Time: Sat May 08 21:08:53 CDT 2021