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