Child to Parent

[csvenkata]

Hi All,
I want to get the parent records of the child recs. If I want to do the reverse ie Parent to child then I use Level,connect by and prior functions. I want to do the reverse way. Is there any way or method.

TIA

[jmodic]

The trick is to reverse the side of the PROR keyword in the CONNECT BY clause. For example, in a clasical SCOTT.EMP table, the hierarchy from KING (president) downwards to all lower level is find by:

SELECT level, ename name, empno, mgr
FROM emp
START WITH ename = 'KING'
CONNECT BY PRIOR empno = mgr;

Now if you want to find all the supperiors of ADAMS, you would switch the keyword PRIOR from empno to mgr:

SELECT level, ename name, empno, mgr
FROM emp
START WITH ename = 'ADAMS'
CONNECT BY empno =  PRIOR mgr;