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 -> CONNECT BY Questions...

CONNECT BY Questions...

From: Jimmy <david_petit_at_yahoo.com>
Date: Fri, 15 Oct 1999 22:49:06 -0700
Message-ID: <380811D1.33D49030@yahoo.com>


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 Received on Sat Oct 16 1999 - 00:49:06 CDT

Original text of this message

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