|
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() ) |
|
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 ... Thanks!! That got it working.
(08 May '13, 11:03)
TonyV
|