Using 'case expression column' in where cl

问题:

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"

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

回答1:

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

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

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

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

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

• SELECT: list of columns is evaluated.

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

• 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: rows are ordered.

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.

---

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:

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.

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"

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

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