Case Statements/Decode Function in Informatica [CLOSED]

Question

Could anyone help me with writing case statements in Informatica PowerCenter Designer? What I feel is that 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.

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.