TSQL Syntax for CASE WHEN in WHERE Clause-CodePudding

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')

CodePudding user response：

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.)

CodePudding user response：

Just use normal boolean logic

AND (
     (sales_org = 'AM MEXICO' AND business_type = 'DIRECT SALES')
  OR (sales_org <> 'AM MEXICO' AND business_type IN ('DIRECT SALES', 'INDIRECT SALES', 'INTERNATIONAL', 'OEM'))
)

A shorter version is

AND (business_type = 'DIRECT SALES'
   OR (sales_org <> 'AM MEXICO' AND business_type IN ('INDIRECT SALES', 'INTERNATIONAL', 'OEM'))
)

Another version

AND business_type IN ('DIRECT SALES' ,'INDIRECT SALES', 'INTERNATIONAL', 'OEM')
AND (NOT sales_org <> 'AM MEXICO' AND business_type IN ('INDIRECT SALES', 'INTERNATIONAL'))