Case operations / decoding function in Informatica

Question

Can someone help me write case statements in Informatica PowerCenter Designer? I am new to Informatica, and based on my limited experience, I feel that case reports are not supported. There is a decoding function with similar functionality, but I can not find good syntax examples.

I would really appreciate it if someone could give me some concrete examples of how to use case / decode statements in Informatica.

Many thanks for your help!

Answer 1

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

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

This is the 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 value of TRUE ,
• even parameters (2nd, 4th, etc.) are conditions ,
• Odd parameters (3rd, 5th, etc.) are return values ,
• the last parameter is the default value ,
• the first condition that evaluates the value of the 1st parameter (i.e., the first condition that is true) determines the return value,
• If none of the conditions is true, the last parameter is returned.

Answer 2

See also the IIF () function, which is often used to implement conditional logic:

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