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

Multi-Part Logic

DECODE function calls can be nested to support multi-part logic. For example, if we want to repeat our previous example, but restrict our salary increase of £1000 to those employed in the SALES department, we could use the following statement.

Example 5a

SELECT d.dname department,
	SUM(DECODE(GREATEST(e.sal, 10000), 10000, 
			DECODE(d.dname, 'SALES', e.sal + 1000, e.sal),	
		e.sal)) projected_salary_bill
FROM 	emp e, dept d
WHERE d.deptno = e.deptno
GROUP BY d.dname;
DEPARTMENT     PROJECTED_SALARY_BILL
-------------- ---------------------
ACCOUNTING                      8750
RESEARCH                       10875
SALES                          15400

Oracle first checks to see if the employee's salary is less than £10000, using the GREATEST function. If it is, then Oracle will check whether the employee's department is SALES. If both tests are true, then e.sal+1000 will be returned. In all other cases, e.sal will be returned because that is the default value of both the inner and outer DECODEs.

Nested DECODEs facilitate AND logic (i.e. if the inner test AND the outer test are true), but what about implementing OR logic using DECODE? In practice, this is very simple. All that's required is for us to implement multiple search values to test the initial expr against which return the same result. If we wanted to modify our previous example to show the result of giving the same salary increase if the employee's department is SALES OR RESEARCH, it might look something like this.

Example 5b

SELECT d.dname department,
	        SUM(DECODE(GREATEST(e.sal, 10000),
	            	10000, DECODE(d.dname,
	                	'SALES', e.sal + 1000,
                         	'RESEARCH, e.sal + 1000,
	                    	e.sal),	
	                e.sal)) projected_salary_bill
FROM 	emp e, dept d
WHERE d.deptno = e.deptno
GROUP BY d.dname;
DEPARTMENT     PROJECTED_SALARY_BILL
-------------- ---------------------
ACCOUNTING                      8750
RESEARCH                       15875
SALES                          15400

Previous

Next