在where子句中使用'case expression column'

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

This fails:

这失败了：

ORA-00904: "%s: invalid identifier"

ORA-00904：“％s：无效标识符”

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

有没有办法克服Oracle 10.2 SQL中的这种限制？如何在where子句中使用'case expression column'？

6 个解决方案

#1

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

出现此错误的原因是SQL SELECT语句按以下顺序进行逻辑*处理：

FROM: selection of one table or many JOINed ones and all rows combinations that match the ON conditions.

FROM：选择一个表或多个JOINed以及与ON条件匹配的所有行组合。
WHERE: conditions are evaluated and rows that do not match are removed.

WHERE：评估条件并删除不匹配的行。
GROUP BY: rows are grouped (and every group collapses to one row)

GROUP BY：对行进行分组（每个组都折叠为一行）
HAVING: conditions are evaluated and rows that do not match are removed.

HAVING：评估条件并删除不匹配的行。
SELECT: list of columns is evaluated.

SELECT：评估列的列表。
DISTINCT: duplicate rows are removed (if it's a SELECT DISTINCT statement)

DISTINCT：删除重复的行（如果它是SELECT DISTINCT语句）
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.

UNION，EXCEPT，INTERSECT：该操作数的操作取决于子SELECT语句的行。例如，如果它是UNION，则在评估所有子SELECT语句之后，将收集所有行（并删除重复项，除非它是UNION ALL）。因此对于EXCEPT或INTERSECT案件。
ORDER BY: rows are ordered.

ORDER BY：行是有序的。

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

因此，您不能在WHERE子句中使用尚未填充或计算的内容。另见这个问题：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.

*逻辑处理：请注意，数据库引擎也可以为查询选择另一个评估顺序（这就是他们通常所做的！）唯一的限制是结果应该与使用上面的顺序相同。

Solution is to enclose the query in another one:

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

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

or to duplicate the calculation in the WHERE condition:

或者在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 ;

#2

Your table does not contain a column "department" and thus you can not reference it in your where clause. Use deptno instead.

您的表不包含“department”列，因此您无法在where子句中引用它。请改用deptno。

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

#3

This work for me :

这项工作对我来说：

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

#4

select emp_.*
from (SELECT ename
  ,    job
  ,    CASE deptno
         WHEN 10
           THEN 'ACCOUNTS'
         WHEN 20
           THEN 'SALES'
         ELSE 'UNKNOWN'
       END AS department
FROM emp /* !!! */ ) emp_ where emp_.department='UNKNOWN';

#5

try:

尝试：

  SQL> SELECT ename
      2  ,      job
      3  ,      CASE
      4            WHEN  deptno = 10
      5            THEN 'ACCOUNTS'
      6            WHEN  deptno = 20
      7            THEN 'SALES'
     12            ELSE 'UNKNOWN'
     13         END AS department
     14  FROM   emp /* !!! */ where department = 'SALES';

#6

Oracle tries to filter the number of records to be scanned from table by going for the where clause first before select that is why your query fails. Moreover, your query would have never returned rows with department - "Accounts or Unknown" because of the filter Department="SALES"

Oracle尝试通过先选择where子句来筛选要从表中扫描的记录数，然后再选择这是查询失败的原因。此外，由于过滤器部门=“SALES”，您的查询将永远不会返回带有部门的行 - “帐户或未知”

Try below instead, that will be easy to be fetched by Engine :

请尝试下面的内容，这很容易被Engine取出：

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

SELECT ename，job，'SALES'AS部门FROM emp WHERE deptno = 20;

#1