Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchy again and again (11.2.0.3.0)
Hierarchy again and again [message #680029] Sun, 19 April 2020 16:28 Go to next message
Amine
Messages: 318
Registered: March 2010
Senior Member

Hi Gurus,
I have this simple schema :

drop table struct;
create table struct
(
	id_stru			number			,
	lib_stru		varchar2(100)	,
	id_stru_sup		number
)
;
alter table struct add constraint PK_struct primary key (id_stru);
alter table struct add constraint FK_struct foreign key (id_stru_sup) references struct(id_stru);
alter table struct add constraint CK_struct check (id_stru_sup <> id_stru);

insert into struct values (1, 'BIG', null);
insert into struct values (2, 'SD1', 1);
insert into struct values (3, 'B11', 2);
insert into struct values (4, 'B12', 2);
insert into struct values (5, 'SD2', 1);
insert into struct values (7, 'B21', 5);
insert into struct values (8, 'B22', 5);
My requirement is simple :
I want to get, for a particular node, the node itself and their children without union all.

select id_stru
, lib_stru
from struct
connect by prior id_stru = id_stru_sup
start with nvl(id_stru_sup, 0) = 2
union all
select id_stru
, lib_stru
from struct
where id_stru = 2
;
Thanks in advance,
Amine
Hierarchy again and again [message #680030 is a reply to message #680029] Sun, 19 April 2020 16:28 Go to previous messageGo to next message
Amine
Messages: 318
Registered: March 2010
Senior Member

Hi Gurus,
I have this simple schema :

drop table struct;
create table struct
(
	id_stru			number			,
	lib_stru		varchar2(100)	,
	id_stru_sup		number
)
;
alter table struct add constraint PK_struct primary key (id_stru);
alter table struct add constraint FK_struct foreign key (id_stru_sup) references struct(id_stru);
alter table struct add constraint CK_struct check (id_stru_sup <> id_stru);

insert into struct values (1, 'BIG', null);
insert into struct values (2, 'SD1', 1);
insert into struct values (3, 'B11', 2);
insert into struct values (4, 'B12', 2);
insert into struct values (5, 'SD2', 1);
insert into struct values (7, 'B21', 5);
insert into struct values (8, 'B22', 5);
My requirement is simple :
I want to get, for a particular node, the node itself and their children without union all.

select id_stru
, lib_stru
from struct
connect by prior id_stru = id_stru_sup
start with nvl(id_stru_sup, 0) = 2
union all
select id_stru
, lib_stru
from struct
where id_stru = 2
;
Thanks in advance,
Amine
Hierarchy again and again [message #680031 is a reply to message #680029] Sun, 19 April 2020 16:30 Go to previous messageGo to next message
Amine
Messages: 318
Registered: March 2010
Senior Member

Hi Gurus,
I have this simple schema :

drop table struct;
create table struct
(
	id_stru			number			,
	lib_stru		varchar2(100)	,
	id_stru_sup		number
)
;
alter table struct add constraint PK_struct primary key (id_stru);
alter table struct add constraint FK_struct foreign key (id_stru_sup) references struct(id_stru);
alter table struct add constraint CK_struct check (id_stru_sup <> id_stru);

insert into struct values (1, 'BIG', null);
insert into struct values (2, 'SD1', 1);
insert into struct values (3, 'B11', 2);
insert into struct values (4, 'B12', 2);
insert into struct values (5, 'SD2', 1);
insert into struct values (7, 'B21', 5);
insert into struct values (8, 'B22', 5);
My requirement is simple :
I want to get, for a particular node, the node itself and their children without union all.

select id_stru
, lib_stru
from struct
connect by prior id_stru = id_stru_sup
start with nvl(id_stru_sup, 0) = 2
union all
select id_stru
, lib_stru
from struct
where id_stru = 2
;
Thanks in advance,
Amine
Hierarchy again and again [message #680032 is a reply to message #680029] Sun, 19 April 2020 16:30 Go to previous messageGo to next message
Amine
Messages: 318
Registered: March 2010
Senior Member

Hi Gurus,
I have this simple schema :

drop table struct;
create table struct
(
	id_stru			number			,
	lib_stru		varchar2(100)	,
	id_stru_sup		number
)
;
alter table struct add constraint PK_struct primary key (id_stru);
alter table struct add constraint FK_struct foreign key (id_stru_sup) references struct(id_stru);
alter table struct add constraint CK_struct check (id_stru_sup <> id_stru);

insert into struct values (1, 'BIG', null);
insert into struct values (2, 'SD1', 1);
insert into struct values (3, 'B11', 2);
insert into struct values (4, 'B12', 2);
insert into struct values (5, 'SD2', 1);
insert into struct values (7, 'B21', 5);
insert into struct values (8, 'B22', 5);
My requirement is simple :
I want to get, for a particular node, the node itself and their children without union all.

select id_stru
, lib_stru
from struct
connect by prior id_stru = id_stru_sup
start with nvl(id_stru_sup, 0) = 2
union all
select id_stru
, lib_stru
from struct
where id_stru = 2
;
Thanks in advance,
Amine
Re: Hierarchy again and again [message #680035 is a reply to message #680032] Mon, 20 April 2020 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you want the root itself then start with it:
SQL> select id_stru, lib_stru
  2  from struct
  3  connect by prior id_stru = id_stru_sup
  4  start with id_stru = 2
  5  /
   ID_STRU LIB_STRU
---------- ---------------------------------------
         2 SD1
         3 B11
         4 B12
Re: Hierarchy again and again [message #680053 is a reply to message #680035] Mon, 20 April 2020 08:42 Go to previous message
Amine
Messages: 318
Registered: March 2010
Senior Member

Thanks Michel.
Previous Topic: Text to number conversion
Next Topic: RAISE_APPLICATION_ERROR not invoked by Logon trigger
Goto Forum:
  


Current Time: Mon Sep 21 15:04:52 CDT 2020