CODEWITHSUNDEEP
oracle-database
oceansmoon
oceansmoon

Reputation: 77

Oracle CONNECT BY and MAX

I am having difficulty integrating a MAX into a query and would greatly appreciate any help.

Basically what I'm trying to achieve is this: list a supervisor's supervisor's supervised employees along with their latest "day out" punch time.

The part I can't get right is the MAX(day out) part.

Here's the part I got so far that works fine:

SELECT EMPLOYEE.NUMBER, 
       EMPLOYEE.NAME, 
       S.NAME AS SUPERVISOR,
       EMPLOYEE.HIRE_DATE
FROM EMPLOYEE
LEFT JOIN EMPLOYEE_NUMBER ON EMPLOYEE_NUMBER.NUMBER = EMPLOYEE.SUPERVISOR_NUMBER
LEFT JOIN EMPLOYEE S ON S.NUMBER = EMPLOYEE.SUPERVISOR_NUMBER
WHERE LEVEL =2 
START WITH EMPLOYEE_NUMBER.USERID = (ID OF SUPERVISOR HERE)
CONNECT BY PRIOR EMPLOYEE.NUMBER = EMPLOYEE.SUPERVISOR_NUMBER

This part works and gives me the basic input that I need. However, I also need to list, for each employee, his/her lastest "day out" date.

Here's what I tried tried that didn't work:

SELECT EMPLOYEE.NUMBER, 
       EMPLOYEE.NAME, 
       S.NAME AS SUPERVISOR,
       EMPLOYEE.HIRE_DATE,
       MAX(EMPLOYEE_TIME.DATE_OUT)
FROM EMPLOYEE
LEFT JOIN EMPLOYEE_NUMBER ON EMPLOYEE_NUMBER.NUMBER = EMPLOYEE.SUPERVISOR_NUMBER
LEFT JOIN EMPLOYEE S ON S.NUMBER = EMPLOYEE.SUPERVISOR_NUMBER
LEFT JOIN EMPLOYEE_TIME ON EMPLOYEE_TIME.EMPLOYEE_NUMBER = EMPLOYEE.EMPLOYEE_NUMBER
WHERE LEVEL =2 
START WITH EMPLOYEE_NUMBER.USERID = (ID OF SUPERVISOR HERE)
CONNECT BY PRIOR EMPLOYEE.NUMBER = EMPLOYEE.SUPERVISOR_NUMBER
GROUP BY
       EMPLOYEE.NUMBER, 
       EMPLOYEE.NAME, 
       S.NAME,
       EMPLOYEE.HIRE_DATE
HAVING MAX(EMPLOYEE_TIME.DATE_OUT) >= SYSDATE-60

It doesn't throw any error, it just keeps on processing forever. I'm guessing something must be looping but I can't figure it out.

Thanks for any help.

Upvotes: 0

Views: 118

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

A slight change to your query in joining the time table, it need not be joined while CONNECT BY, instead separate it.

SELECT NUMBER, 
       EMPLOYEE_NAME, 
       SUPERVISOR,
       HIRE_DATE,
       MAX(TIME.DATE_OUT)
FROM
(SELECT NUMBER, 
       EMPLOYEE.NAME AS EMPLOYEE_NAME,
       S.NAME AS SUPERVISOR,
       EMPLOYEE.HIRE_DATE
FROM EMPLOYEE
LEFT JOIN EMPLOYEE_NUMBER ON EMPLOYEE_NUMBER.NUMBER = EMPLOYEE.SUPERVISOR_NUMBER
LEFT JOIN EMPLOYEE S ON S.NUMBER = EMPLOYEE.SUPERVISOR_NUMBER
WHERE LEVEL =2 
START WITH EMPLOYEE_NUMBER.USERID = (ID OF SUPERVISOR HERE)
CONNECT BY PRIOR EMPLOYEE.NUMBER = EMPLOYEE.SUPERVISOR_NUMBER) EMP_SUP
INNER JOIN EMPLOYEE_TIME TIME
ON( EMP_SUP.NUMBER = TIME.NUMBER)
GROUP BY
       NUMBER, 
       EMPLOYEE_NAME, 
       SUPERVISOR,
       HIRE_DATE
HAVING MAX(DATE_OUT) >= SYSDATE-60

Upvotes: 1

Related Questions