Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: CONNECT BY Questions...
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
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
> --------------------------------------------------------------------------
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
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