由 ORDER BY 引起的 Oracle SQL 子查询语法错误

塞缪尔·P。

我在 Oracle SQL 查询中出现语法错误。查询应该让我知道一个员工的部门。如果员工没有部门（空），我希望第一个经理的部门在层次结构上有一个部门。

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

错误信息是这样的：

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

我写了一个替代查询，它可以完成这项工作。但我对此并不十分满意。

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

您知道如何修复第一个查询吗？还是简化第二个？先感谢您。

大力水手

在您的第一个查询中，@Barbaros 正确地说这ORDER BY是不需要的，甚至rownum = 1不会做您在想的事情。

以下查询是否满足您的要求：

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

干杯！！

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。