This thread is continued from
1056886
and
1057057
select * from v$version;
BANNER
-------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
create table siblingsT(
ID number primary key,
OyaID number);
insert into siblingsT values(1,null);
insert into siblingsT values(3, 1);
insert into siblingsT values(5, 3);
commit;
select connect_by_root ID as treeID,ID,OyaID
from siblingsT
start with OyaID is null
connect by prior ID = OyaID;
treeID ID OyaID
------ -- -----
1 1 null
1 3 1
1 5 3
OK I will emulate
connect_by_root in
recursive with clause :-)
with rec(treeID,ID,OyaID) as(
select ID,ID,OyaID
from siblingsT
where OyaID is null
union all
select a.ID,b.ID,b.OyaID
from rec a,siblingsT b
where a.ID = b.OyaID)
select * from rec;
treeID ID OyaID
------ -- -----
1 1 null
1 3 1
3 5 3
But I think correct result is below :-(
Why treeID of third row is 3 ?
treeID ID OyaID
------ -- -----
1 1 null
1 3 1
1 5 3
****************************************************************************************************
SomeHow below SQL which exchange
a.ID to
a.TreeID returns correct result.
But I think this is a bug B-)
with rec(treeID,ID,OyaID) as(
select ID,ID,OyaID
from siblingsT
where OyaID is null
union all
select a.treeID,b.ID,b.OyaID
from rec a,siblingsT b
where a.ID = b.OyaID)
select * from rec;
treeID ID OyaID
------ -- -----
1 1 null
1 3 1
1 5 3