Re: Hierarchical Query revisited

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/04/05
Message-ID: <3527fb1f.4648994@www.sigov.si>#1/1

On Fri, 03 Apr 1998 14:26:13 GMT, jurij.modic_at_mf.sigov.mail.si (Jurij Modic) wrote:

>On 3 Apr 1998 10:09:48 GMT, "Wolfgang Hann" <W.Hann_at_netway.at> wrote:
>
>> ...[SNIP]....
>>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.
>>
> ....[SOME EXAMPLES OF MY PREVIOUS POST SNIPED]...
>
>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
>

Maybe I missed Wolfgang's point in my previous post. If I understand him correctly now, he wants the hierarchy, leading from the choosen leaf objects toward the root. So in the above example, the single branch that leads from let's say SMITH to the top of the hierarchy (i.e. SMITH -> FORD -> JONES -> ....).

This can be done by switching the empno and mgr in the CONNECT BY PRIOR clause:

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

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

SMITH                7902 CLERK            20
  FORD               7566 ANALYST          20
    JONES            7839 MANAGER          20
      KING                PRESIDENT        10

SQL> In this case the lowest object in the hierarchy gets assigned the lowest level (LEVEL=1), so the ordering is reversed - from lowest to the highest.

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 Sun Apr 05 1998 - 00:00:00 CST