Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

HELP with hierarchical query

0 views
Skip to first unread message

Christopher Reimold

unread,
Jan 10, 1996, 3:00:00 AM1/10/96
to
I want to do a bottom-up hierarchical query on the scott/tiger database which starts with the
"lowest" employees (employees without managers) as roots and shows their respective bosses:
(This is basically the example in the Oracle manuals - just the other way round.)

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
--------------------------------------------------------------------


Geoff Ingram

unread,
Jan 10, 1996, 3:00:00 AM1/10/96
to
Try SQL below. I think by "employees without managers" you probably mean
"employees who don't manage anyone":

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


Christopher Reimold

unread,
Jan 13, 1996, 3:00:00 AM1/13/96
to
>Try SQL below. I think by "employees without managers" you probably mean
>"employees who don't manage anyone":

Thanks a lot! It works but I still wonder what was wrong with my original query?

Christopher


0 new messages