How Can I Use a CASE Expression Column in the WHERE Clause of an Oracle 10.2 Query?

Using 'Case Expression Column' in a WHERE Clause in Oracle 10.2

In Oracle 10.2, an ORA-00904 error occurs when attempting to use a case expression column in a WHERE clause. This arises due to the logical processing order of SQL statements, where WHERE conditions are evaluated before columns are selected.

Error Explanation:

SQL statements are logically processed in the following order:

• FROM: Table selection and row combinations
• WHERE: Row filtering based on conditions
• GROUP BY: Row grouping
• HAVING: Additional row filtering
• SELECT: Column selection
• DISTINCT: Duplicate row removal
• UNION/EXCEPT/INTERSECT: Subquery operations
• ORDER BY: Row ordering

In the provided query, the WHERE clause attempts to use the department column calculated in the CASE expression. However, this column is not populated until after the WHERE clause execution, leading to the error.

Solution 1: Subquery Enclosure

To overcome this limitation, enclose the original query in a subquery and select the desired columns from it:

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: Conditional Replication

Replicate the CASE expression 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' ;

Alternative Solution:

If feasible, consider using a simpler query:

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

The above is the detailed content of How Can I Use a CASE Expression Column in the WHERE Clause of an Oracle 10.2 Query?. For more information, please follow other related articles on the PHP Chinese website!