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() )

asked 08 May '13, 10:48

TonyV's gravatar image

TonyV
1.2k333967
accept rate: 75%

edited 08 May '13, 13:55

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270


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
...
permanent link

answered 08 May '13, 10:59

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270
accept rate: 40%

Thanks!! That got it working.

(08 May '13, 11:03) TonyV
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×30
×5

question asked: 08 May '13, 10:48

question was seen: 707 times

last updated: 08 May '13, 13:55