Consider the following query from Oracle documentation https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm
SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME MANAGER_ID
101 Kochhar 100
108 Greenberg 101
109 Faviet 108
110 Chen 108
111 Sciarra 108
112 Urman 108
113 Popp 108
200 Whalen 101
I want to filter this tree to get employees only with a letter 'a' in a last name. I could use WHERE clause, but the thing is I don't want to get only the rows that do match condition, but also their parents event if they DON'T, i.e I don't want to break a tree. According documentation Oracle evaluates the condition for each row individually. For example if I use WHERE clause I get rows with ids 101, 109, 111, 112, 200. But I want to get 101, 108, 109, 111, 112, 200. How can I filter the tree without breaking it?
As one of the approaches, you can start traversing the tree from the bottom up - you find an employee with an a in his/her name and go up the tree:
Distinct clause is there to get rid of duplicate parents and we need second connect by clause to turn the tree upside down.
-- sample of data from your question
with t1(EMPLOYEE_ID,LAST_NAME,MANAGER_ID) as(
select 101, 'Kochhar' , 100 from dual union all
select 108, 'Greenberg' , 101 from dual union all
select 109, 'Faviet' , 108 from dual union all
select 110, 'Chen' , 108 from dual union all
select 111, 'Sciarra' , 108 from dual union all
select 112, 'Urman' , 108 from dual union all
select 113, 'Popp' , 108 from dual union all
select 200, 'Whalen' , 101 from dual
)
-- actual query
select employee_id
, manager_id
, concat(lpad('-', 3*level, '-'), last_name) as last_name
from (
-- using distinct to get rid of duplicate parents
select distinct last_name
, employee_id
, manager_id
from t1
start with last_name like '%a%'
connect by employee_id = prior manager_id
) q
start with manager_id = 100
connect by prior employee_id = manager_id
Result:
EMPLOYEE_ID MANAGER_ID LAST_NAME
----------- ---------- --------------------
101 100 ---Kochhar
108 101 ------Greenberg
109 108 ---------Faviet
111 108 ---------Sciarra
112 108 ---------Urman
200 101 ------Whalen
6 rows selected.