snowflake-cloud-data-platforminformatica

Convert iff expression in informatica to snowflake query


I am trying to convert the below IFF expression from INFORMATICA to Snowflake SQL statement(the column code is a column from a table), but I am unable to:

iif(substr(CODE,0,6)='CE-PUU', 
    '70NPU'||substr(CODE,7),
    iif(substr(CODE,0,5)='AR-70', 
        '70N'||substr(CODE,6),
        iif(substr(CODE,0,3)='HD-', 
            '70D'||substr(CODE,4), 
            decode(substr(CODE,0,4),'HP-B','7PB','HP-A','70P','HP-C','7PC','HP-X','7PX','IT-T','70T','HP-D','7PD','HP-Z','7PZ',substr(CODE,0,4))||substr(CODE,5) 
        )
    )
)

i tried to use case statement but its throwing NULL


Solution

  • Snowflake has IFF instead of IIF

    so you could use it like it is:

    iff(substr(CODE,0,6)='CE-PUU', 
        '70NPU'||substr(CODE,7),
        iff(substr(CODE,0,5)='AR-70', 
            '70N'||substr(CODE,6),
            iff(substr(CODE,0,3)='HD-', 
                '70D'||substr(CODE,4), 
                decode(substr(CODE,0,4),
                    'HP-B','7PB',
                    'HP-A','70P',
                    'HP-C','7PC',
                    'HP-X','7PX',
                    'IT-T','70T',
                    'HP-D','7PD',
                    'HP-Z','7PZ',
                    substr(CODE,0,4))||substr(CODE,5) 
            )
        )
    )