Illegal Expression In WHEN of CASE Expression : How to solve this error

Question

i have error in teradata that say :

Illegal Expression In WHEN of CASE Expression

Here is my data :

And that's my query :

 SELECT 
    CASE WHEN Country='UK' AND Customer IN (SELECT DISTINCT Customer FROM table WHERE 
    Customer<>'A') THEN 'EUR' ELSE 'OTHER' END AS Continent_E
 FROM table

Suppose that in my real data there are 20 distinct customers that are different to A, how can i write the query to not have an error

Can you help me Kind regards

Answer 1

Teradata does not allow IN (subquery) within a CASE expression. If you need this type of logic, you could use outer join pattern instead:

SELECT T.*,
    CASE WHEN Country='UK' 
      AND X.Customer IS NOT NULL /* Outer Join found a match */
    THEN 'EUR' 
    ELSE 'OTHER' END AS Continent_E
 FROM myTable T
 LEFT JOIN (SELECT DISTINCT Customer FROM CustTable WHERE Customer<>'A') X
 ON T.Customer = X.Customer;