Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> CONNECT BY Questions...
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
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
E,System Analyst2 G,Programmer H,Programmer C,System Analyst2 D,System Analyst2
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
Received on Sat Oct 16 1999 - 00:49:06 CDT