Re: Hierarchical Query revisited

> 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
>

Thatīs exactly the point. Iīve already tried it and now it works perfectly. Puuh! Itīs just that easy - just turn around the connect by prior statement.

I suggest Oracle should put that example in their manual.

In addition it would be very nice to be able to use a subquery beneath the start with clause for example

start with job=(select job from job_list).

Currently this can not be done because Oracle expects not have a multi row query.   Received on Wed Apr 08 1998 - 04:08:07 CDT