0

I have an query like this , but it does not work , what's wrong with IN keyword with CASE EXPRESSION ?

Select State = case c.addressId
  when in('2552','2478','2526') then 'IN' 
  when in ('9999') then 'OUT'
  else 'UNKNOWN'
  end,
  name, time
from x;

I've use SQL Server 2008 and this is the error msg :

Incorrect syntax near the keyword 'in'.

Abdullah
  • 5,445
  • 10
  • 41
  • 48
  • 1
    Oh, how great it could have been to have a comma separated list of `WHEN` expressions in simple CASE! And it doesn't even seem too hard a change to the syntax. – Andriy M Feb 06 '11 at 19:13

2 Answers2

4

You've got the syntax wrong. It should be CASE WHEN [COLUMN] in (...):

Select 
  case when c.addressId in('2552','2478','2526') then 'IN' 
  when c.addressId in ('9999') then 'OUT'
  else 'UNKNOWN'
  end as State,
  name, time
from contact c;
dogbane
  • 266,786
  • 75
  • 396
  • 414
  • 3
    Being pedantic -- `CASE` supports the column reference between the CASE and the WHEN, just not for IN and other forms of comparison/evaluation logic. – OMG Ponies Feb 06 '11 at 16:28
-1

Consider using a join instead.

SELECT ISNULL(a.[State], 'UNKNOWN') [State]
    , c.[Name]
    , c.[Time]
FROM [contact] c
LEFT OUTER JOIN
(
    SELECT 2552 AddressId, 'IN' [State]
    UNION ALL SELECT 2478, 'IN'
    UNION ALL SELECT 2526, 'IN'
    UNION ALL SELECT 9999, 'OUT'
) [address] a ON a.AddressId = c.AddressId;
Anthony Faull
  • 17,549
  • 5
  • 55
  • 73
  • I can't think of a single reason why that would be better then the case/when construct. Unless you meant to pull the values from a table? – Ronnis Feb 06 '11 at 18:51
  • A table is a great idea. Then you can add or remove magic numbers without changing your SQL code. You can also add a descriptive field which explains each magic number. – Anthony Faull Feb 06 '11 at 20:20