sql - Using 'case expression column' in where clause -

select ename   ,    job   ,    case deptno          when 10            'accounts'          when 20            'sales'          else 'unknown'        end department emp /* !!! */  department = 'sales' 

this fails:

ora-00904: "%s: invalid identifier"

is there way overcome limitation in oracle 10.2 sql ? how use 'case expression column' in clause ?

the reason error sql select statements logically * processed in following order:

• from: selection of 1 table or many joined ones , rows combinations match on conditions.

• where: conditions evaluated , rows not match removed.

• group by: rows grouped (and every group collapses 1 row)

• having: conditions evaluated , rows not match removed.

• select: list of columns evaluated.

• distinct: duplicate rows removed (if it's select distinct statement)

• union, except, intersect: action of operand taken upon rows of sub-select statements. example, if it's union, rows gathered (and duplicates eliminated unless it's union all) after sub-select statements evaluated. accordingly except or intersect cases.

• order by: rows ordered.

therefore, can't use in where clause, hasn't been populated or calculated yet. see question: oracle-sql-clause-evaluation-order

^{* logically processed:} note database engines may choose order of evaluation query (and that's do!) restriction results should same if above order used.

---

solution enclose query in one:

select *   ( select ename          , job          , case deptno              when 10 'accounts'              when 20 'sales'                      else 'unknown'            end department     emp   ) tmp department = 'sales' ; 

or duplicate calculation in condition:

select ename      , job      , case deptno          when 10 'accounts'          when 20 'sales'                  else 'unknown'        end department emp     case deptno       when 10 'accounts'       when 20 'sales'               else 'unknown'     end = 'sales' ; 

---

i guess simplified version of query or use:

select ename      , job      , 'sales' department emp deptno = 20 ;