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

Most people here want sample table data _and the expected result_ as properly formatted text, not as images. — jarlh, Apr 27 '23 at 07:49
Within a CASE you can only use Correlated Scalr Subqueries, see https://stackoverflow.com/a/32985863/2527905, but there might be better solutions... — dnoeth, Apr 27 '23 at 20:11

score 1 · Answer 1 · answered Apr 26 '23 at 22:26

A subquery inside case clause is not accepted, your query can simply be like this :

 SELECT *,
    CASE WHEN Country='UK' AND Customer <> 'A' THEN 'EUR' ELSE 'OTHER' END AS Continent_E
 FROM table

Result :

customer    country          Continent_E
A           India            OTHER
A           USA              OTHER
A           USA              OTHER
B           UK               EUR
C           UK               EUR
D          JAPAN             OTHER

Thanks for your response but I still have the error – Arthur_75 May 01 '23 at 17:51 — Arthur_75, May 01 '23 at 17:51

score 0 · Answer 2 · answered Apr 27 '23 at 00:39

0

Maybe you can try to rename your table name. Because in SQL, "table" is a reserved word. Or you can add [] to the table name([table]). In SQL Server, the query executes successfully although it still renders "Invalid valid object name 'table' " warning. But in MySQL Workbench, it fails.

answered Apr 27 '23 at 00:39

tech-kz

22
4

Firstly, op probably just used `table` instead of the actual table name. Second, in Teradata someone did want to use a reserved word as an object name, they would need to use double quotes. – Andrew Apr 27 '23 at 15:26

Fred · Accepted Answer · 2023-04-27T19:06:41.387

0

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;

edited Apr 27 '23 at 19:06

answered Apr 27 '23 at 15:18

Fred

1,916
1
8
16

Within CASE you could use a correlated scalar subquery (one that returns a single value per "outer" row) or a non-correlated scalar subquery (which returns only one row / one value) and do some other conditional test (e.g. equality) with that returned value. But that seems an additional level of complication. – Fred Apr 28 '23 at 19:11

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

3 Answers3