SELECT empno || lpad (' ',3*level) || ename || ' ' ||mgr
CONNECT BY empno = PRIOR mgr
START WITH empno != ANY
(SELECT DISTINCT MGR FROM EMP
WHERE MGR IS NOT NULL)
Yet this query always picks employees at higher levels in the company's hierarchy (e.g. 7839 KING)
as roots of the query. I want only the employees without managers to be on level 1 - why doesn't it
work?
Thanks,
Christopher
--------------------------------------------------------------------
Christopher Reimold * Jahnstr. 24 * 50676 Koeln * Tel. 0221-2407203
a262...@smail.rrz.uni-koeln.de, rei...@rrz.uni-koeln.de
--------------------------------------------------------------------
SELECT empno || lpad (' ',3*level) || ename || ' ' ||mgr
from emp
CONNECT BY empno = PRIOR mgr
START WITH empno = ANY
(SELECT EMPNO FROM EMP
WHERE EMPNO NOT IN
(SELECT nvl(MGR,0) FROM EMP)
)
EMPNO||LPAD('',3*LEVEL)||ENAME||''||MGR
----------------------------------------
7369 SMITH 7902
7902 FORD 7566
7566 JONES 7839
7839 KING
7499 ALLEN 7698
7698 BLAKE 7839
7839 KING
7521 WARD 7698
7698 BLAKE 7839
7839 KING
7654 MARTIN 7698
7698 BLAKE 7839
7839 KING
7844 TURNER 7698
7698 BLAKE 7839
7839 KING
7876 ADAMS 7788
7788 SCOTT 7566
7566 JONES 7839
7839 KING
7900 JAMES 7698
7698 BLAKE 7839
7839 KING
7934 MILLER 7782
7782 CLARK 7839
7839 KING
regards Geoff Ingram
--------------------
all my own personal opinions
Thanks a lot! It works but I still wonder what was wrong with my original query?
Christopher