Introduction

Definitions

Basic Usage

Logic-driven Column Calculations

Mind the Trap!

Beyond Equality

Multi-part Logic

User-definable Report Ordering

A Real-world Example

The Down-side

The Up-side

Please note this page has moved to http://oracledoug.com/decode6.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

Beyond Equality

The first few examples that we have looked at have tested whether columns are equal to specified values. However, it won't be long before someone asks us to produce a report based on columns being within a certain range. The HR Manager may want to know what the salary bill for the entire company would be if all employees whose salary was less than £10,000 received an increase of £1,000.

Using our newly acquired skills, our first attempt may look something like this.

Example 4a

SELECT	 SUM(DECODE(e.sal, < 10000, e.sal + 1000, e.sal) 
					projected_salary_bill
FROM 	emp e;
< 10000, e.sal + 1000,
      *
Error at line 2:
ORA-00936: missing expression

This statement isn't valid because Oracle doesn't treat '< 10000' as a valid expression to compare e.sal to. It's easy to doubt the value of DECODE when you first encounter this problem because it's difficult to imagine any significant application without range-checking abilities. Fortunately, a closer look at the variety of other functions at our disposal suggests that we could combine DECODE with other functions to support range checking, including the SIGN() and TRUNC() functions and the functions that we will look at here, GREATEST() and LEAST(). In the past, I tended to use and recommend the SIGN() function because it supports all combinations of <, > and = with one function call. The advantage of GREATEST and LEAST is that they are more widely available across Oracle versions (e.g. SIGN is not available in Oracle Lite 3.5, whereas GREATEST and LEAST are)

	GREATEST(expr, [expr, …])
	LEAST(expr, [expr, …])

Both functions accept a list of one or more expressions and return whichever expression is the greatest or least respectively. We'll use the GREATEST function to develop a working replacement for Example 4a.

Example 4b

SELECT	 SUM(DECODE(GREATEST(e.sal, 10000),
		    10000, e.sal + 1000,
		    e.sal)) 	projected_salary_bill
FROM 	emp e;
PROJECTED_SALARY_BILL
---------------------
	      43025

For each employee, this example compares the greater of the employee's salary and 10,000 and then, if the result is 10,000 (which would mean that the employee's salary is lower) it applies the salary increase.

There are a number of built-in functions that become even more powerful when combined with DECODE and these are well worth some additional investigation. If there isn't a function that supports what you are looking to achieve, you could develop your own function using PL/SQL and reference it within the parameters of the DECODE function.

Previous

Next