How to write this CASE expression?

[Former Member]

I'm implementing a new feature in our application, which requires me to add functionality to a query. Without getting into the whys and the wherefores, the case statement in the following query gives me syntax errors. What's the right way to write the statement?

SELECT  list.ListId, list.DomainId AS listDomainId, 
        entry.ListId, entry.ListDetailId, entry.CountryId, entry.LocaleCode, entry.Plate, 
        entry.HashedPlate, entry.PlateClassId, entry.ListPriorityId, entry.BeginDate, entry.EndDate, 
        entry.VehicleTypeId, entry.MakeId, entry.ModelId, entry.Year, entry.ColorId, 
        entry.Notes, COALESCE( entry.AlarmClassId, 0 ), entry.OfficerNotes 
FROM ListDetails as entry 
JOIN Lists AS list WITH( INDEX( Lists ) ) ON entry.ListId = list.ListId  
     AND list.ListTypeId <> 3
     AND ( list.domain_filter IS NULL OR list.domain_filter = 7 ) 
WHERE entry.FromVersion IS NULL 
AND (
    CASE list.ListTypeId 
    WHEN 3 THEN NOT ( entry.Plate IN ( 'ABC5632' ) AND entry.LocaleCode = 'NY' )
    ELSE            ( entry.Plate IN ( 'ABC5632' ) AND entry.LocaleCode = 'NY' )
    END )
AND ( entry.BeginDate IS NULL OR entry.BeginDate <= NOW() ) 
AND ( entry.  EndDate IS NULL OR entry.  EndDate >  NOW() )

[MarkCulp · Accepted solution]

I'm sure there are lots of ways of doing this, but here is one:

...
AND (
    CASE list.ListTypeId 
    WHEN 3 THEN
        if NOT( entry.Plate IN ( 'ABC5632' ) AND entry.LocaleCode = 'NY' ) ) then 1 else 0 endif
    ELSE
        if    ( entry.Plate IN ( 'ABC5632' ) AND entry.LocaleCode = 'NY' )   then 1 else 0 endif
    END ) = 1
...

[Former Member · Accepted solution]

Thanks!! That got it working.