CODEWITHSUNDEEP
sqloracle-database
pOrinG
pOrinG

Reputation: 935

Oracle SQL Connect By Logic

Currently in one of our products we have a query with logic as explained below:

create table temp (emp_id varchar2(3), manager_id varchar2(3) )

Data :

E10 E20
E20 E50
E30 E50
E50 E90

Query:

select *
from Temp
    Start with EMP_ID = 'E90'
    Connect by Prior EMP_ID = MANAGER_ID and EMP_ID != MANAGER_ID
order by EMP_ID

I understand the concept of the query [& connect by] which is that we need to fetch all the children records of the employee specified including the current employee record. I have doubts about the need of adding EMP_ID != MANAGER_ID in the end.

The question is why was it added & in what situation will it be useful [if any].

Upvotes: 2

Views: 142

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

The last condition does not apply to your data, but it is very important to avoid infinite recursion.

To illustrate this point, consider what would happen if you added another line to your table:

E40 E40

If you start with E40 instead of E90, Oracle would spin into an infinite recursion without EMP_ID != MANAGER_ID condition, because E40 would connect back to E40.

Note that a better approach to writing this query is to use NOCYCLE option instead of coding in an explicit check:

SELECT *
FROM Temp
    START WITH EMP_ID = 'E90'
    CONNECT BY NOCYCLE PRIOR EMP_ID = MANAGER_ID
ORDER BY EMP_ID

Upvotes: 3

Related Questions