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 departmen, 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, usingthe 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
Next

The Power of Decode