SQL Snippets ™: Tutorials for Oracle Developers

This method performs a selective expansion of a target item with the LEVEL value set to "1" at the target row and increasing as you move away from it in either direction, as in this selective expansion example for item "b.c.c".

KEY         LEVEL
---------- ------
b               3
b.c             2
b.c.c           1
b.c.c.a         2
b.c.c.b         2
b.c.c.c         2
 

We start by considering the following query, which gives us the target row and all rows above it.

execute :v_target_key := 'b.c.c'

select
  key ,
  level
from
  t
start with
  key = :v_target_key
connect by
  key = prior parent_key
;
 

KEY         LEVEL
---------- ------
b.c.c           1
b.c             2
b               3
 

This next query gives us all the rows below the target.

select
  key ,
  level
from
  t
where
  key != :v_target_key
start with
  key = :v_target_key
connect by
  parent_key = prior key
;
 

KEY         LEVEL
---------- ------
b.c.c.a         2
b.c.c.b         2
b.c.c.c         2
 

Combining the two queries with a UNION ALL gives us the rows we need.

select
  key ,
  level
from
  t
start with
  key = :v_target_key
connect by
  key = prior parent_key
union all
select
  key ,
  level
from
  t
where
  key != :v_target_key
start with
  key = :v_target_key
connect by
  parent_key = prior key
order by
  1
;
 

KEY         LEVEL
---------- ------
b               3
b.c             2
b.c.c           1
b.c.c.a         2
b.c.c.b         2
b.c.c.c         2
 

In the next tutorial we see how to generate a result where the LEVEL is '1' at the highest row in the hierarchy.