5

Decode in Informatica with Examples

Decode in Informatica

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.

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.

DECODE(CustomerAge,

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.

CustomerID CustomerAge ReturnValue
1 34 Knowledgeable
2 76 Very Wise
3 15 Young
4 45 Very Wise

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

StarRating ReturnValue
2 Bad
4 Good
7 OK
5 Awesome!
1 Very Poor
3 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’)

V_1 V_2 V_3 ReturnValue
27 26 2100 V3 Greater than 1000
56 52 1200 V2 Greater than 50
73 2 501 Unknown
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!

Aaron Gendle

Aaron Gendle

Senior Consultant & Data Architect at Eccella
As a data architect, I have the privilege of solving challenging data problems on a daily basis.

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 -
Aaron Gendle

Comments 5

    1. Post
      Author
    1. Post
      Author
  1. The example one, did not provide required results in my testing. The decode result is always 999. Informatica Ver 9.5.1

    DECODE(IN_DEPARTMENT_ID, IN_DEPARTMENT_ID 30 and IN_DEPARTMENT_ID <= 50, 50 , 999)

    However, below worked fine.
    DECODE(IN_DEPARTMENT_ID, 30, 31, 50, 51 , 999)
    DECODE(TRUE, IN_DEPARTMENT_ID 30 and IN_DEPARTMENT_ID <= 50, 50 , 999)

Leave a Reply

Your email address will not be published. Required fields are marked *