CODEWITHSUNDEEP
sqloracle-databaseconnect-by
Pratsam
Pratsam

Reputation: 162

Use of Prior in Hierarchical Query

I am trying to implement hierarchical query in my project. I have read most of the documents on internet but unable to get information related to use of PRIOR in it. 

SELECT EMPNO,ENAME,MGR,LEVEL
FROM TMP_PCH
CONNECT BY PRIOR EMPNO=MGR
START WITH MGR IS NULL;

PRIOR plays significant role in this query but changing the position of PRIOR or Removing it from query gives me a different result.
It would be really helpful if you can help me to understand exact use of this.
Thanks :)

Upvotes: 0

Views: 1055

Answers (2)

Pavel Gatnar
Pavel Gatnar

Reputation: 4053

you can understand PRIOR as a reference to the previous hierarchy level.
PRIOR MGR = EMPNO means "the manager from the previous level is now the employee", so we are going bottom-up
PRIOR EMPNO = MGR means "the employee from the previous level is now the manager", so we are going top-down

when you write it like this EMPNO = PRIOR MGR, you can read it as "we go the direction from an employee to his manager"

bottom-up

             MGR
PRIOR MGR    EMPNO
PRIOR EMPNO

vs top-down

PRIOR MGR
PRIOR EMPNO  MGR
             EMPNO

Upvotes: 0

Dmitriy
Dmitriy

Reputation: 5565

Hierarchy means, that one row of a table is a "parent", and another one - a "child". PRIOR is used to show who is who. Clause CONNECT BY PRIOR EMPNO = MGR means, that if two rows have the same value, but one row in a column EMPNO, and the second - in a column MGR, then second is a "parent" and first is a "child". So, query

SELECT EMPNO,ENAME,MGR,LEVEL
FROM TMP_PCH
CONNECT BY PRIOR EMPNO = MGR
START WITH MGR = 'John'

returns all subordinates of John (and John itself), and query

SELECT EMPNO,ENAME,MGR,LEVEL
FROM TMP_PCH
CONNECT BY PRIOR MGR = EMPNO
START WITH MGR = 'John'

returns all bosses of John (and John itself).

Upvotes: 3

Related Questions