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 |
Amine
Messages: 376 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 |
Amine
Messages: 376 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 |
Amine
Messages: 376 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 |
Amine
Messages: 376 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
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:42:44 CDT 2024
|