Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: CONNECT BY Questions...

Re: CONNECT BY Questions...

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 16 Oct 1999 16:27:17 GMT
Message-ID: <7ua915$379$4@news.seed.net.tw>

Jimmy <david_petit_at_yahoo.com> wrote in message news:38081118.7CCA03DD_at_yahoo.com...
> Hello all,

[snip]
> 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 first
> level.

Not really true.
The order is depended of how Oracle reads records. I am not sure what you mean?
Is the following what you want?

SQL> select lpad(' ',2*level)||ename||','||job   2 from aaa
  3 connect by prior empno = mgr
  4 start with job = 'President'
  5 order by level
  6 /

LPAD('',2*LEVEL)||ENAME||','||JOB



  A,President
    B,System Analyst
      E,System Analyst2
      C,System Analyst2
      D,System Analyst2
        G,Programmer
        H,Programmer

7 rows selected.

But, doing this loses the subordination info.

 > 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
>

SQL> select lpad(' ',2*level)||ename||','||job   2 from aaa
  3 where rownum=level
  4 connect by prior empno = mgr
  5 start with job = 'President'
  6 /

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 am not sure what you want.

> 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?

CONNECT BY is not a SQL standard feature, you can use ALTER SESSION SET FLAGGER command to check it.

SQL> alter session set flagger=entry;

Session altered.

SQL> select lpad(' ',2*level)||ename||','||job   2 from aaa
  3 connect by prior empno = mgr
  4 start with job = 'President'
  5 /
  from aaa

       *
ERROR at line 2:
ORA-00097: Use of Oracle SQL feature not in SQL92 Entry Level Received on Sat Oct 16 1999 - 11:27:17 CDT

Original text of this message

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