DECODE (SQL)

DECODE (SQL)

#############################################################################################
--
-- %Purpose: DECODE as a very effizient use of IF-THEN-ELSE   
--
-- Use:      DECODE(expr,search,result,default)
--           if expr IS equal to search, Oracle returns result,
--           if no match is found, Oracle returns default.
--
-- #############################################################################################
--
-- Quite slow is .......
--
select count(*), sum(sal)
from emp
where DEPTNO = 10
and ename like 'SMITH%';
--
select count(*), sum(sal)
from emp
where DEPTNO = 30
and ename like 'SMITH%';
--
-- .... the same result much more efficiently with DECODE
--
-- Remeber that NULL values are never included in, nor do they affect the
-- outcome of, the COUNT and SUM functions
--
SELECT COUNT(DECODE(deptno,10,'*',NULL)) D10_count,
       COUNT(DECODE(deptno,30,'*',NULL)) D30_count,
         SUM(DECODE(deptno,10,sal,NULL)) D10_sal,
         SUM(DECODE(deptno,30,sal,NULL)) D30_sal
FROM emp
WHERE ename like 'SMITH%';