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.
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.
This statement checks the deptno column of the emp table and if the value = 10, it displays 'Accounting'; if it's 20, it displays 'Research'; and so on. Notice as well that a default value of 'Unknown' has been specified, which will be displayed if the deptno column does not equal 10, 20, 30 or 40.
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.