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