How to Resolve ORA-00904 Error When Using CASE Expressions in WHERE Clauses?

Overcoming ORA-00904: Using 'case expression column' in WHERE Clause

When attempting to use a case expression as a column in the WHERE clause, ORA-00904 can arise due to SQL's logical order of processing. The WHERE clause is evaluated before the SELECT clause, making it impossible to utilize columns defined within the SELECT's case expression.

Solution 1: Nested Query

To resolve this issue, enclose the original query within a nested query:

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

Solution 2: Duplicate Calculation

Alternatively, duplicate the case expression in the WHERE condition itself:

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

Simplified Query

For the specific case presented, a simplified query can also be utilized:

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

The above is the detailed content of How to Resolve ORA-00904 Error When Using CASE Expressions in WHERE Clauses?. For more information, please follow other related articles on the PHP Chinese website!