Oracle SQL Connect By Logic

Question

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].

Sergey Kalinichenko · Accepted Answer · 2017-06-30 11:00:46Z

3

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

edited Jun 30, 2017 at 11:00

answered Jun 30, 2017 at 10:47

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL Connect By Logic

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related