RelationalDBDesign RelationalDBDesign

Data Manipulation   «Prev 

DECODE and NVL functions

Example of DECODE

Explanation of an example of the DECODE function
  1. The column the DECODE function examines is named STATE.
  2. The first case looks at the value in STATE and compares it to this value (the literal 'HI').
  3. If STATE is equal to 'HI', then the DECODE function returns the literal shown here.
  4. The second case is considered if STATE does not contain 'HI.' Here, STATE is compared to 'AK.'
  5. If STATE equals 'AK,' the DECODE function returns this value.
  6. This is the final case. It applies to all rows where the value in STATE is neither 'HI' nor 'AK.' In these cases, the value shown here gets returned by the DECODE function.
  7. For your information, this name is the column alias. It is used as the column heading.
  8. Here are the results when using the CUSTOMER table in the pet store schema.

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 if-then 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.