Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • PowerCenter 10.5.7
  • All Products

Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions
  8. Creating Custom Functions
  9. Custom Function API Reference

Transformation Language Reference

Transformation Language Reference

Back Next

DECODE

DECODE

Searches a port for a value you specify. If the function finds the value, it returns a result value, which you define. You can build an unlimited number of searches within a DECODE function.
If you use DECODE to search for a value in a string port, you can either trim trailing blanks with the RTRIM function or include the blanks in the search string.

Syntax

DECODE( 
value
, 
first_search
, 
first_result
 [, 
second_search
, 
second_result
]...[,
default
] )
The following table describes the arguments for this command:
Argument
Required/
Optional
Description
value
Required
Any datatype except Binary. Passes the values you want to search. You can enter any valid transformation expression.
search
Required
Any value with the same datatype as the value argument. Passes the values for which you want to search. The search value must match the value argument. You cannot search for a portion of a value. Also, the search value is case sensitive.
For example, if you want to search for the string 'Halogen Flashlight' in a particular port, you must enter 'Halogen Flashlight, not just 'Halogen'. If you enter 'Halogen', the search does not find a matching value. You can enter any valid transformation expression.
result
Required
Any datatype except Binary. The value you want to return if the search finds a matching value. You can enter any valid transformation expression.
default
Optional
Any datatype except Binary. The value you want to return if the search does not find a matching value. You can enter any valid transformation expression.

Return Value

First_result
 if the search finds a matching value.
Default value if the search does not find a matching value.
NULL if you omit the default argument and the search does not find a matching value.
Even if multiple conditions are met, the
PowerCenter Integration Service
 returns the first matching result.
If the data contains multibyte characters and the DECODE expression compares string data, the return value depends on the code page and data movement mode of the
PowerCenter Integration Service
.

DECODE and Datatypes

When you use DECODE, the datatype of the return value is always the same as the datatype of the result with the greatest precision.
For example, you have the following expression: 
DECODE ( CONST_NAME
         'Five', 5,
         'Pythagoras', 1.414213562,
         'Archimedes', 3.141592654,
         'Pi', 3.141592654 )
The return values in this expression are 5, 1.414213562, and 3.141592654. The first result is an Integer, and the other results are Decimal. The Decimal datatype has greater precision than Integer. This expression always writes the result as a Decimal.
When you run a session in high precision mode, if at least one result is Double, the datatype of the return value is Double.
You cannot create a DECODE function with both string and numeric return values.
For example, the following expression is invalid: 
DECODE ( CONST_NAME
         'Five', 5,
         'Pythagoras', '1.414213562',
         'Archimedes', '3.141592654',
         'Pi', 3.141592654 )
When you validate the expression above, you receive the following error message: 
Function cannot resolve operands of ambiguously mismatching datatypes.

Examples

You might use DECODE in an expression that searches for a particular ITEM_ID and returns the ITEM_NAME: 
DECODE( ITEM_ID, 10, 'Flashlight',
                 14, 'Regulator',
                 20, 'Knife',
                 40, 'Tank',
                 'NONE' )
ITEM_ID  
RETURN VALUE
 
10
Flashlight
14
Regulator
17
NONE
20
Knife
25
NONE
NULL
NONE
40
Tank
DECODE returns the default value of NONE for items 17 and 25 because the search values did not match the ITEM_ID. Also, DECODE returns NONE for the NULL ITEM_ID.
The following expression tests multiple columns and conditions, evaluated in a top to bottom order for TRUE or FALSE: 
DECODE( TRUE,
        Var1 = 22, 'Variable 1 was 22!',
        Var2 = 49, 'Variable 2 was 49!',
        Var1 < 23, 'Variable 1 was less than 23.',
        Var2 > 30, 'Variable 2 was more than 30.',
        'Variables were out of desired ranges.')
Var1   
Var2   
RETURN VALUE
 
21 
47
Variable 1 was less than 23.
22 
49
Variable 1 was 22!
23 
49
Variable 2 was 49!
24 
27
Variables were out of desired ranges.
25 
50
Variable 2 was more than 30.
0 COMMENTS
We’d like to hear from you!