Case Statement as vals Outer Apply

Question

I am trying to add a computed column to a table. Currently I have the following CASE statement building a calculated column in a SELECT statement,

--but I want to use this column to determine subsequent columns (sooo.. adding a computed column is somewhat a workaround to avoid complex CTE that i'm not sure i can figure out - let me know if CTE or functions is a better way to go)

--- I want the CASE statement to be a computed column in the 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 *
    FROM   Breinigsville.dbo.SCADA_SR S
    WHERE  S.SCADA_SR_TAG = L.UsrStr15 and 
            ((L.[UsrStr4]='analog' and C.[AlarmType] like '%HH%') or (L.[UsrStr4]='status'))
    )
        THEN 1
        ELSE 0
    END AS [Safety]



FROM [Breinigsville].[dbo].[LMFW] L


  full outer join [Breinigsville].[dbo].[_AcknowledgedAlarms] C
  on
  L.SeqNo=C.SeqNo2



  WHERE (
L.[Type]='Ack' AND 
L.UsrStr12 LIKE '%CCXOS%' AND 
L.UsrStr12 NOT LIKE '%CCXOS5' AND 
L.UsrStr12 NOT LIKE '%CCXOS6' AND 
L.UsrStr12 NOT LIKE '%CCXOS9' AND 
L.UsrStr12 NOT LIKE '%CCXOS12' AND 
L.MsgDate>getdate()-1


)

order by L.SeqNo desc

Answer 1

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 THEN 1 ELSE 0 END) as Safety
      FROM   Breinigsville.dbo.SCADA_SR S
      WHERE  S.SCADA_SR_TAG = L.UsrStr15 and 
             ((L.[UsrStr4] = 'analog' and C.[AlarmType] like '%HH%') or
              (L.[UsrStr4] = 'status')
             )
     ) vals

Then you can use vals.Safety anywhere in the query.

Note: this version uses count(*). If performance is at all a concern, then you can get what you want using an additional subquery. I answered this way, because the structure of the query seems easier to follow.

Incidentally, you cannot easily put a subquery in as a computed column. You can do it, but it requires creating a user-defined function, and using that for the computed column. OUTER APPLY is definitely simpler, and you can even put this logic into a table-valued function or view.