Archive
Archive
DECODE() — Evaluates an expression against one or more alternatives and returns the matching response.
DECODE( expression, { comparison-value, result } [,...] [,default-result] )
The DECODE() function compares an expression against one or more possible comparison values. If the expression matches the comparison-value, the associated result is returned. If the expression does not match any of the comparison values, the default-result is returned. If the expression does not match any comparison value and no default result is specified, the function returns NULL.
The DECODE() function operates the same way an IF-THEN-ELSE, or CASE statement does in other languages.
The following example uses the DECODE() function to interpret a coded data column and replace it with the appropriate meaning for each code.
V13.x DocumentationSELECT title, industry, DECODE(salary_range,
'A', 'under $25,000',
'B', '$25,000 - $34,999',
'C', '$35,000 - $49,999',
'D', '$50,000 - $74,999',
'E', '$75,000 - $99,000',
'F', '$100,000 and over',
'unspecified') from survey_results
order by industry, title;The next example tests a value against three columns and returns the name of the column when a match is found, or a message indicating no match if none is found.
SELECT product_name, DECODE(?,product_name,'PRODUCT NAME',
part_name, 'PART NAME',
category, 'CATEGORY',
'NO MATCH FOUND')
FROM product_list ORDER BY product_name;