CASE result in WHERE clause?

[james0816]

Not sure how to word this but is it possible to take the result of a CASE statement:

select a.field5,
(case when a.field1 ='HI' then substr(a.field2,6,8) else substr(a.field3,6,8) end) as newfield

and use that result in the where clause:

from table1 a, table2 b

where a.field4 = 'TODAY' and newfield = b.field1

 

[r937]

yes, but not directly

select a2.field5
     , a2.newfield
  from (
       select field5
            , case when field1 ='HI' 
                   then substr(field2,6,8) 
                   else substr(field3,6,8) 
                end as newfield
         from table1
        where field4 = 'TODAY' 
       ) as a2
inner
  join table2 b
    on b.field1 = a2.newfield

r937.com | rudy.ca