| how can i get recursive values thru query [message #1439] |
Tue, 30 April 2002 21:41  |
ram
Messages: 95
Registered: November 2000
|
Member |
|
|
hi to all,
Can anybody help me to retrieve values from a tree.
Suppose
EMP1
|--------------
EMP2 EMP3
|
|----------| EMP6
EMP4 EMP5
Lets suppose table structure is
EID, EName, MgrID
if we takes MgrID as EMP1 , then i have to get all the
sublevels ids as EMP2,EMP4,EMP5,EMP3,EMP6
if we takes MgrID as EMP3 then it should give that sublevel employee as EMP6.
if we takes MgrID as EMP2 then it should give that sublevel employee as EMP4,EMP5.
Is there any possibility a query or procedure for getting like this,
plz give me reply asap
Thanks in advance
Have a nice time
|
|
|
|
| Re: how can i get recursive values thru query [message #1451 is a reply to message #1439] |
Wed, 01 May 2002 08:39  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
hope this helps you.
****************************************************
this sql is just for our understanding. the SQL you want is give at the end.
****************************************************
SQL> select
2 lpad(' ',2*(level-1))|| ename org_chart,
3 e.empno, e.mgr, e.job, e.deptno
4 from emp e
5 connect by prior empno = mgr
6 start with mgr is null
7 ;
ORG_CHART EMPNO MGR JOB DEPTNO
--------------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 10
JONES 7566 7839 MANAGER 20
SCOTT 7788 7566 ANALYST 20
ADAMS 7876 7788 CLERK 20
FORD 7902 7566 ANALYST 20
SMITH 7369 7902 CLERK 20
BLAKE 7698 7839 MANAGER 30
ALLEN 7499 7698 SALESMAN 30
WARD 7521 7698 SALESMAN 30
MARTIN 7654 7698 SALESMAN 30
TURNER 7844 7698 SALESMAN 30
JAMES 7900 7698 CLERK 30
CLARK 7782 7839 MANAGER 10
MILLER 7934 7782 CLERK 10
14 rows selected.
SQL> get tr
1 select
2 lpad(' ',2*(level-1))|| ename org_chart,
3 e.empno, e.mgr, e.job, e.deptno
4 from emp e
5 connect by prior empno = mgr
6* start with mgr =7566
7 /
ORG_CHART EMPNO MGR JOB DEPTNO
--------------- ---------- ---------- --------- ----------
SCOTT 7788 7566 ANALYST 20
ADAMS 7876 7788 CLERK 20
FORD 7902 7566 ANALYST 20
SMITH 7369 7902 CLERK 20
4 rows selected.
SQL>
****************************************
but to make the job done, this simple sql will do
******************************************
SQL> ed
Wrote file afiedt.buf
1 select empno,ENAME
2 from EMP
3 connect by prior EMPNO = MGR
4* start with MGR =7566
SQL> /
EMPNO ENAME
---------- ----------
7788 SCOTT
7876 ADAMS
7902 FORD
7369 SMITH
4 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
