I am trying to use a case statement in a where clause with an IN -
it's erroring arount the in - I am not sure quite what the correct syntax is but if the my supplier is = acme - they want to see more actions then the rest of the suppliers...
asked Jun 30 '11 at 03:16 PM in Default
That's not how CASE is used, it's not a logical construct that you can use for filtering, it returns a scalar value.
You could do:
Two things I'd like to mention with your query:
If SPLR_ACCT_ID is an int, you'd probably get a better result if you do:
At least that was the case in SQL Server 2000 and I think I've tested it with SQL Server 2005 as well. What it does is: If @SPLR_ID is null, you check if SPLR_ACCT_ID is between the minimum and the maximum values of an integer, otherwise it checks if SPLR_ACCT_ID is between @SPLR_ID and @SPLR_ID. The optimizer at least used to handle this better than col=@val or @val is null
Second thing: If you can, avoid using LIKE and '%something%' because that comparison can not use an index seex, it will have to scan to find its values.
answered Jun 30 '11 at 03:35 PM
You can restate it like this (the logic is still the same):
This way all records with CHNG_TYP = 'CHANGE' will be shown regardless of the supplier name and additionally, the ACME supplier's record with additional change type of REMOVAL will be shown as well.
answered Jun 30 '11 at 03:41 PM