Using INETERVAL With CASE Statement

Database
Enthusiast

Using INETERVAL With CASE Statement

Hello,

The following statement works if it is just a select

select case when grp_prpkg_exp_dt = '9999-12-31' then CAST('2007-12-31' AS DATE) - grp_prpkg_eff_dt
else grp_prpkg_exp_dt - grp_prpkg_eff_dt end as num
,grp_prpkg_eff_dt
from db_bidwd1v.dim_grp_prpkg_cvg
sample 5

but when I m trying to convert it to give me the number of months:

select case when grp_prpkg_exp_dt = '9999-12-31' then CAST('2007-12-31' AS DATE) - grp_prpkg_eff_dt MONTH(4)
else grp_prpkg_exp_dt - grp_prpkg_eff_dt MONTH(4) end as num
,grp_prpkg_eff_dt
from db_bidwd1v.dim_grp_prpkg_cvg
sample 5

It gives me error.

Can someone help me.

Thanks in advance.
4 REPLIES
Teradata Employee

Re: Using INETERVAL With CASE Statement

Hello,

What error you are getting?

Regards,

Adeel
Enthusiast

Re: Using INETERVAL With CASE Statement

It gives me syntax error saying that END should be there between grp_prpkg_eff_dt and MONTH
Teradata Employee

Re: Using INETERVAL With CASE Statement

So add parentheses:

CASE WHEN grp_prpkg_exp_dt = '9999-12-31'
THEN (CAST('2007-12-31' AS DATE) - grp_prpkg_eff_dt MONTH(4))
ELSE (grp_prpkg_exp_dt - grp_prpkg_eff_dt MONTH(4))
END AS num
Enthusiast

Re: Using INETERVAL With CASE Statement

COOL.. Thanks