Search code examples
sqlsql-servert-sqlcasewhere-clause

TSQL Syntax for CASE WHEN in WHERE Clause


I'm having trouble figuring out how to use CASE WHEN in the WHERE clause. A query similar to what I'm trying to write is as follows:

SELECT *
FROM Global_Sales
WHERE sales_org IN ('AM NEW ZEALAND', 'AM AUSTRALIA', 'AM MEXICO')
AND business_type IN (CASE WHEN sales_org IN ('AM MEXICO') THEN ('DIRECT SALES')
ELSE ('DIRECT SALES','INDIRECT SALES','INTERNATIONAL') END)

Is it possible to use CASE WHEN like this?

Understanding now I can not use CASE WHEN to return the specific result I'm after, here is a longer version I initially wrote using UNION. Since I have dozens of these, I was trying to cut down on the length and use of UNION if possible.

SELECT *
FROM AM_SALE_GLOBAL
WHERE sorg IN ('AM NEW ZEALAND')
AND bus_type_cd In ('DIRECT SALES','INDIRECT SALES','INTERNATIONAL','OEM') 

UNION

SELECT *
FROM AM_SALE_GLOBAL
WHERE sorg IN ('AM MEXICO')
AND bus_type_cd In ('DIRECT SALES')

Solution

  • No need for a UNION, simply OR the WHERE clauses:

    SELECT *
    FROM AM_SALE_GLOBAL
    WHERE (sorg IN ('AM NEW ZEALAND') AND
           bus_type_cd In ('DIRECT SALES','INDIRECT SALES','INTERNATIONAL','OEM'))
       OR (sorg IN ('AM MEXICO') AND
           bus_type_cd In ('DIRECT SALES'))
    

    (Perhaps you want SELECT DISTINCT - but probably not.)