2

I have a employee-->Manager hierarchy and using the following query:

SELECT  EMP_ID, FIRST_NAME CONNECT_BY_ISCYCLE "CYCLE" FROM  MYTABLE CONNECT by NOCYCLE PRIOR MANAGER_EMP_ID = EMP_ID START WITH EMP_ID = '12345'

We are getting the following as the output

12345   Nan 0
A12345  John 0
B12345  Dumble 0
C12345 Mark 0
D12345 Shady 0
E12345 Yenn 1

The problem here is because the CEO (F12345) is his owner manager, a LOOP occurs and hence , I am not getting the CEO record at the end of the table. My expected output is:

12345   Nan 0
A12345  John 0
B12345  Dumble 0
C12345 Mark 0
D12345 Shady 0
E12345 Yenn 1
*F1345 Don*

Is there a way I can retrieve the LOOP record too? If there is no other way, I have to probably do two query and fetch the CEO is the second one.

Any guidance is appreciated.

1 Answer 1

1

You could use what your query returns to get the extra row that have CONNECT_BY_ISCYCLE=1

WITH a AS
(
SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID, CONNECT_BY_ISCYCLE
FROM  MYTABLE 
   CONNECT BY NOCYCLE
    PRIOR MANAGER_EMP_ID = EMP_ID 
    START WITH EMP_ID = '12345'
)
SELECT EMP_ID, FIRST_NAME
FROM a

UNION ALL

SELECT m.EMP_ID, m.FIRST_NAME
FROM MYTABLE m
  JOIN a
    ON a.MANAGER_EMP_ID = m.EMP_ID
WHERE a.CONNECT_BY_ISCYCLE = 1 ;

Or use a recursive CTE:

WITH a (EMP_ID, FIRST_NAME, MANAGER_EMP_ID) AS
(
SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID
FROM  MYTABLE 
WHERE EMP_ID = '12345'

UNION ALL

SELECT  m.EMP_ID, m.FIRST_NAME, m.MANAGER_EMP_ID
FROM  MYTABLE m 
  JOIN a
    ON  a.MANAGER_EMP_ID = m.EMP_ID
    AND a.MANAGER_EMP_ID <> a.EMP_ID   -- to avoid the cycles
)
SELECT EMP_ID, FIRST_NAME
FROM a ;

Another way to avoid cycles in a recursive CTE is to use the CYCLE clause (available only in Oracle):

WITH a (EMP_ID, FIRST_NAME, MANAGER_EMP_ID) AS
(
SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID
FROM  MYTABLE 
WHERE EMP_ID = 1

UNION ALL

SELECT  m.EMP_ID, m.FIRST_NAME, m.MANAGER_EMP_ID
FROM  MYTABLE m 
  JOIN a
    ON  a.MANAGER_EMP_ID = m.EMP_ID
)
CYCLE MANAGER_EMP_ID 
  SET IS_CYCLE TO 'Y' DEFAULT 'N'
SELECT EMP_ID, FIRST_NAME, IS_CYCLE
FROM a ;

Test at SQLfiddle


Just realized that the extra row can be retrieved with a simple change in the original query:

SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID, CONNECT_BY_ISCYCLE
FROM  MYTABLE 
   CONNECT BY NOCYCLE
     PRIOR MANAGER_EMP_ID = EMP_ID 
     AND PRIOR MANAGER_EMP_ID <> PRIOR EMP_ID 
                                 -- if she's a manager
                                 -- don't try to connect her with superiors
   START WITH EMP_ID = '12345' ;
1
  • Thanks! it works very well. We were at the brink of giving up :)
    – Vicyan
    Mar 17, 2015 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.