Using 'case expression column' in where clause

Viewed 65610
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 ?

6 Answers
Related