Q:

Case Expression on a created column using Microsoft SQL

Within a view I put put together a select statement within a case and delcared it as a column. The column name is 'IR2'
How can I case off of the column 'IR2'?
I end up getting an error which says 'Invalid Column Name 'IR2'.
What are my work around options? 
case when r.ana = 'nh3' and r.serv='energy' and exists( select 1 from results x where x.no=r.no and x.ana='nh3' and x.sa='rejected' and x.serv <> 'energy')
then '*' else r.sa end as IR2,

CASE IR2 WHEN 'Released' then
 '' 
 ELSE
 '*' 
 END AS IR

A:

CTE would be the best choice. If you want to continue with current statement, you need to put a copy of the case statement in other case statement. Very messy code.
SELECT
case when r.ana = 'nh3' and r.serv='energy' and 
exists( select 1 from results x where x.no=r.no and x.ana='nh3' and x.sa='rejected' and x.serv <> 'energy')
then '*' else r.sa end as IR2,

CASE 
    (case when r.ana = 'nh3' and r.serv='energy' 
        and exists( select 1 from results x where x.no=r.no and x.ana='nh3' and x.sa='rejected' and x.serv <> 'energy') 
        then '*' else r.sa end)
WHEN 'Released' then
    '' 
ELSE
    '*' 
END AS IR

