How to Use a CASE Expression Column in an Oracle 10.2 WHERE Clause?

Using CASE Expression Column in WHERE Clause in Oracle 10.2

When using a CASE expression column in a WHERE clause in Oracle 10.2, you may encounter the error "ORA-00904: invalid identifier" if the CASE expression column is not yet defined in the query. This is because in Oracle 10.2, the WHERE clause is evaluated before the SELECT clause.

Solution 1: Enclose the Query in Another Query

To overcome this limitation, you can enclose the query in another query that defines the CASE expression column:

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 the Calculation in the WHERE Condition

Alternatively, you can duplicate the CASE expression 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';

Simplified Version

You can also simplify the query by simply selecting the sales department from employees with a department number equal to 20:

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

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