Convert iff expression in informatica to snowflake query

28/09/2024 Kiến thức lập trình

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

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

Try this

CASE 
    WHEN SUBSTR(CODE, 1, 6) = 'CE-PUU' THEN '70NPU' || SUBSTR(CODE, 7)
    WHEN SUBSTR(CODE, 1, 5) = 'AR-70' THEN '70N' || SUBSTR(CODE, 6)
    WHEN SUBSTR(CODE, 1, 3) = 'HD-' THEN '70D' || SUBSTR(CODE, 4)
    ELSE 
        DECODE(SUBSTR(CODE, 1, 4), 
            'HP-B', '7PB', 
            'HP-A', '70P', 
            'HP-C', '7PC', 
            'HP-X', '7PX', 
            'IT-T', '70T', 
            'HP-D', '7PD', 
            'HP-Z', '7PZ', 
            SUBSTR(CODE, 1, 4)
        ) || SUBSTR(CODE, 5)
END

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website

LEAVE A COMMENT Hủy