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')))

1 answer

  • answered 2021-12-31 05:12 Koushik Roy

    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, '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.

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum