What is a Decode in Informatica?

Question

I have the following decode statement in an expression transformation in informatica:

(DECODE
(TRUE
, OPERATION1='I' and NOT ISNULL(a_new),'YES'
, OPERATION1='D'and NOT ISNULL(a_old),'YES'
, OPERATION1='U'and ( (
     (a<>b) 
  or (ISNULL(a_new) and NOT ISNULL(a_old)) 
  or (NOT ISNULL(a_new) and ISNULL(a_old))
      ) 
),'YES','NO CHANGE')
)

Where a_new and a_old are both integers (when they appear at all).

Here's the weird part:

This decode statement, when run, constantly returns a value of 0 (zero). I can't for the life of me figure out why. When I run this in the debugger and evaluate the decode (right click on the expression, click on 'evaluate expression', paste the decode in) it evaluates to either YES or NO CHANGE correctly. But when it actually runs, both in the debugger and in production, it still evaluates to zero. Does anybody know why?

Answer 1

Think of the decode in Informatica like a CASE statement in SQL. Here is a quick SQL example to set the stage.

SELECT CUSTOMER_ID,
CASE
WHEN CUSTOMERAGE <= 20 THEN 'Young' WHEN CUSTOMERAGE > 30 AND AGE <= 40 THEN 'Knowledgeable' WHEN    CUSTOMERAGE > 40 AND AGE = 60 THEN ‘Wise’
ELSE ‘Very Wise’
END AS CUSTOMER_WISDOM
FROM CUSTOMER

Many programming languages have functionality similar to this. It is very helpful to be able to assign a value to a variable based on input variable values. This is also very similar to an IF condition we find in many programming languages.

Ok, now that you have an idea what the decode in Informatica is, lets get into the details. Here is the formal decode syntax…

DECODE( value, first_search, first_result [, second_search,second_result]…[,default] )

Lets step through each section of the decode in Informatica.

VALUE – The value/port you want to search. Specify a valid expression transformation port. Any datatype except for boolean. This is similar to the CUSTOMERAGE field in our SQL CASE statement example above.

SEARCH – The values you want to search for in the VALUE parameter. The search value must match the the value parameter data exactly. A search string value of ‘Informatica Interview Questions’, must match exactly to a string in the value parameter. Decode value and search are case sensitive. This is similar to statement CUSTOMERAGE <= 20 in our SQL CASE statement example above. RESULT – The values you want to return based on the search and value data matching. This is similar our THEN results in our CASE statement example. So we would return ‘Knowledgeable’ if CUSTOMERAGE was 34.

DEFAULT – The value you want to return if our search data does not match our value data. This is similar to ELSE ‘Very Wise’ in our SQL CASE statement example above.

Ok, now that we have gone through each decode parameter, lets convert our SQL CASE statement example into a decode in Informatica.

Ok simple enough right? Let’s pass some records through the above DECODE in our expression transformation in Informatica to make sure you’ve got it.

I like learning through examples, so lets spend the rest of the posts working through a couple more.

Decode in Informatica Example 2

This decode in Informatica example demonstrates searching for an exact value and matching to a single result.

DECODE(StarRating, 1, ‘Very Poor’, 2, ‘Bad’, 3, ‘OK’, 4, ‘Good’, 5, ‘Awesome!’, ‘OK’)

Decode in Informatica Example 3

This next decode in Informatica example demonstrates using multiple variables as value parameters. When using this technique, we use TRUE or FALSE initially in the value parameter. Then use conditional statements in our search parameters. Lets take a look. Keep in mind decode will evaluate TRUE or FALSE from the first search to the last in that order. Whatever search gets a match first will be the decode return value for that record.

DECODE(TRUE, V_1 <= 25, 'V1 Less than 25', V_2 > 50, ‘V2 Greater than 50’, V_3 > 1000, ‘V3 Greater than 1000’, ‘Unknown’)

Decode in Informatica Summary

The decode in Informatica is a very helpful conditional statement. It allows us to apply traditional IF or CASE statement logic in the Informatica world with a simple function. Decode is used in an expression transformation to define a variable or output port. With transformation heavy mappings, you will likely have the need to leverage this function.