DECODE
DECODE allows data value transformation during run-time retrieval.
Syntax
DECODE (expression, string1, result1 [, stringN, resultN][, default]) columnNameAlias
Remarks
The DECODE function is similar to an IF-THEN-ELSE statement, where a regular expression can be compared to one or more values, and if the expression equals a specified value, the corresponding replacement value is returned.
• DECODE can be used to resolve strings into digital values for counting or other purposes.
• The expression and any of the strings can be a table.column, a regular expression, or values that are compared with each other for equality.
• The expression must resolve to a single value, but the string can be any value that resolves to TRUE or FALSE in an equality function.
• If the compared arguments are equal, the value of the result corresponding to the string is returned; otherwise, the specified default value or null is returned.
• Each string is compared with the expression in sequential order, even if the expression does not match a prior string.
• If a default value is specified, it is returned if the expression does not match any of the strings.
Example (Expanding a One-Letter Code)
This example performs a mapping from a one-letter code to a more meaningful value.
SELECT TBL_user.user_id "User ID",
DECODE (TBL_user.gender,
'F', 'Female',
'M', 'Male',
'unspecified') Gender,
TBL_user.first_name "First Name"
FROM /shared/examples/NORTHBAY/"user" TBL_user
Similar syntax could be used to convert a pair of one-letter Boolean values (T/F, 1/0, etc.) to a value of TRUE or FALSE.
Example (Mapping States to Regions)
This example performs a mapping from states to regions.
SELECT *,
DECODE (customers.StateOrProvince,
'Al', 'East',
'Ak', 'North',
'Ar', 'Midwest',
'Az', 'West',
'Somewhere else') Region
FROM /shared/examples/ds_orders/customers customers
ORDER BY Region
Example (Nesting DECODE in Other Functions)
DECODE can be nested within other functions. This can be useful for counting occurrences of a particular value.
In this example, the number of suppliers in each of three states is counted after deriving a string to either a 1 or a 0.
SELECT
SUM (DECODE (suppliers.StateOrProvince, 'CA', 1, 0)) California,
SUM (DECODE (suppliers.StateOrProvince, 'NY', 1, 0)) "New York",
SUM (DECODE (suppliers.StateOrProvince, 'PA', 1, 0)) Pennsylvania
FROM /shared/examples/ds_inventory/suppliers