The DECODE Function

Contact Us or call 1-877-932-8228
The DECODE Function

Many programming languages have the concept of conditional processing. Among the most common construct is an if/then structure. Oracle SQL does allow for conditional processing, but does not use if/then statements to accomplish it. Instead, the DECODE function and a CASE statement are provided.

The DECODE Function

In the demonstration below, the relevant arguments listed are as follows.

  • Argument 1: 1 as the base expression
  • Argument 2: 1 as the search expression for the first pair
  • Argument 3: 'option 1' as the result expression for the first pair
  • Argument 4: 2 as the search expression for the second pair
  • Argument 5: 'option 2' as the result expression for the second pair
  • Argument 6 (if included): the default value

Code Sample:

Conditional-Processing/Demos/decode_with_dual.sql
SELECT DECODE(1,1,'option 1',2,'option 2','anything else') AS result 
FROM dual;

SELECT 
	DECODE(2,1,'option 1',2,'option 2','anything else') AS result 
FROM dual;

SELECT 
	DECODE(99,1,'option 1',2,'option 2','anything else') AS result 
FROM dual;

SELECT 
	DECODE(99,1,'option 1',2,'option 2') AS result 
FROM dual;

The DECODE statement can be better understood by doing a few tests with hard-coded values against the dual table.

The first example returns 'option 1' (argument 3) because the base expression of 1 (argument 1) matches the search expression (1) in the first pair (which consists of arguments 2 and 3).

The next example uses 2 as the base expression, and so returns option 2.

The following example uses 99 as the base expression. Since none of the options match, the default value ('anything else') is returned.

The last example has no default value specified. No match is made, and NULL is returned.

In some cases, indentation can clarify the logic involved. This is clearly seen when multiple functions are nested together or a large numbers of arguments are required.

Code Sample:

Conditional-Processing/Demos/decode_dept_name.sql
SELECT 
	DECODE(
		department_name, 
		'Executive', 
		'THE EXECUTIVES!', 
		department_name) AS dept 
FROM departments;

-- Formatted...
SELECT 
	DECODE(
		department_name, 
		'Executive', 
		'THE EXECUTIVES!', 
		department_name
	) AS dept
FROM departments;

Having seen the use of decode with literals in the previous example, it is easier to understand the use of the function with fields in tables.

Code Sample:

Conditional-Processing/Demos/decode_commission.sql
SELECT 
	first_name, 
	last_name, 
	DECODE	(	commission_pct, 
				NULL, 'None', 
				commission_pct * 100  ||'%' 
			) AS commission_info 
FROM employees
ORDER BY first_name, last_name;

DECODE can not only evaluate literals or simple field values. It also can deal with expressions. In this example, the default value returns a calculated value representing the percentage commission for each employee.

Next