在 where 子句中使用“case 表达式列"

2021-12-05 00:00:00 sql oracle oracle10g ora-00904 
SELECT ename
  ,    job
  ,    CASE deptno
         WHEN 10
           THEN 'ACCOUNTS'
         WHEN 20
           THEN 'SALES'
         ELSE 'UNKNOWN'
       END AS department
FROM emp /* !!! */ 
WHERE department = 'SALES'

这失败了:

ORA-00904:%s:无效标识符"

ORA-00904: "%s: invalid identifier"

有没有办法克服 Oracle 10.2 SQL 中的这个限制?如何在 where 子句中使用case 表达式列"?

Is there a way to overcome this limitation in Oracle 10.2 SQL ? How to use the 'case expression column' in where clause ?

推荐答案

出现这个错误的原因是 SQL SELECT 语句是逻辑上 * 按以下顺序处理:

The reason for this error is that SQL SELECT statements are logically * processed in the following order:

  • FROM:选择一张表或多张JOINed表以及符合ON条件的所有行组合.

WHERE:评估条件并删除不匹配的行.

WHERE: conditions are evaluated and rows that do not match are removed.

GROUP BY:行被分组(每组折叠为一行)

GROUP BY: rows are grouped (and every group collapses to one row)

HAVING:评估条件并删除不匹配的行.

HAVING: conditions are evaluated and rows that do not match are removed.

SELECT:评估列列表.

SELECT: list of columns is evaluated.

DISTINCT:删除重复的行(如果是 SELECT DISTINCT 语句)

DISTINCT: duplicate rows are removed (if it's a SELECT DISTINCT statement)

UNION、EXCEPT、INTERSECT:对子 SELECT 语句的行执行该操作数的操作.例如，如果它是 UNION，则在评估所有子 SELECT 语句后收集所有行(并消除重复，除非它是 UNION ALL).因此，对于 EXCEPT 或 INTERSECT 情况.

UNION, EXCEPT, INTERSECT: the action of that operand is taken upon the rows of sub-SELECT statements. For example, if it's a UNION, all rows are gathered (and duplicates eliminated unless it's a UNION ALL) after all sub-SELECT statements are evaluated. Accordingly for the EXCEPT or INTERSECT cases.

ORDER BY:行被排序.

ORDER BY: rows are ordered.

因此，您不能在 WHERE 子句中使用尚未填充或计算的内容.另请参阅此问题:oracle-sql-clause-evaluation-order

Therefore, you can't use in WHERE clause, something that hasn't been populated or calculated yet. See also this question: oracle-sql-clause-evaluation-order

* 逻辑处理: 请注意，数据库引擎也可以为查询选择其他评估顺序(这就是他们通常做的！)唯一的限制是结果应该与使用上述顺序相同.

解决方案是将查询包含在另一个查询中:

SELECT *
FROM
  ( SELECT ename
         , job
         , CASE deptno
             WHEN 10 THEN 'ACCOUNTS'
             WHEN 20 THEN 'SALES'
                     ELSE 'UNKNOWN'
           END AS department
    FROM emp
  ) tmp
WHERE department = 'SALES' ;

或复制 WHERE 条件中的计算:

SELECT ename
     , job
     , CASE deptno
         WHEN 10 THEN 'ACCOUNTS'
         WHEN 20 THEN 'SALES'
                 ELSE 'UNKNOWN'
       END AS department
FROM emp
WHERE
    CASE deptno
      WHEN 10 THEN 'ACCOUNTS'
      WHEN 20 THEN 'SALES'
              ELSE 'UNKNOWN'
    END = 'SALES' ;

<小时>

我想这是您查询的简化版本，或者您可以使用:

I guess this is a simplified version of your query or you could use:

SELECT ename
     , job
     , 'SALES' AS department
FROM emp
WHERE deptno = 20 ;

相关文章