DECODE Function

The DECODE expression is similar to the CASE expression in that it can print different results depending on the values found in a specified column.

DECODE Function


Element	Description	Restrictions	Syntax
expr, else_expr, then_expr, when_expr	Expressions whose values and data types can be evaluated	Data types of when_expr and expr must be compatible, as must then_expr and else_expr. Value of when_expr cannot be a NULL.	Expression

The expressions expr, when_expr, and then_expr are required. DECODE evaluates expr and compares it to when_expr. If the value of when_expr matches the value of expr, then DECODE returns then_expr.

The expressions when_expr and then_expr are an expression pair, and you can specify any number of expression pairs in the DECODE function. In all cases, DECODE compares the first member of the pair against expr and returns the second member of the pair if the first member matches expr.

If no expression matches expr, DECODE returns else_expr. If no expression matches expr and you specified no else_expr, then DECODE returns NULL.

You can specify any data type for the arguments, but two restrictions exist:

All instances of when_expr must have the same data type, or a common compatible type must exist. All instances of when_expr must also have the same (or a compatible) data type as expr.
All instances of then_expr must have the same data type, or a common compatible type must exist. All instances of then_expr must also have the same (or a compatible) data type as else_expr.

The DECODE function uses the same data type compatibility rules as a CASE expression. For more information on the compatibility of returned data types, see CASE expressions data type compatibility.

Example

Suppose that a user wants to convert descriptive values in the evaluation column of the students table to numeric values in the output. The following table shows the contents of the students table.


firstname	evaluation		firstname	evaluation
Edward	Great		Mary	Good
Joe	Not done		Jim	Poor

The user now enters a query with the DECODE function to convert the descriptive values in the evaluation column to numeric equivalents:

SELECT firstname, DECODE(evaluation,
   'Poor', 0,
   'Fair', 25, 
   'Good', 50, 
   'Very Good', 75,
   'Great', 100, 
   -1) as grade
FROM students;

The following table shows the output of this SELECT statement.


firstname	evaluation		firstname	evaluation
Edward	100		Mary	50
Joe	-1		Jim	0