I have a question about the conditions START WITH and CONNECT BY (in Oracle 12c):
This is my Oracle SQL:
CHOOSE
Employee ID,
lpad (& # 39; & # 39 ;, level * 2-1, & # 39; & # 39;) || Surname,
manager_id,
Level,
SYS_CONNECT_BY_PATH (last_name, & # 39; / & # 39;) "Path" - calls the full path of the child object
FROM
Employee
Start with employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
AND NOT (last_name = C Cambrault und and first_name = # Gerald #)
Sort siblings by last name
what gives the following output:
EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL PATH
100 King Zero 1 / King
102 De Haan 100 2 / King / De Haan
103 Hunold 102 3 / King / De Haan / Hunold
105 Austin 103 4 / King / De Haan / Hunold / Austin
104 Ernst 103 4 / King / De Haan / Hunold / Ernst
107 Lorentz 103 4 / King / De Haan / Hunold / Lorentz
106 Pataballa 103 4 / King / De Haan / Hunold / Pataballa
However, if I remove the START WITH in the above query:
CHOOSE
Employee ID,
lpad (& # 39; & # 39 ;, level * 2-1, & # 39; & # 39;) || Surname,
manager_id,
Level,
SYS_CONNECT_BY_PATH (last_name, & # 39; / & # 39;) "Path" - calls the full path of the child object
FROM
Employee
CONNECT BY PRIOR employee_id = manager_id
AND NOT (last_name = C Cambrault und and first_name = # Gerald #)
Sort siblings by last name
it gives the following result:
EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL PATH
148 Cambrault 100 1 / Cambrault
172 Bates 148 2 / Cambrault / Bates
169 Bloom 148 2 / Cambrault / Bloom
170 Fox 148 2 / Cambrault / Fox
173 Kumar 148 2 / Cambrault / Kumar
168 Ozer 148 2 / Cambrault / Ozer
171 Smith 148 2 / Cambrault / Smith
Can you please explain why, despite using the NOT condition (last_name = # Cambrault # and first_name = # Gerald #), it will not work unless it is used with START WITH (Starting the hierarchy of employee_id = 100). I understand that the condition in CONNECT BY has no effect on the root row (s), but employee_id (148) is not root. it's just a parent line, why does not it work?