This method performs a selective expansion of a target item with the LEVEL value set to "1" at the highest row in the hierarchy and increasing as you descend the tree, as in this selective expansion example for item "b.c.c".
PADDED_KEY LEVEL
--------------- ------
b 1
b.c 2
b.c.c 3
b.c.c.a 4
b.c.c.b 4
b.c.c.c 4
To accomplish this, we simply place the query developed in Method 1 - UNION ALL in the FROM clause of another hierarchical query.
execute :v_target_key := 'b.c.c'
select
lpad( ' ', (2*level-2) ) || key as padded_key ,
level
from
( --------------------------------------------------
-- this query came from "Method 1 - UNION ALL"
--------------------------------------------------
select -- all rows at and above the target
key ,
parent_key
from
t
start with
key = :v_target_key
connect by
key = prior parent_key
union all
select -- select all rows below the target
key ,
parent_key
from
t
where
key != :v_target_key
start with
key = :v_target_key
connect by
parent_key = prior key
)
start with
parent_key is null
connect by
prior key = parent_key
order siblings by
key
;
PADDED_KEY LEVEL
--------------- ------
b 1
b.c 2
b.c.c 3
b.c.c.a 4
b.c.c.b 4
b.c.c.c 4