Better way to write a multiple case statement SQL

I have this query bellow. And I’m trying to find a better way to write this query that has a statement. I don’t want to store each case value in a temp table, but maybe in an array.. Any input?

   SELECT          
       [coll_desc],
       [coll_descF],
       [coll_descS],
       [collcode],
       [agencystat],
       [nosend],
       [expiredays],
       [tier],
       [Available],
 case when([collcode] = 'PIE')  then CAST(0 AS BIT)
      when([collcode] = 'PIF')  then CAST(0 AS BIT)
      when([collcode] = 'PND')  then CAST(0 AS BIT)
      when([collcode] = 'PPA')  then CAST(0 AS BIT)
      when([collcode] = 'PPD')  then CAST(0 AS BIT)
      when([collcode] = 'RCL')  then CAST(0 AS BIT)
      when([collcode] = 'RES')  then CAST(0 AS BIT)
      when([collcode] = 'SIF')  then CAST(0 AS BIT)
      when([collcode] = 'SSC')  then CAST(0 AS BIT)
      when([collcode] = 'SSP')  then CAST(0 AS BIT)
      when([collcode] = 'HLD')  then CAST(0 AS BIT)
      else CAST(1 AS BIT)
  end as [fin_hold]
   FROM [dbo].[collcode_Agency_Codes]  WITH (NOLOCK)
     WHERE type  = 'APR'

Answer

Does IN satisfy what you want to do?

(case when(collcode IN ('PIE', 'PIF', 'PND', . . .) then CAST(0 AS BIT)
      else CAST(1 AS BIT)
 end)

It certainly shortens the code.

Advertisement

Answer