在where子句中使用"case expression column" [英] Using 'case expression column' in where clause

问题描述

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 expression column"?

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 :选择一个或多个表以及所有符合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

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

^{* logically processed:} Note that database engines may as well choose another order of evaluation for a query (and that's what they usually do!) The only restriction is that the results should be the same as if the above order was used.

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

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 ;

这篇关于在where子句中使用"case expression column"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！