Re: CONNECT BY Questions...
Date: Tue, 19 Oct 1999 19:24:33 GMT
Message-ID: <7uigh1$ph4$1_at_nnrp1.deja.com>
In article <380811D1.33D49030_at_yahoo.com>,
Jimmy <david_petit_at_yahoo.com> wrote:
> Hello all,
>
> I create a recursive table aaa. I want to do the query on this table.
> Here is what I want to do:
>
> SQL> create table aaa
> 2 (mgr number(5),
> 3 empno number(5),
> 4 ename varchar2(30),
> 5 job varchar2(30));
>
> SQL> insert into aaa (mgr, empno, ename,job) values (null,
> 1,'A','President');
> SQL> insert into aaa (mgr, empno, ename,job) values (1, 2, 'B','System
> Analyst');
> SQL> insert into aaa (mgr, empno, ename,job) values (2, 6, 'C','System
> Analyst2');
> SQL> insert into aaa (mgr, empno, ename,job) values (2, 7, 'D','System
> Analyst2');
> SQL> insert into aaa (mgr, empno, ename,job) values (3,
> 5,'G','Programmer');
> SQL> insert into aaa (mgr, empno, ename,job) values (3,
> 6,'H','Programmer');
> SQL> insert into aaa (mgr, empno, ename,job) values (2, 3, 'E','System
> Analyst2');
> SQL>
> SQL> select lpad(' ',2*level) || ename || ',' || job from aaa connect
by
> prior empno = mgr start with job = 'President';
>
> (CASE I)
> LPAD('',2*LEVEL)||ENAME||','||JOB
> ---------------------------------
> A,President
> B,System Analyst
> C,System Analyst2
> D,System Analyst2
> E,System Analyst2
> G,Programmer
> H,Programmer
>
> SQL> truncate table aaa;
> SQL> insert into aaa (mgr, empno, ename,job) values (null,
> 1,'A','President');
> SQL> insert into aaa (mgr, empno, ename,job) values (1, 2, 'B','System
> Analyst');
> SQL> insert into aaa (mgr, empno, ename,job) values (2, 3, 'E','System
> Analyst2');
> SQL> insert into aaa (mgr, empno, ename,job) values (2, 6, 'C','System
> Analyst2');
> SQL> insert into aaa (mgr, empno, ename,job) values (2, 7, 'D','System
> Analyst2');
> SQL> insert into aaa (mgr, empno, ename,job) values (3, 5,
> 'G','Programmer');
> SQL> insert into aaa (mgr, empno, ename,job) values (3,
> 6,'H','Programmer');
> SQL>
> SQL> select lpad(' ',2*level) || ename || ',' || job from aaa connect
by
> prior empno = mgr start with job = 'President';
>
> (CASE II)
> LPAD('',2*LEVEL)||ENAME||','||JOB
> ---------------------------------
> A,President
> B,System Analyst
> E,System Analyst2
> G,Programmer
> H,Programmer
> C,System Analyst2
> D,System Analyst2
>
> 1) I find that the output is depended on the rows inserted order. How
> can I rewrite the SQL statement such that I always get the result of
> case I? i.e. the result in breathe first level, not in depth
firstlevel.
>
> 2) Is is possible to get the following result by using one SQL
> statement? (i.e. if more than one entries in each level, just show the
> first entry)
>
> LPAD('',2*LEVEL)||ENAME||','||JOB
> ----------------------------------------------------------------------
>
> A,President
> B,System Analyst
> E,System Analyst2
> G,Programmer
>
> 3) Can I get the above two requirements by using one SQL statement?
>
> I find that using CONNECT BY is not an easy job. Could anyone
suggests
> me the books on teaching how to use CONNECT BY? Is CONNECT BY only
used
> in Oracle? Not SQL standard?
>
> Thanks
> Jimmy
>
>
There is a good chapter in The Complete Reference.
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Oct 19 1999 - 21:24:33 CEST