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

You can do a couple of things. CTE, APPLY, SubQuery etc. Tried some of those? I think you should read up on CTE's if you think they are complex. They are not that hard to use. Just skip anything about recursion if you find it to difficult. — CodeMonkey, Jul 10 '15 at 14:04
is it possible to make the CASE statement a function that I can use in subsequent Select statements? — Orin Moyer, Jul 10 '15 at 14:11

Gordon Linoff · Accepted Answer · 2015-07-11T11:14:48.347

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.

you guys are too smart. This is exactly what I needed, thanks! — Orin Moyer, Jul 10 '15 at 15:00

Case Statement as vals Outer Apply

1 Answers1