Using two = signs in CASE statement?

Question

I'm attempting to use a case statement, dependent on a variable, to determine part of my WHERE clause, but it doesn't like what I'm doing. Here's my query (modified for simplicity)

DECLARE @SalesType VARCHAR(10)

SET @SalesType = 'Bulk'

SELECT CASE 
        WHEN fwsf.Customer_Cardlock_Customer = 'True'
            THEN 'CFN'
        ELSE 'Bulk'
        END AS 'Prod Type'
FROM TABLE t
WHERE CASE 
        WHEN @SalesType = 'Bulk' then t.customer_type = 'False'
        WHEN @SalesType = 'CFN' then t.customer_type = 'True'
        End

Put another way, I want to state in the WHERE clause that when the @SalesType is a given value, to select the rows that have another value.

EDIT:For sake of others, I realized I had another scenario where I may need to select an 'All' option. Per Shawn below, he corrected my original proposed solution with the following that I verified works:

AND t.customer_type =
CASE @SalesType
    WHEN 'Bulk' then 'False'
    WHEN 'CFN'  then 'True'
    WHEN 'All'  then t.customer_type
END
        END

That would explain it. Didn't realize it had that limitation. Any suggestions on how I might achieve my goal, if not by a CASE? — jw11432, Aug 13 '19 at 20:46
I've edited my answer. Note that I don't quite understand the connection between `Customer_Cardlock_Customer` and `@SalesType` but I think it's probably not relevant to answering the question. As per you nested `case`, I don't see that that will work right. Basically it was always evaluate to "False when given "All". — shawnt00, Aug 13 '19 at 23:28

S3S · Answer 1 · 2019-08-14T00:02:10.183

5

You can write your WHERE clause a bit more simpler:

where 
  (@SalesType = 'Bulk' and t.customer_type = 'False')
   OR
  (@SalesType = 'CFN' and t.customer_type = 'True')
   OR 
   @SalesType = 'All'

edited Aug 14 '19 at 00:02

answered Aug 13 '19 at 20:47

S3S

24,809
5
26
45

Dale K · Answer 2 · 2020-06-18T09:12:49.270

CASE expressions can only return a value not a condition. So just move the column to compare outside the case expression, and then use the case expression to generate the value to compare.

DECLARE @SalesType VARCHAR(10);

SET @SalesType = 'Bulk';

SELECT CASE 
    WHEN fwsf.Customer_Cardlock_Customer = 'True' THEN 'CFN'
    ELSE 'Bulk'
    END AS 'Prod Type'
FROM TABLE t
WHERE t.customer_type = CASE 
    WHEN @SalesType = 'Bulk' THEN 'False'
    WHEN @SalesType = 'CFN' THEN 'True'
    END;

shawnt00 · Accepted Answer · 2019-08-15T15:26:14.710

Your summary is very good and this is a common way to translate or decode values. Per your edit there's also a case where you want all results.

WHERE t.customer_type =
    CASE @SalesType
        WHEN 'Bulk' then 'False'
        WHEN 'CFN'  then 'True'
        WHEN 'All'  then t.customer_type
    END

I've modified the expression to use a different form of case. You might find this to be more readable and at least now you know it exists.

I'll also note that in your original logic, if the case falls through then the result will be null.

The "all rows" can be handled by just returning the customer_type value which is naturally always equal to itself, except when null. To handle null you'd need to do something a bit more complicated like coalesce(t.customer_type, 'ALL!') on both sides of the equality.

If optimization is a concern you might be better off with regular and/or logic.

(
    @SalesType = 'Bulk' AND t.customer_type = 'False' OR
    @SalesType = 'CFN'  AND t.customer_type = 'True'  OR
    @SalesType = 'All'
)

Great catch. As I started putting this together, I realized I need to account for an 'All', which is both of these values... — jw11432, Aug 13 '19 at 20:59

score 1 · Answer 4 · edited Jun 18 '20 at 09:14

1

If you are looking to use CASE in a WHERE clause then you can do something like:

WHERE 1 = CASE 
           WHEN @SalesType = 'Bulk' AND t.customer_type = 'False' THEN 1
           WHEN @SalesType = 'CFN' AND t.customer_type = 'True'THEN 1
           ELSE 0
          END

edited Jun 18 '20 at 09:14

Dale K

25,246
15
42
71

answered Aug 13 '19 at 20:51

Jaydip Jadhav

12,179
6
24
40

Using two = signs in CASE statement?

4 Answers4