You can use the DECODE function to
convert an expression of one value to another value. The DECODE function
has the following form:
DECODE(test, a, a_value, b, b_value, ..., n, n_value, exp_m )
The DECODE function
returns a_value when a equals test, and returns b_value when b equals test,
and, in general, returns n_value when n equals test.
If
several expressions match test, DECODE returns n_value for
the first expression found. If no expression matches test, DECODE returns exp_m;
if no expression matches test and there is no exp_m, DECODE returns
NULL.
Restriction: The DECODE function
does not support arguments of type TEXT or BYTE.
Suppose
an employee table exists that includes emp_id and evaluation columns.
Suppose also that execution of the following query on the employee table
returns the rows that the result shows.Figure 1: Query
SELECT emp_id, evaluation FROM employee;
Figure 2: Query
result
emp_id evaluation
012233 great
012344 poor
012677 NULL
012288 good
012555 very good
In some cases, you might want to convert a set of values.
For example, suppose you want to convert the descriptive values of
the evaluation column in the preceding example to corresponding
numeric values. The following query shows how you might use the DECODE function
to convert values from the evaluation column to numeric values
for each row in the employee table.Figure 3: Query
SELECT emp_id, DECODE(evaluation, "poor", 0, "fair", 25, "good",
50, "very good", 75, "great", 100, -1) AS evaluation
FROM employee;