Home Page

Back to Papers


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/decode2.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

Definitions

The first thing to note is that the DECODE() function is defined in the Expr section of the Oracle SQL Language Reference Manual. This offers our first hint of the power of DECODE, because it indicates that we can use this function wherever we might use any other expression, in the SELECT, WHERE or ORDER BY clauses for example. So, here is the formal definition.

decode(expr,search1,result1[,search2,result,...][,default])

Where ...

expr is any valid expression, which could include column names, constants, bind variables, function calls or arithmetic expressions. (E.g. 12039, 'A String', emp.empno, emp.sal * 1.15). This will represent the A value in an A <-> B comparison, so this is the expression that we're going to test the value of.

The next two parameters appear together as one or more repeating pairs (e.g. search1/result1, search2/result2)

search[1-n] is any valid expression, which will represent the B value in the comparison. Note that, if search1 and expr are different data types then Oracle will attempt to convert expr and all search expressions to the same type as search1, but it is best not to depend on this automatic type casting. Another important point to note is that null equals null in a DECODE expression.

result[1-n] is the result returned by the function (or alternatively, the value of this expression) if the preceding search value matched expr. Oracle will only evaluate the minimum number of search expressions necessary to return the correct result. For example, if the server evaluates search1 and discovers that expr = search1 then the function will return result1 and will never evaluate search2, search3 etc. For this reason, you should order the search expressions in decreasing likelihood of matching expr, from left to right. This will minimise the small amount of processing overhead required to evaluate the expressions.

default is the value returned if none of the search expressions matches expr. If no value is specified for default and none of the search expressions match expr, then Oracle will return null. 

All of which is a slightly long-winded way of describing a very simple principle. Those of you with previous programming experience in other languages may find it simpler to understand a DECODE expression as a variation on an if … then … elseif … type of structure.

if (expr == search1) return(result1); elseif (expr == search2) return(result2); … elseif (expr == searchn) return(resultn); else return(default);
The switch structure used in C, Java and other languages is probably a more accurate equivalent.
switch ( expr ) { case search1 : return(result1); … case searchn : return(resultn); default : return(default); }

Previous

Next

Technical Papers Utilities and Scripts Book Reviews Links
My Resume Fun & Games Email Home