Home
Page
Back to Papers Logicdriven Column Calculations Userdefinable Report Ordering

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 ;) MultiPart Logic DECODE function calls can be nested to support multipart 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 