| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Recursive select
A copy of this was sent to buurd_at_my-deja.com
(if that email address didn't require changing)
On Fri, 19 Nov 1999 14:33:59 GMT, you wrote:
>Thanks for your answer... it took me a little bit further but ........
>I've tried connect by but i can't make it work in my queries (usually
>with a join or two) since connect by don't like joins..... so i'm a
>little bit puzzled of how to use connect by....
>
>if i expand your example with a department_id that joins with a tables
>with the full names of the departments... how do i do that?? Is it
>possible to do in one query?
>
you cannot CONNECT BY a join but you can join to a CONNECT by... consider:
scott_at_8i> select dname, a.*
2 from ( select ename, empno, mgr, deptno
3 from emp 4 start with ename = 'SMITH' 5 connect by prior mgr = empno ) a, 6 dept
DNAME ENAME EMPNO MGR DEPTNO -------------------- ---------- ---------- ---------- ---------- ACCOUNTING KING 7839 10 RESEARCH SMITH 7369 7902 20 RESEARCH FORD 7902 7566 20 RESEARCH JONES 7566 7839 20
Or, if you might find that this works nicely:
scott_at_8i> create or replace function get_dname( p_deptno in dept.deptno%type )
return dept.dname%type
2 is
3 l_dname dept.dname%type;
4 begin
5 select dname into l_dname from dept where deptno = p_deptno;
6
7 return l_dname;
8 end;
9 /
Function created.
scott_at_8i> scott_at_8i> column dname format a20 scott_at_8i> select ename, empno, mgr, get_dname(deptno) dname 2 from emp 3 start with ename = 'SMITH' 4 connect by prior mgr = empno
ENAME EMPNO MGR DNAME
---------- ---------- ---------- -------------------- SMITH 7369 7902 RESEARCH FORD 7902 7566 RESEARCH JONES 7566 7839 RESEARCH KING 7839 ACCOUNTING
Or, in Oracle8i, release 8.1 you can:
scott_at_8i> REM Oracle8i release 8.1 specific syntax: scott_at_8i> scott_at_8i> select ename, empno, mgr, ( select dname from dept b where b.deptno = emp.deptno ) dname 2 from emp 3 start with ename = 'SMITH' 4 connect by prior mgr = empno
ENAME EMPNO MGR DNAME
---------- ---------- ---------- -------------------- SMITH 7369 7902 RESEARCH FORD 7902 7566 RESEARCH JONES 7566 7839 RESEARCH KING 7839 ACCOUNTING
>Tia
>Roland
>In article <uyw0OEb695aC0qQ+1fWkbKE0ibLi_at_4ax.com>,
> tkyte_at_us.oracle.com wrote:
>> A copy of this was sent to buurd_at_my-deja.com
>> (if that email address didn't require changing)
>> On Thu, 18 Nov 1999 15:39:59 GMT, you wrote:
>>
>> > Hi... i want to ask a question like this:
>> >List all ancesters from person p. No only the closest one but ALL
>that
>> >is in the table. My table looks (somewhat simplified) like this
>> >
>> >table related(
>> > id int,
>> > name varchar2(30),
>> > parent int
>> >)
>> >
>> >Tia
>>
>> look at the connect by clause:
>>
>> 1 select ename, empno, mgr
>> 2 from emp
>> 3 start with ename = 'SMITH'
>> 4* connect by prior mgr = empno
>> scott_at_8.0> /
>>
>> ENAME EMPNO MGR
>> ---------- ---------- ----------
>> SMITH 7369 7902
>> FORD 7902 7566
>> JONES 7566 7839
>> KING 7839
>>
>> here SMITH works for FORD works for JONES works for KING. we started
>with smith
>> and then took smiths MGR field and found the matching EMPNO (ford's
>record). it
>> walks the tree for us.
>>
>> --
>> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
>Oracle8i'...
>> Current article is "Part I of V, Autonomous Transactions" updated
>June 21'st
>>
>> Thomas Kyte tkyte_at_us.oracle.com
>> Oracle Service Industries Reston, VA USA
>>
>> Opinions are mine and do not necessarily reflect those of Oracle
>Corporation
>>
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Nov 19 1999 - 09:47:50 CST
 |
 |