Re: Hierarchical Query revisited

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/04/03
Message-ID: <3524eec7.28114095@www.sigov.si>#1/1

On 3 Apr 1998 10:09:48 GMT, "Wolfgang Hann" <W.Hann_at_netway.at> wrote:

>Anybody of you has or had the problem to
>follow one branch of a hierarchical structure beginning
>from the leaf object.
>
>The problem is that connect by.... can only
>show you the complete tree. In addition it allows
>you only to start your query from the parent.
>
>I´ve solved this one very unnicely by using a
>Cursor Open/Close Loop which works but
>i don´t think is the fastest one.
>
>Any ideas?

Here is a complete hierarchy of the SCOTT.EMP table:

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

ORG_CHART MGR JOB DEPTNO --------------- --------- --------- ---------

KING                      PRESIDENT        10
  JONES              7839 MANAGER          20
    SCOTT            7566 ANALYST          20
      ADAMS          7788 CLERK            20
    FORD             7566 ANALYST          20
      SMITH          7902 CLERK            20
  BLAKE              7839 MANAGER          30
    ALLEN            7698 SALESMAN         30
    WARD             7698 SALESMAN         30
    MARTIN           7698 SALESMAN         30
    TURNER           7698 SALESMAN         30
    JAMES            7698 CLERK            30
  CLARK              7839 MANAGER          10
    MILLER           7782 CLERK            10

14 rows selected.

Now here are just branches listing the hierarchies under each MANAGER:

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

ORG_CHART MGR JOB DEPTNO --------------- --------- --------- ---------

JONES                7839 MANAGER          20
  SCOTT              7566 ANALYST          20
    ADAMS            7788 CLERK            20
  FORD               7566 ANALYST          20
    SMITH            7902 CLERK            20
BLAKE                7839 MANAGER          30
  ALLEN              7698 SALESMAN         30
  WARD               7698 SALESMAN         30
  MARTIN             7698 SALESMAN         30
  TURNER             7698 SALESMAN         30
  JAMES              7698 CLERK            30
CLARK                7839 MANAGER          10
  MILLER             7782 CLERK            10

13 rows selected.

And here is just a hierarchy under manager JONES:

SQL> SELECT SUBSTR(LPAD(' ',2*(LEVEL-1))||ename,1,15) AS org_chart,   2 mgr, job, deptno FROM emp
  3 START WITH job = 'MANAGER' AND ename = 'JONES'   4 CONNECT BY PRIOR empno = mgr;

ORG_CHART MGR JOB DEPTNO --------------- --------- --------- ---------

JONES                7839 MANAGER          20
  SCOTT              7566 ANALYST          20
    ADAMS            7788 CLERK            20
  FORD               7566 ANALYST          20
    SMITH            7902 CLERK            20

SQL> Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Apr 03 1998 - 00:00:00 CST