Trouble with hierarchical query
Vince
results in a particular way. Let's say I have the following data
(straight from Oracle's documentation):
ORG_CHART EMPNO MGR JOB
------------ ---------- ---------- ---------
KING 7839 PRESIDENT
JONES 7566 7839 MANAGER
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
BLAKE 7698 7839 MANAGER
ALLEN 7499 7698 SALESMAN
WARD 7521 7698 SALESMAN
MARTIN 7654 7698 SALESMAN
TURNER 7844 7698 SALESMAN
JAMES 7900 7698 CLERK
CLARK 7782 7839 MANAGER
MILLER 7934 7782 CLERK
I would like to query that table and retrieve two columns: an ancestor
(always level 1), and one of his descendants (level > 1). For
example, I would like to query the table for Jones and Blake (i.e.
"start with empno in (7566, 7698)") and return the following result
set:
7566 (Jones) 7566
7566 7788
7566 7876
7566 7902
7566 7369
7698 (Blake) 7698
7698 7499
7698 7521
7698 7654
7698 7844
7698 7900
I can't seem to get those two columns side-by-side.
Any ideas? Help!
Thanks,
Vince
Vince
result set in the format I want. Consider the following data (taken
from Oracle's documentation):
ORG_CHART EMPNO MGR JOB
------------ ---------- ---------- ---------
KING 7839 PRESIDENT
JONES 7566 7839 MANAGER
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
BLAKE 7698 7839 MANAGER
ALLEN 7499 7698 SALESMAN
WARD 7521 7698 SALESMAN
MARTIN 7654 7698 SALESMAN
TURNER 7844 7698 SALESMAN
JAMES 7900 7698 CLERK
CLARK 7782 7839 MANAGER
MILLER 7934 7782 CLERK
I would like to be able to query for a result set that includes 2
columns: the ancestor from my IN clause (level = 1), and the
descendant (level > 1). For example, I would like a single query
where I can specify a where condition to retrieve the children of
Jones and Clark (such as "emp_no in (7566, 7782)"). It should return
the following result set:
7566 7566
7566 7788
7566 7876
7566 7902
7566 7369
7782 7782
7782 7934
I can easily get a list of any one ancestor and his children, but I
can't get a list of multiple ancestors and their children.
Thomas Kyte
vfa...@covansys.com says...
Sigh, no version....
Ok, in 8i and up:
sc...@ORA817.US.ORACLE.COM> select empno, (select empno
2 from emp e2
3 where empno in ( 7566, 7782 )
4 start with e2.empno = emp.empno
5 connect by prior mgr = empno ) top_o_the_tree
6 from emp
7 start with empno in ( 7566, 7782 )
8 connect by prior empno = mgr
9 /
EMPNO TOP_O_THE_TREE
---------- --------------
7566 7566
7788 7566
7876 7566
7902 7566
7369 7566
7782 7782
7934 7782
7 rows selected.
in 8.0 and before, you have to hide the select we select in the select list in a
PLSQL function.
in 9i, you can:
sc...@ORA9I.WORLD> select empno, nvl( substr( path, 2, instr(path,'/', 2 )-2 ),
substr(path,2)) mgr
2 from ( select empno, sys_connect_by_path( empno, '/' ) path
3 from emp
4 start with empno in ( 7566, 7782 )
5 connect by prior empno = mgr
6 )
7 /
EMPNO MGR
---------- --------------------
7566 7566
7788 7566
7876 7566
7902 7566
7369 7566
7782 7782
7934 7782
7 rows selected.
the connect by path returns the path of the tree we took to get to a node:
sc...@ORA9I.WORLD> select empno, path mgr
2 from ( select empno, sys_connect_by_path( empno, '/' ) path
3 from emp
4 start with empno in ( 7566, 7782 )
5 connect by prior empno = mgr
6 )
7 /
EMPNO MGR
---------- --------------------
7566 /7566
7788 /7566/7788
7876 /7566/7788/7876
7902 /7566/7902
7369 /7566/7902/7369
7782 /7782
7934 /7782/7934
7 rows selected.
--
Thomas Kyte (tk...@us.oracle.com) http://asktom.oracle.com/
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/
Opinions are mine and do not necessarily reflect those of Oracle Corp
Thomas Kyte
Sigh, no version....
Ok, in 8i and up:
sc...@ORA817.US.ORACLE.COM> select empno, (select empno
2 from emp e2
3 where empno in ( 7566, 7782 )
4 start with e2.empno = emp.empno
5 connect by prior mgr = empno ) top_o_the_tree
6 from emp
7 start with empno in ( 7566, 7782 )
8 connect by prior empno = mgr
9 /
EMPNO TOP_O_THE_TREE
---------- --------------
7566 7566
7788 7566
7876 7566
7902 7566
7369 7566
7782 7782
7934 7782
7 rows selected.
in 8.0 and before, you have to hide the select we select in the select list in a
PLSQL function.
in 9i, you can:
sc...@ORA9I.WORLD> select empno, nvl( substr( path, 2, instr(path,'/', 2 )-2 ),
substr(path,2)) mgr
2 from ( select empno, sys_connect_by_path( empno, '/' ) path
3 from emp
4 start with empno in ( 7566, 7782 )
5 connect by prior empno = mgr
6 )
7 /
EMPNO MGR
---------- --------------------
7566 7566
7788 7566
7876 7566
7902 7566
Thomas Kyte
Sigh, no version....
Ok, in 8i and up:
sc...@ORA817.US.ORACLE.COM> select empno, (select empno
2 from emp e2
3 where empno in ( 7566, 7782 )
4 start with e2.empno = emp.empno
5 connect by prior mgr = empno ) top_o_the_tree
6 from emp
7 start with empno in ( 7566, 7782 )
8 connect by prior empno = mgr
9 /
EMPNO TOP_O_THE_TREE
---------- --------------
7566 7566
7788 7566
7876 7566
7902 7566
7369 7566
7782 7782
7934 7782
7 rows selected.
in 8.0 and before, you have to hide the select we select in the select list in a
PLSQL function.
in 9i, you can:
sc...@ORA9I.WORLD> select empno, nvl( substr( path, 2, instr(path,'/', 2 )-2 ),
substr(path,2)) mgr
2 from ( select empno, sys_connect_by_path( empno, '/' ) path
3 from emp
4 start with empno in ( 7566, 7782 )
5 connect by prior empno = mgr
6 )
7 /
EMPNO MGR
---------- --------------------
7566 7566
7788 7566
7876 7566
7902 7566