top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

Case Statements/Decode Function in Informatica [CLOSED]

+1 vote
204 views

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.

This question has an answer at: Case Statements/Decode Function in Informatica
posted Mar 27, 2014 by Pooja Bhanout

Share this question
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button

1 Answer

0 votes

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 Mar 27, 2014 by Shweta Singh
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...