DECODE Function in Oracle

What is the purpose of the DECODE Function in Oracle?
The DECODE function in Oracle is a versatile and powerful tool that allows users to perform conditional transformations on data within an SQL query. It is similar to the CASE statement, but it offers a more compact syntax, making it a popular choice for simplifying complex queries and improving code readability.
The primary purpose of the DECODE function is to evaluate a given expression, compare it to a set of search values, and return the corresponding result value based on the match. In essence, it provides a convenient way to perform if-then-else logic within a query, thus enabling users to manipulate and transform data as per specific conditions.
The syntax for the DECODE function is as follows:

DECODE(expression, search1, result1, [search2, result2, ...], [default])

Parameters:

1. expression: The value or column to be evaluated.
2. searchN: The search value to compare with the expression.
3. resultN: The corresponding result value to be returned if the expression matches the search value.
4. default: Optional parameter that specifies the value to be returned if no match is found.

The DECODE function compares the expression with each searchN value sequentially. If a match is found, it returns the corresponding resultN value. If no match is found and a default value is provided, the function returns the default value; otherwise, it returns NULL.
Example Usage:
Suppose we have a table named employees with the following columns: employee_id, first_name, last_name, and job_code. We want to retrieve the full names of all employees along with a description of their job titles.
Here is a sample query using the DECODE function:

SELECT first_name || ' ' || last_name AS full_name,
       DECODE(job_code, 
              'MGR', 'Manager',
              'DEV', 'Developer',
              'HR', 'Human Resources',
              'Unknown') AS job_title
FROM employees;

In this example, the DECODE function evaluates the job_code column, comparing it with the specified search values ('MGR', 'DEV', and 'HR'). If a match is found, the corresponding job title is returned. If there is no match, the function returns 'Unknown'.

if (temp >32) 
then
  [ice is melting]
else
  [ice is solid]

DECODE(value, if1, then1, if2, then2, if3, then3, . . . ,else)

The DECODE and NVL functions are really special. They can be used for date, number, and character datatypes. I have found these two functions to be great time-savers when writing logic using Oracle SQL.

Think of DECODE as a mini-IF-statement, which can compare the value in a column or expression to a list of values and substitute a different value for each item on the list. The basic syntax is:

DECODE(col1,case1,value1 [, case2, value2,...] , 
defaultvalue) 

Add as many case and value pairs as you need. The final value is used when col1's value does not match any of the cases.
The MouseOver below shows an example of DECODE. Mouseover the state of Hawaii HI.

NVL is a simple function that replaces a null value with the specified value. It is useful when you want to perform arithmetic on columns that might contain null values. In these cases, a null value will cause the arithmetic to result in a null value. To avoid this, you can substitute zero for null value using the NVL function. Values that are anything other than null remain unchanged. The syntax is shown below.

Click the Exercise link below to write a query using DECODE and NVL.
Decode NVL Functions - Exercise
The next lesson concludes this module.