Decode in Informatica Summary
So, what is a decode in Informatica?
Think of the decode in Informatica like a CASE statement in SQL. Here is a quick SQL example to set the stage.
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
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.
CustomerAge <= 20, 'Young', CustomerAge > 20 AND CustomerAge <= 40, 'Knowledgeable', CustomerAge > 40 AND CustomerAge <= 60, 'Wise', 'Very Wise') 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’)
|27||26||2100||V3 Greater than 1000|
|56||52||1200||V2 Greater than 50|
|3||52||2300||V1 Less than 25|
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. Hopefully, I provided enough solid examples for you to understand what the decode in Informatica is all about. Enjoy!
I hold a B.S. in Mathematics and M.S. in System Engineering. I have also earned a Big Data and Social Analytics Certification from MIT.
Eccella is a high end, boutique style consulting firm focused, on creating data driven companies.
Find Aaron Gendle on LinkedIn -
Latest posts by Aaron Gendle (see all)
- 5 Reasons to Quit Your Job Today - June 20, 2017
- The 2017, Top 50
Data Driven CEO’s - March 1, 2017
- 25+ Advanced Informatica Interview Questions and Answers - February 7, 2015