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