Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions

Transformation Language Reference

Transformation Language Reference

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
Data 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
Data 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 mapping 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!