Puzzle of the Week #21: Management Report

Puzzle of the Week #21:

Produce a report that shows employee name, his/her immediate manager name, and the next level manager name. The following conditions should be met:

  • Use Single SELECT statement only
  • Use mgr column to identify employee’s immediate manager
  • The query should work in Oracle 11g.
  • A preferred solution should use only a single instance of emp table.

Expected Result:

NAME1      NAME2      NAME3
---------- ---------- ------
SMITH      FORD       JONES
ALLEN      BLAKE      KING
WARD       BLAKE      KING
JONES      KING
MARTIN     BLAKE      KING
BLAKE      KING
CLARK      KING
SCOTT      JONES      KING
KING
TURNER     BLAKE      KING
ADAMS      SCOTT      JONES
JAMES      BLAKE      KING
FORD       JONES      KING
MILLER     CLARK      KING

To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.


					
Advertisements

7 thoughts on “Puzzle of the Week #21: Management Report

  1. Krishna Jamal August 21, 2016 / 4:44 am

    WITH x AS
    (SELECT Empno, Ename, PRIOR Ename MGR_Name1
    FROM Emp
    START WITH MGR IS NULL
    CONNECT BY PRIOR Empno = MGR),
    y AS
    (SELECT Empno, PRIOR MGR_Name1 MGR_Name2
    FROM x
    START WITH MGR_Name1 IS NULL
    CONNECT BY PRIOR Ename = MGR_Name1)
    SELECT E.Ename “Name1”,
    (SELECT MGR_Name1 FROM x WHERE x.Empno = E.Empno) “Name2”,
    (SELECT MGR_Name2 FROM y WHERE y.Empno = E.Empno) “Name3”
    FROM Emp E;

  2. Taroon Tay August 23, 2016 / 12:08 pm

    WITH A AS(
    SELECT DISTINCT ENAME, PRIOR ENAME MANAGER
    FROM EMP
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR
    )
    SELECT Y.ENAME NAME1, X.ENAME NAME2, X.MANAGER NAME3 FROM A X, A Y
    WHERE X.ENAME(+) = Y.MANAGER;

  3. Taroon Ray August 23, 2016 / 12:28 pm

    WITH A AS(
    SELECT DISTINCT ENAME, PRIOR ENAME MANAGER
    FROM EMP
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR
    )
    SELECT Y. ENAME NAME1, X. ENAME NAME2, X. MANAGER NAME3 FROM A X, A Y
    WHERE X. ENAME(+) =Y. MANAGER;

  4. Harikrishna August 24, 2016 / 8:50 am

    select e.ename,m.ename,mm.ename from emp e,emp m,emp mm where e.mgr=m.empno(+) and m.mgr=mm.empno(+) order by e.rowid;

  5. Krishna Jamal August 25, 2016 / 12:27 pm

    SELECT Ename Name1, PRIOR Ename Name2,
    DECODE(LEVEL,
    3, CONNECT_BY_ROOT(Ename),
    4, TRIM(BOTH ‘ ‘ FROM
    REPLACE(
    REPLACE(SYS_CONNECT_BY_PATH(PRIOR Ename, ‘ ‘), PRIOR Ename),
    CONNECT_BY_ROOT(Ename)))
    ) Name3
    FROM Emp
    START WITH Ename = ‘KING’
    CONNECT BY PRIOR Empno = MGR
    ORDER BY ROWID;

  6. Krishna Jamal August 29, 2016 / 2:34 am

    SELECT E1 Name1, E2 Name2, PRIOR E2 Name3
    FROM (
    SELECT ROWID Rn, Ename E1, PRIOR Ename E2
    FROM Emp
    CONNECT BY PRIOR Empno = MGR
    START WITH MGR IS NULL
    ORDER BY ROWID
    )
    CONNECT BY PRIOR E1 = E2
    START WITH E2 IS NULL
    ORDER BY Rn;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s