Re: HALP !!!! HOW DO YOU GET ALL THE PARENTS OF A CHILD IN A HEIRARCHICAL QEY

From: Jurij Modic <jmodic_at_src.si>
Date: Tue, 16 Mar 1999 21:54:33 GMT
Message-ID: <36eecde0.2703600@news.siol.net>

On Tue, 16 Mar 1999 08:14:45 GMT, bonanos_at_yahoo.com wrote:

>The subject says it all

Simply apply the PRIOR operator to the parent side of the CONNECT BY clause instead of the child side. Consider the following example:

SQL> SELECT SUBSTR(LPAD(' ',2*(LEVEL-1))||ename,1,15) name, empno, mgr   2 FROM emp
  3 START WITH ename = 'KING'
  4 CONNECT BY PRIOR empno = mgr;

NAME                 EMPNO        MGR
--------------- ---------- ----------
KING                  7839
  JONES               7566       7839
    SCOTT             7788       7566
      ADAMS           7876       7788
    FORD              7902       7566
      SMITH           7369       7902
  BLAKE               7698       7839
    ALLEN             7499       7698
    WARD              7521       7698
    MARTIN            7654       7698
    TURNER            7844       7698
    JAMES             7900       7698
  CLARK               7782       7839
    MILLER            7934       7782

14 rows selected.

Now if you want to see all the parents and grandparents of the SMITH, use the following (note the change of position of the PRIOR operator in the command line 4):

SQL> SELECT SUBSTR(LPAD(' ',2*(LEVEL-1))||ename,1,15) name, empno, mgr   2 FROM emp
  3 START WITH ename = 'SMITH'
  4 CONNECT BY empno = PRIOR mgr;

NAME                 EMPNO        MGR
--------------- ---------- ----------
SMITH                 7369       7902
  FORD                7902       7566
    JONES             7566       7839
      KING            7839

SQL> HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Mar 16 1999 - 15:54:33 CST