Using an IIF statement in a where clause
Don't use conditional logic. Just use boolean expressions:
Where (ss.DATAAREAID = 'USMF', and ss.ITEMGROUPID like 'S%' and ss.ITEMGROUPID not like 'SMS%') or
(ss.DATAAREAID <> 'USMF' and ss.ITEMGROUPID like 'SW%' and ss.ITEMGROUPID like 'SS%')
Note: iif()
is a SQL Server function, but it was introduced for backwards compatibility to MS Access. You should use ANSI-standard case
expressions instead.
Your version doesn't work because SQL Server does not treat the result of a boolean expression as a valid value. You would need to do something like this:
where (case when . . . and . . . then 1
else 0
end) = 1
The above does not take NULL
values into account. That condition is easily added if needed.
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Admin
Updated on June 04, 2022Comments
-
Admin about 2 years
I am trying to add multiple criteria to a Where clause in SQL Server 2014 using the following code and I am getting a syntax error.
I have tried a case statement but cannot get that to work based on the examples on this site.
Where iif(ss.DATAAREAID = 'USMF', (ss.ITEMGROUPID like 'S%' and ss.ITEMGROUPID not like 'SMS%'), (ss.ITEMGROUPID like 'SW%' and ss.ITEMGROUPID like 'SS%')
I am sure it is a quick solution, but any help would be appreciated.