Re: Hierarchical Query revisited

Wolfgang Hann wrote in article >>
>
> 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.
>

A post by Modic showed that Oracle can select only part of the tree by using restrictions (conditions) in the where clause and even though you start at the parent it is possible to go up or down the tree by switching the parent-child relationship through movement of the prior keyword across the relational operator:

select lpad(' ',2 * (level - 1))||ename ename, empno, mgr, sal, level, job from emp
start with empno = '7788'
connect by prior mgr = empno

/                             
ENAME          EMPNO   MGR        SAL      LEVEL JOB
-------------  ----- ---------- ---------- -----  ----
SCOTT           7788  7566       3000          1 ANALYST
  JONES         7566  7839       2975          2 MANAGER
    KING        7839             5000          3 PRESIDENT