sql - Case Statement as vals Outer Apply -

i trying add computed column table. have following case statement building calculated column in select statement,

--but want use column determine subsequent columns (sooo.. adding computed column workaround avoid complex cte i'm not sure can figure out - let me know if cte or functions better way go)

--- want case statement computed column in table itself

select top 1000 l.[msgdate] ,c.[alarmtype] ,l.[type] ,l.[usrstr1] ,l.[usrstr4] ,l.[usrstr5] ,l.[usrstr12] ,l.[usrstr15] ,case     when exists      (     select *       breinigsville.dbo.scada_sr s      s.scada_sr_tag = l.usrstr15 ,              ((l.[usrstr4]='analog' , c.[alarmtype] '%hh%') or (l.[usrstr4]='status'))     )         1         else 0     end [safety]    [breinigsville].[dbo].[lmfw] l     full outer join [breinigsville].[dbo].[_acknowledgedalarms] c   on   l.seqno=c.seqno2      ( l.[type]='ack' ,  l.usrstr12 '%ccxos%' ,  l.usrstr12 not '%ccxos5' ,  l.usrstr12 not '%ccxos6' ,  l.usrstr12 not '%ccxos9' ,  l.usrstr12 not '%ccxos12' ,  l.msgdate>getdate()-1   )  order l.seqno desc 

use outer apply:

from [breinigsville].[dbo].[lmfw] l full outer join      [breinigsville].[dbo].[_acknowledgedalarms] c      on l.seqno = c.seqno2 outer apply      (select (case when count(*) > 0 1 else 0 end) safety         breinigsville.dbo.scada_sr s        s.scada_sr_tag = l.usrstr15 ,               ((l.[usrstr4] = 'analog' , c.[alarmtype] '%hh%') or               (l.[usrstr4] = 'status')              )      ) vals 

then can use vals.safety anywhere in query.

note: version uses count(*). if performance @ concern, can want using additional subquery. answered way, because structure of query seems easier follow.

incidentally, cannot put subquery in computed column. can it, requires creating user-defined function, , using computed column. outer apply simpler, , can put logic table-valued function or view.