How is the decode function used in Oracle?

1 year ago
Noah Thompson
2 minutes

In Oracle, the DECODE function is used to evaluate an expression based on given conditions and return a result. The basic syntax is as follows:

Translate the given expression into its equivalent in plain English: DECODE(expression, search1, outcome1, search2, outcome2, …, default_outcome)

Among them,

  1. expr is the expression to be compared.
  2. Values like search1, search2, etc. are the ones to be compared with expr.
  3. Results 1, result2, etc. are the results returned when matched with the corresponding search value.
  4. The default_result is an optional value that is returned when the expr does not match any of the search values.

The functioning of the DECODE function is as follows:

  1. It first compares expr with search1, and if they are equal, it returns result1.
  2. If they are not equal, continue comparing expr with search2, and return result2 if they are equal.
  3. If there is no matching search value provided and a default_result is given, return the default_result.
  4. If there is no matching search value and no default_result provided, return NULL.

The DECODE function can be used for various purposes, such as returning different results based on different conditions, or mapping the values of a column to other values. Here are some examples of using the DECODE function:

  1. Convert gender code (1 for male, 2 for female) to descriptive text:
    SELECT CASE gender
    WHEN 1 THEN ‘Male’
    WHEN 2 THEN ‘Female’
    ELSE ‘Unknown’
    END AS gender_desc
    FROM employees;
  2. Calculate different bonus percentages based on the salary range of employees:
    SELECT DECODE(salary, 1000, 0.05, 2000, 0.1, 0.02) AS bonus_percentage
    FROM employees;
  3. Map the values of a column to other values:
    SELECT CASE status
    WHEN ‘A’ THEN ‘Activated’
    WHEN ‘I’ THEN ‘Disabled’
    ELSE ‘Unknown’ END AS status_desc
    FROM customers;

In conclusion, the DECODE function in Oracle is used to check an expression based on given conditions and returns a result, which can be used in various scenarios.

bannerAds