Menu
In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null. The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255.
Syntax
Description of the illustration decode.gif
Purpose
DECODE
compares expr
to each search
value one by one. If expr
is equal to a search
, then Oracle Database returns the corresponding result
. If no match is found, then Oracle returns default
. If default
is omitted, then Oracle returns null.The arguments can be any of the numeric types (
NUMBER
, BINARY_FLOAT
, or BINARY_DOUBLE
) or character types.- If
expr
andsearch
are character data, then Oracle compares them using nonpadded comparison semantics.expr
,search
, andresult
can be any of the datatypesCHAR
,VARCHAR2
,NCHAR
, orNVARCHAR2
. The string returned is ofVARCHAR2
datatype and is in the same character set as the firstresult
parameter. - If the first
search-result
pair are numeric, then Oracle compares allsearch-result
expressions and the firstexpr
to determine the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.
The
search
, result
, and default
values can be derived from expressions. Oracle Database uses short-circuit evaluation. That is, the database evaluates each search
value only before comparing it to expr
, rather than evaluating all search
values before comparing any of them with expr
. Consequently, Oracle never evaluates a search
if a previous search
is equal to expr
.Oracle automatically converts
expr
and each search
value to the datatype of the first search
value before comparing. Oracle automatically converts the return value to the same datatype as the first result
. If the first result
has the datatype CHAR
or if the first result
is null, then Oracle converts the return value to the datatype VARCHAR2
.In a
DECODE
function, Oracle considers two nulls to be equivalent. If expr
is null, then Oracle returns the result
of the first search
that is also null.The maximum number of components in the
DECODE
function, including expr
, searches
, results
, and default
, is 255.See Also:
'Datatype Comparison Rules' for information on comparison semantics, 'Data Conversion' for information on datatype conversion in general, 'Floating-Point Numbers' for information on floating-point comparison semantics, and 'Implicit and Explicit Data Conversion' for information on the drawbacks of implicit conversionExamples
This example decodes the value
warehouse_id
. If warehouse_id
is 1, then the function returns 'Southlake
'; if warehouse_id
is 2, then it returns 'San Francisco
'; and so forth. If warehouse_id
is not 1, 2, 3, or 4, then the function returns 'Non domestic
'.Active7 years, 6 months ago
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!
Ashish SachdevaAshish Sachdeva
2 Answers
You're right - there is no
CASE
statement, but you can use DECODE
to simulate it:It is an equivalent of the following Transact-SQL
CASE
statement:Here's how it works:
- the 1st parameter is a hard-coded
TRUE
value, - even parameters (2nd, 4th and so on) are the conditions,
- odd parameters (3rd, 5th and so on) are the return values,
- the last parameter is the default return value,
- the first condition that evaluates to the value of the 1st parameter (i.e. the first condition that is true) determines the value that is returned,
- if none of the conditions is met the last parameter is returned.
14.4k66 gold badges7272 silver badges9494 bronze badges
Look also at the IIF() function which is often used to implement conditional logic:
arturroarturro1,49911 gold badge99 silver badges1111 bronze badges