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

User-definable Report Ordering

Because DECODE is treated in the same way as any other expression, it can appear in the SELECT, WHERE and ORDER BY clauses. We can use this to develop a generic reporting statement with parameter-driven ordering.

Example 6

SELECT d.dname department, e.ename employee, e.sal salary 
FROM 	emp e, dept d
WHERE d.deptno = e.deptno
ORDER BY DECODE(&&Order_Option,
	    	    'E', e.name,
	    	    'D', d.dname,
	    	     d.dname);

This statement will generate a report containing all employees, their salary and the department that they work in. If the Order_Option parameter is 'E', the report will be ordered by the employee's name and if it is 'D', by the employee's department. If the parameter is not one of these values, the report will be ordered by the employee's department name by default. This may be useful in reducing the amount of code contained in an application, but there is one very important factor to be aware of. As a direct consequence of the DECODE function in the ORDER BY clause, Oracle will not be able to use an index to control the ordering of the report so it will always perform a sort. That might not be too much of a problem for certain reports, but restricts the usefulness of this technique.

Previous

Next