Search notes:

Oracle SQL: WHERE clause in hierarchical queries

In start with … connect by hierarchical queries, the where clause is evaluated after hierarchically selecting the records:

Test data:

drop   table tq84_hq;
create table tq84_hq (id number, parent_id number, txt varchar2(20));

insert into tq84_hq values (1, null, 'ROOT');
insert into tq84_hq values (2,    1, 'sub a');
insert into tq84_hq values (3,    1, 'sub b');
insert into tq84_hq values (4,    2, 'sub a sub 1');
insert into tq84_hq values (5,    3, 'sub b sub 1');
insert into tq84_hq values (6,    3, 'sub b sub 2');

Query:

select
   id,
   txt,
   level
from
   tq84_hq
where
   id between 3 and 5
start with
   parent_id is null
connect by
   prior id = parent_id
;

The above query returns;

        ID TXT                       LEVEL
---------- -------------------- ----------
         4 sub a sub 1                   3
         3 sub b                         2
         5 sub b sub 1                   3

Index