question

siera_gld avatar image
siera_gld asked

Case Statement in Where Clause

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... WHERE [REPTD_DTS] IS NULL AND ([SPLR_ACCT_ID] = @SPLR_ID OR @SPLR_ID IS NULL) and (case when ([SPLR_NAME] like '%Acme%') then (CHNG_TYP in ('REMOVAL', 'CHANGE')) else CHNG_TYP = 'CHANGE' end )
whereincase
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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: WHERE [REPTD_DTS] IS NULL AND ([SPLR_ACCT_ID] = @SPLR_ID OR @SPLR_ID IS NULL) and (([SPLR_NAME] like '%Acme%' and CHNG_TYP in ('REMOVAL', 'CHANGE')) OR ([SPLR_NAME] NOT like '%Acme%' AND CHNG_TYP = 'CHANGE')) 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: AND (SPLR_ACCT_ID BETWEEN COALESCE(@SPLR_ID,-2147483648) AND COALESCE(@SPLR_ID,2147483648)) 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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@Magnus Ahlkvist I was also thinking in terms of (ACME and IN (A, B)) or (NOT ACME and = A) but then realised that the way I have it in my answer is probably cheaper, but then again, this predicate does not appear to be slim anyways, so perhaps it does not really matter.
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
@Oleg You're right, I wasn't paying attention to that both logical paths included 'CHANGE'.
0 Likes 0 ·
Oleg avatar image
Oleg answered
You can restate it like this (the logic is still the same): WHERE [REPTD_DTS] IS NULL AND ([SPLR_ACCT_ID] = @SPLR_ID OR @SPLR_ID IS NULL) and ( CHNG_TYP = 'CHANGE' or ([SPLR_NAME] like '%Acme%' and CHNG_TYP = 'REMOVAL') ); 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. Oleg
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.