-- ############################################################################################# -- -- %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%';