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 '1-29 days'. When 60_Pst_Due_dt is between 30 and 59 then '30-59 days', when 90_Pst_Due_dt between 60 and 89 then '60-89 days'.
I have wrote the following in informatica but I got an Pm parse Error: IIF(30_Pst_Due_dt between 1 and 29, '1-29 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')))
Between doesn't work in infa. You need to use old school
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, '1-29 days', IIF(v_60_Pst_Due_dt >= 30 and v_60_Pst_Due_dt <=59, '30-59 days', IIF(v_90_Pst_Due_dt >= 60 and v_90_Pst_Due_dt <=89, '60-89 days' )))
Pls make sure to add rest of your port just like 30_past_due calculation.