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!

Steiger

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.

Yokel

Answer 2

Look also at the IIF() function which is often used to implement conditional logic:

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

Sipes

This will work but it will also get messy quickly as the number of conditions grows. DECODE allows to write more readable expressions. – Yokel