case statement question

Database
Enthusiast

case statement question

can I use a sub select in a case statement? I tried the query below but received an error. Not sure if this is possible or not.

example -

SELECT a.cust_num,
CASE WHEN a.cust_num IN (SELECT cust_num FROM customer WHERE type = 106) THEN 'EAST COAST'

ELSE 'WEST COAST'
END
FROM account a

Thanks,

Joe
3 REPLIES
Enthusiast

Re: case statement question

i believe Case statements expects hardcoded values as they work on If,Else boolean logic and therefore a sub query gives illegal expression error.

What u say?

Thanks
Junior Contributor

Re: case statement question

Hi Joe,

you can't use any Scalar Subquery in Teradata, it's still not supported :-(

But you can rewrite it using an Outer Join:

SELECT
a.cust_num,
CASE WHEN b.cust_num is not null THEN 'EAST COAST'
ELSE 'WEST COAST'
END
FROM account a left join customer
on a.cust_num = c.cust_num and type = 106

Dieter
Enthusiast

Re: case statement question

Thanks Dieter!!