Case Statements/Decode Function in Informatica

Question

Could anyone help me with writing case statements in Informatica PowerCenter Designer? I am fairly new to Informatica, and based on my limited experience I feel case statements aren't supported. There is a decode function with similar functionality, but I am unable to find any good examples on the syntax.

I would really appreciate if anyone could give me some specific examples on how to use case statements/decode function in Informatica.

Thanks much for your help!

Answer 1

You're right - there is no CASE statement, but you can use DECODE to simulate it:

DECODE( TRUE
      , DECIMAL_PORT > 0, 'positive value'
      , DECIMAL_PORT < 0, 'negative value'
                        , 'zero' )

It is an equivalent of the following Transact-SQL CASE statement:

CASE
  WHEN DECIMAL_PORT > 0 THEN 'positive value'
  WHEN DECIMAL_PORT < 0 THEN 'negative value'
  ELSE 'zero'
END

Here's how it works:
•The 1st parameter is a hard-coded TRUE value,
•Even parameters (2nd, 4th and so on) are the conditions,
•Odd parameters (3rd, 5th and so on) are the return values,
•The last parameter is the default return value,
•The first condition that evaluates to the value of the 1st parameter (i.e. the first condition that is true) determines the value that is returned,
•If none of the conditions is met the last parameter is returned

Answer 2

We can use IIF() function to implement conditional logic:
IIF(DECIMAL_PORT > 0, 'positive value', IIF(DECIMAL_PORT < 0 ,'negative value', 'zero'))

NOTE: If conditions are less then we can use IIF() function, if there are many condition then we can use Decode function explained below.