Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Hierarchical Query revisited

Re: Hierarchical Query revisited

From: Peter Schneider <peter.schneider_at_okay.net>
Date: Wed, 08 Apr 1998 20:11:25 GMT
Message-ID: <352bd6aa.4875414@news.ipf.net>


On 8 Apr 1998 09:08:07 GMT, "Wolfgang Hann" <W.Hann_at_netway.at> wrote:

[...]

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

Ermm, this is in the manual. Have a look in the subchapter about hierarchical queries in the Oracle7 Server SQL Reference Manual under 4. Commands - SELECT. There is a pretty illustrative example and detailed explanations of how each of the clauses START WITH, CONNECT BY and WHERE work in the context of a recursive query.

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

You cannot have a subquery in the CONNECT BY clause, although this would be extremly useful in many situations :(( But you definitely can have it in the START WITH clause:

SQL> r
  1 SELECT SUBSTR(LPAD(' ',2*(LEVEL-1))||e.ename,1,15) AS org_chart,

  2         e.mgr, e.job, e.deptno
  3     FROM emp e

  4 CONNECT BY PRIOR e.empno = e.mgr
  5 START WITH e.empno IN
  6         (SELECT e2.empno
  7            FROM emp e2
  8*          WHERE job = 'ANALYST')

ORG_CHART             MGR JOB          DEPTNO
--------------- --------- --------- ---------
SCOTT                7566 ANALYST          20
  ADAMS              7788 CLERK            20
FORD                 7566 ANALYST          20
  SMITH              7902 CLERK            20

SQL> HTH,
Peter

--
Peter Schneider
peter.schneider_at_okay.net Received on Wed Apr 08 1998 - 15:11:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US