Informatica Case Statement with date
I'm trying to do a case statement with three columns which are 30_Pst_Due_dt, 60_Pst_Due_dt, 90_Pst_Due_dt. I need it to do when 30_pst_Due_dt is between 1 and 29 then '129 days'. When 60_Pst_Due_dt is between 30 and 59 then '3059 days', when 90_Pst_Due_dt between 60 and 89 then '6089 days'.
I have wrote the following in informatica but I got an Pm parse Error: IIF(30_Pst_Due_dt between 1 and 29, '129 days', IIF(60_Pst_Due_dt between 30 and 59, '30  59 days', IIF(90_Pst_Due_dt between 60 and 89, '60  89 days')))
1 answer

Between doesn't work in infa. You need to use old school
>=
and<=
.Since you mentioned pst_due is a date column, add few variable ports to calculate date difference with sysdate. And then use that in the iif clause to calculate final bucket.
v_30_Pst_Due_dt(int)= date_diff(30_Pst_Due_dt,SYSDATE,'D') v_60_Pst_Due_dt(int)= date_diff(60_Pst_Due_dt,SYSDATE,'D') v_90_Pst_Due_dt(int)= date_diff(90_Pst_Due_dt,SYSDATE,'D') Out_bucket (string)= IIF(v_30_Pst_Due_dt >= 1 and v_30_Pst_Due_dt <=29, '129 days', IIF(v_60_Pst_Due_dt >= 30 and v_60_Pst_Due_dt <=59, '3059 days', IIF(v_90_Pst_Due_dt >= 60 and v_90_Pst_Due_dt <=89, '6089 days' )))
Pls make sure to add rest of your port just like 30_past_due calculation.
do you know?
how many words do you know