top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What is a Decode in Informatica?

+1 vote
3,650 views
What is a Decode in Informatica?
posted Feb 24, 2016 by Sachin

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

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.

enter image description here

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’)

enter image description here

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’)
enter image description here

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.

answer Feb 25, 2016 by Manikandan J
Similar Questions
0 votes

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?

0 votes

I'm new to Informatica and like to ask a question regarding infa ports. I found an example which show how to create an update flag in expression transform for updating data. The code in v_UPDATE_FLAG port looks like this:

IIF(NOT ISNULL(PREV_ITEM_KEY)
AND
(
DECODE(BONUS_FLAG,PREV_BONUS_FLAG,1,0) = 0 
OR
DECODE(DISCOUNT,PREV_DISCOUNT,1,0) = 0 
OR
DECODE(PRICE,PREV_PRICE,1,0) = 0 
),'TRUE','FALSE')

Can you explain what this does? What the DECODE does here??

DECODE(BONUS_FLAG,PREV_BONUS_FLAG,1,0) = 0 
+2 votes

I'm new to Informatica and like to ask a question regarding infa ports. I found an example which show how to create an update flag in expression transform for updating data. The code in v_UPDATE_FLAG port looks like this:

IIF(NOT ISNULL(PREV_ITEM_KEY)
AND
(
DECODE(BONUS_FLAG,PREV_BONUS_FLAG,1,0) = 0 
OR
DECODE(DISCOUNT,PREV_DISCOUNT,1,0) = 0 
OR
DECODE(PRICE,PREV_PRICE,1,0) = 0 
),'TRUE','FALSE'

)

Can anyone explain what this does? What the DECODE does here??

DECODE(BONUS_FLAG,PREV_BONUS_FLAG,1,0) = 0 
0 votes

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

+1 vote

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!

...