Make your own free website on Tripod.com

Home Page

Back to Papers


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


Technical Papers Utilities and Scripts Book Reviews Links
My Resume Fun & Games Email Home