|
|
|
Forum Newbie
Group: Forum Members
Last Login: Yesterday @ 3:44:38 PM
Posts: 9,
Visits: 32
|
|
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.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 4:46:44 AM
Posts: 134,
Visits: 370
|
|
Hello,
What error you are getting?
Regards,
Adeel
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: Yesterday @ 3:44:38 PM
Posts: 9,
Visits: 32
|
|
|
It gives me syntax error saying that END should be there between grp_prpkg_eff_dt and MONTH
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: Today @ 10:56:35 AM
Posts: 298,
Visits: 363
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: Yesterday @ 3:44:38 PM
Posts: 9,
Visits: 32
|
|
|
|
|