x

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 )
more ▼

asked Jun 30 '11 at 03:16 PM in Default

siera_gld gravatar image

siera_gld
1k 74 80 83

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered Jun 30 '11 at 03:35 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

@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.
Jun 30 '11 at 03:50 PM Oleg
@Oleg You're right, I wasn't paying attention to that both logical paths included 'CHANGE'.
Jun 30 '11 at 11:13 PM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Jun 30 '11 at 03:41 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x16
x11
x3

asked: Jun 30 '11 at 03:16 PM

Seen: 1310 times

Last Updated: Jun 30 '11 at 03:16 PM