The DECODE() Function

tags: #sql/useful_functions

The DECODE function is a conditional function in SQL, primarily associated with Oracle databases. It's used to perform conditional logic similar to the CASE expression found in many other database systems.

It takes an expression and a set of search-result pairs and returns the result associated with the first matching search value. If no match is found, it returns a default value if provided, otherwise NULL.

General Syntax:

DECODE(
	expression, -- value to compare against search values
	search_value1, -- valeus to compare to the expression against
	result1, -- values to return if expression matches the search_value
	search_value2, 
	result2, 
	..., 
	default_result -- defautl value to return if no matches are found
)

What is it?

The DECODE function evaluates expression against each search_value in order. If it finds a match, it returns the corresponding result. If no matches are found, it returns default_result if specified, or NULL otherwise.

Use `DECODE()` with `MAX()`

When you use MAX with DECODE, it evaluates the conditions provided by DECODE and returns the maximum value among the results; i.e., by combining MAX with DECODE allows you to find the maximum value among the results of conditional expressions, providing a flexible way to perform complex logic and aggregations in SQL queries.

Consider the following scenario:

Student	Grade
John	A
Alice	B
Bob	C
Emily	D

SELECT MAX(DECODE(grade,
                  'A', 4,
                  'B', 3,
                  'C', 2,
                  'D', 1,
                  0)) AS max_score
FROM student_grades;

Applying the MAX(DECODE(...)) expression to this data would result in the maximum score being 4, as 'A' corresponds to the highest numeric score for the column grade.

Nested Decode()

Here, we are using nested DECODE functions to conditionally transform data based on the values of different columns, such that each nested DECODE function evaluates a different column (column3 or column4`) and returns a result based on predefined conditions or defaults.

select 

  column1,
  column2,
  -- Example of nested DECODE
  decode(
    expression,
    searchValue1, decode(
					      column3,
					      value2_1, result2_1,
					      value2_2, result2_2,
					      default_result2
					    ),
    searchValue2, decode(
						      column4,
						      value3_1, result3_1,
						      value3_2, result3_2,
						      default_result3
						    ),
    default_result1
  ) as combined_result
from your_table

Use DECODE() with MAX()

Nested Decode()

Use `DECODE()` with `MAX()`