The DECODE function is without doubt one of the most powerful in Oracle's SQL.
It is one of several extensions Oracle added to the standard SQL language. DECODE can be used for the generation of crosstab reports.
You can also use the CASE function and the COALESCE function to execute complex logical tests within your SQL statements. DECODE and CASE are often used to pivot data, that is, to turn rows of data into columns of a report.
Here is the format for DECODE:
DECODE(value, if1, then1, if2, then2, if3, then3, . . . ,else)
Here, value represents any column in a table (regardless of datatype) or any result of a computation, such as one date minus another, a SUBSTR of a character column, one number times another, and so on.
value is tested for each row.
If value equals if1, then the result of the DECODE is then1;
if value equals if2, then the result of the DECODE is then2.
This continues for virtually as many
pairs as you can construct. If value equals none of the ifs, then the result of the DECODE is else. Each of the ifs and thens as well as the else also can be a column or the result of a function or computation.
You can have up to 255 elements within the parentheses.