Sub query in Case statment

Database
Enthusiast

Sub query in Case statment

Hi,

 

i have some problem with convertion.

i have this line of code:

 

CASE when exists(select * from DWH.DW_DIM_AccountStatuses where AccountStatus_ID = a.AccountStatus_ID and AccountStatus_Desc like '%Promo OD%') then 1 else 0 end AS Is_PromoOD

 

 

as i understand Tera data have problem with sub query in case statment.

does somone know another way to writh it?

 

 

 

thanks!

3 REPLIES
Senior Apprentice

Re: Sub query in Case statment

Hi,

 

Teradata supports sub-queries in a CASE expression but they must be scalar sub-queries, i.e. return a single value.

 

I'm guessing that your can be converted, but you might need to provide some more info. The following might work for you if 'a' is the alias for a table in your statement.

CASE 
  when a.AccountStatus_ID = (select max(accountstatus_id) from DWH.DW_DIM_AccountStatuses where AccountStatus_ID = a.AccountStatus_ID and AccountStatus_Desc like '%Promo OD%') then 1 
  else 0 
end AS Is_PromoOD

Either try this, or if not please provide a fuller explanation of your code and logic.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Sub query in Case statment

It's work. Thank you so much!
Junior Contributor

Re: Sub query in Case statment

You can simplify this a bit by removing the CASE: 

(select coalesce(max(1),0) from DWH.DW_DIM_AccountStatuses where AccountStatus_ID = a.AccountStatus_ID and AccountStatus_Desc like '%Promo OD%') AS Is_PromoOD

But a Scalar Subquery is equivalent to an Outer Join:

SELECT ...
   Coalesce(Is_PromoOD,0)
FROM a
LEFT JOIN
 ( 
   SELECT DISTINCT AccountStatus_ID, 1 AS Is_PromoOD
   FROM WH.DW_DIM_AccountStatuses
   where AccountStatus_Desc like '%Promo OD%'
 ) AS dt
on a. AccountStatus_ID = dt. AccountStatus_ID

 And this is usually more efficient, less steps in Explain.