Oracle Database Discussions

connect_by_isleaf for 9i

another example from Elic

My alternative solutions.

***Sample***
ID  PrevID
--  ------
 1    null
 2       1
 3       1
 4       3

***output which we want***
ID  PrevID  isLeaf
--  ------  ------
 1    null       0
 2       1       1
 3       1       0
 4       3       1

***DDL***
create table CloneIsLeaf as
select 1 as ID,null as PrevID from dual
union select 2,1 from dual
union select 3,1 from dual
union select 4,3 from dual;

--method1
select ID,PrevID,
case when exists(select 1 from CloneIsLeaf b
                  where a.ID = b.PrevID) then 0 else 1 end as isLeaf
  from CloneIsLeaf a
Start With ID = 1
connect by prior ID = PrevID;

--method2
select ID,PrevID,
case when Level < Lead(Level) over(order by RowNum)
     then 0 else 1 end as isLeaf
  from CloneIsLeaf
Start With ID = 1
connect by prior ID = PrevID;

--method3
select ID,PrevID,
case when LV < Lead(LV) over(order by RowNum)
     then 0 else 1 end as isLeaf
from (select ID,PrevID,Level as LV
        from CloneIsLeaf
      Start With ID = 1
      connect by prior ID = PrevID
      order siblings by ID);

--method4
select ID,PrevID,
case when LV < Lead(LV) over(order by Row_Num)
     then 0 else 1 end as isLeaf
from (select ID,PrevID,LV,RowNum as Row_Num
        from (select ID,PrevID,Level as LV
                from CloneIsLeaf
              Start With ID = 1
              connect by prior ID = PrevID
              order siblings by ID));

on method2 and method3 and method3,
I used that "Hierarchical Queries" is depth-first search (http://en.wikipedia.org/wiki/Depth-first_search)

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm#i2053935

my site :-)
http://www.geocities.jp/oraclesqlpuzzle/10-149.html