DECODE Function in Oracle

Question: 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'.

In programming and logic, a common construction of a problem is in the pattern

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

DECODE follows this kind of logic where the conclusion follows necessarily from the premise. Basic structure and usage of DECODE.

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 diagram below shows an example of DECODE for the state of Hawaii HI.

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.

Suppose you are at House-O-Pets and you want to see total sales you had in each month of the year. You want a report where you have the month name across the top and the sum of that month's sales below it. Here is how to do it using DECODE. The example shows only three months for year 2000, to shorten the code:

SELECT 
SUM(DECODE(TO_CHAR(SALES_DATE,'MM'),'01',TOTAL_SALE_AMOUNT,0))
 January,
SUM(DECODE(TO_CHAR(SALES_DATE,'MM'),'02',TOTAL_SALE_AMOUNT,0))
 February,
SUM(DECODE(TO_CHAR(SALES_DATE,'MM'),'03',TOTAL_SALE_AMOUNT,0))
 March
FROM CUSTOMER_SALE
WHERE TO_CHAR(SALES_DATE,'YYYY') = '2000'

The results look like this:

JANUARY FEBRUARY MARCH --------- --------- --------- 0 20.47 108.03

Looking closely at the DECODE function, you can see that each column in the results adds all rows up. The difference between the columns is that certain rows are set to zero in each DECODE. For example, for the January column, if the sale was made in January, the number returned by the DECODE function is equal to the TOTAL_SALES_AMOUNT. Otherwise, the number returned is zero. In effect, you are filtering out all sales amounts other than the ones in January. The other two query expressions work the same way, except that the second one looks for February and the third one looks for March. Imagine the possibilities of this powerful tool.

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.