Case Statement in Where Clause

Question

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 )

Answer 1

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.

Answer 2

0

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

answered Jun 30 '11 at 03:41 PM

Oleg
15.4k ● 1 ● 4 ● 24

(comments are locked)

Case Statement in Where Clause

Follow this question

Related Questions