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

Using 'Case Expression Column' in WHERE Clause

Oracle 10.2 SQL poses a challenge when attempting to use a 'case expression column' within the WHERE clause. The error "ORA-00904: "%s: invalid identifier" is encountered due to the logical order of SQL statement processing.

Logical Order of SQL Processing

SQL statements are processed in the following sequence:

1. FROM: Table selection and row combinations
2. WHERE: Row filtering
3. GROUP BY: Row grouping
4. HAVING: Row filtering
5. SELECT: Column retrieval
6. DISTINCT: Duplicate row removal
7. UNION/EXCEPT/INTERSECT: Subquery result manipulation
8. ORDER BY: Row sorting

Error Cause

In the WHERE clause of the original query:

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

The 'department' column, generated by the CASE expression, does not exist yet during the WHERE clause evaluation. Hence, the WHERE clause cannot use it for comparison.

Solution 1: Subquery Enclosure

To overcome this limitation, enclose the query within a subquery:

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 CASE Expression in WHERE Clause

Alternatively, duplicate the CASE expression in the WHERE clause:

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 Option

In cases where the WHERE clause condition directly matches a specific department number, a simplified option is possible:

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 an Oracle 10.2 WHERE Clause?. For more information, please follow other related articles on the PHP Chinese website!