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

Basic Usage

Now let's turn to our first example. All of the examples included are designed to work against the standard SCOTT.EMP and SCOTT.DEPT tables and have been tested against Personal Oracle 7.2, Personal Oracle 8.0.3 and Oracle Lite 3.5. All the execution plans were generated by the SQL*Plus Autotrace facility, running against Personal Oracle 8.0.3 and using the rule-based optimiser. This is useful for demonstration purposes because it's more likely to give consistent execution plans across the small data volumes in the example tables and real-world volumes. It also means that the results should be the same on any database without the need to generate any statistics on the example tables. (i.e. Do try this at home. If the SCOTT example tables are in place, these examples should work unmodified.) Whether you use the cost or rule-based optimiser, the principles still hold true.

Example 1 is a simple example, illustrating the way in which DECODE is most commonly used, to improve report formatting.

Example 1

SELECT ename Name,
 	DECODE(deptno, 10, 'Accounting',
 			20, 'Research',
 			30, 'Sales',
 			40, 'Operations',
 			'Unknown') Department
 FROM emp;
 NAME 		DEPARTMENT
 ---------- 	----------
 KING 		Accounting
 BLAKE 		Sales
 CLARK 		Accounting
 JONES 		Research
 MARTIN 	Sales
 ALLEN 		Sales
 TURNER 	Sales
 JAMES 		Sales
 WARD 		Sales
 FORD 		Research
 SMITH 		Research
 SCOTT 		Research
 ADAMS 		Research
 MILLER 	Accounting
 14 rows selected.

Of course, this is not only a simple example of the DECODE function in action, but also of flawed programming practice because the statement assumes that the hard-coded deptno value will not be modified. For a situation like this, we would probably join to a reference table (dept) containing the correct department name. For quick ad-hoc queries, however, this can be much more efficient and there are certain values which it may be safe to hard-code in this way (e.g. Male/Female)

Although translating code values into readable descriptions in reporting applications is the most common and obvious use of DECODE (particularly given the name of the function), it masks some of the more powerful general functionality which we will turn to next.

Previous