Oracle SQL Subquery Syntax Error caused by ORDER BY

Question

I have a syntax error on an Oracle SQL query. The query should get me the department of an employee. If the employee has no department (null), I want the department of the first manager up the hierarchy, which has a department.

SELECT department_id FROM department
WHERE department_id =
(
    SELECT department_id FROM employee
    WHERE department_id IS NOT NULL AND rownum = 1
    start WITH employee_id = 19
    connect by employee_id = prior manager_id
    ORDER BY level
);

The error message is this:

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Error at Line: 8 Column: 2

I have written an alternative query, which does the job. But I'm not very happy with it.

SELECT department_id FROM department
WHERE department_id = 
(
    SELECT department_id FROM employee
    WHERE level =
    (
        SELECT MIN(level) FROM employee
        WHERE department_id IS NOT NULL
        start WITH employee_id = 19
        connect by employee_id = prior manager_id
    )
    start WITH employee_id = 19
    connect by employee_id = prior manager_id
);

Do you have any idea how to fix the first query? Or to simplify the second one? Thank you in advance.

Answer 1

In your first query as correctly said by @Barbaros that ORDER BY is not needed and even rownum = 1 will not do what you are thinking.

Does the following query fulfill your requirements:

SELECT
    E.EMPLOYEE_ID,
    CASE WHEN E.DEPARTMENT_ID IS NOT NULL 
         THEN E.DEPARTMENT_ID
         ELSE LAG(D.DEPARTMENT_ID IGNORE NULLS) OVER(ORDER BY LEVEL)
    END AS DEPARTMENT_ID
FROM
    EMPLOYEE E
    LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
START WITH E.EMPLOYEE_ID = 19
CONNECT BY E.EMPLOYEE_ID = PRIOR E.MANAGER_ID

Cheers!!