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',, '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.


The Power of Decode