Re: how to do when joints forbidden in hierarchical queries

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Fri, 25 Sep 1998 20:52:34 GMT
Message-ID: <360c01ee.24633501@dcsun4.us.oracle.com>

On Fri, 25 Sep 1998 20:39:14 +0200, gil guerillot <gg200492_at_club-internet.fr> wrote:

>example of oracle training table
>
>table emp:
>empno
>ename
>job
>mgr
>hiredate
>sal
>comm
>deptno
>
>
>table deptno:
>deptno
>dname
>loc
>
>
> the following query the hierarchy of employees :
>
><<select level,ename
>from emp
>connect by mgr=prior empno
>start with mgr is null >>
>
>joints being forbidden in hierarchical queries ,how to add the fields
>dname and loc in the select clause of the query for obtain a result
>like select level,ename,dname,loc ...
>

Try an inline view.

eg.

select a.l, a.ename, d.dname, d.loc
  from dept d,

       ( select level l, ename, deptno
           from emp
        connect by mgr=prior empno
          start with mgr is null ) a

 where a.deptno = d.deptno
/

chris. Received on Fri Sep 25 1998 - 15:52:34 CDT