Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Hierarchical Query revisited
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
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
![]() |
![]() |