09-06.Sing-Row Functions【DECODEとCASE】

■DECODE
　-DECODE関数は条件を加えてデータを違う値とかコラムに出力できる。
　-DECODE(VALUE, IF1, THEN1, IF2, THEN2....)で使用
　-VALUE値がIF1の場合、THEN1を返し、VALUE値がIF2の場合、THEN2値を返す。
　-DECODE関数内にDECODE関数使用可能

--部署番号が10ならACCOUNTING、20ならRESEARCH、30ならSALES、その他はOPERATIONSを出力する
SQL> SELECT deptno, DECODE(deptno, 10, 'ACCOUNTING' ,
  2                                20, 'RESEARCH' ,
  3                                30, 'SALES', 'OPERATIONS') name
  4  FROM dept;

    DEPTNO NAME
---------- ----------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

--10部署は給与の合計を、20部署は最大値を、30部署は最小値を出力する
SQL> SELECT deptno, DECODE(deptno, 10, SUM(sal),
  2                                20, MAX(sal),
  3                                30, MIN(sal)) sal
  4  FROM emp
  5  GROUP BY deptno;

    DEPTNO        SAL
---------- ----------
        30        950
        20       3000
        10       8750


--部署別の給与合計を出力する
SQL> SELECT deptno, NVL(SUM(DECODE(deptno, 10, sal)), 0) deptno10,
  2                 NVL(SUM(DECODE(deptno, 20, sal)), 0) deptno20,
  3                 NVL(SUM(DECODE(deptno, 30, sal)), 0) deptno30,
  4                 NVL(SUM(DECODE(deptno, 40, sal)), 0) deptno40
  5  FROM emp
  6  GROUP BY deptno;

    DEPTNO   DEPTNO10   DEPTNO20   DEPTNO30   DEPTNO40
---------- ---------- ---------- ---------- ----------
        30          0          0       8150          0
        20          0      10875          0          0
        10       8750          0          0          0

一般的に集計関数使用時は給与合計が行で検索されるが、
DECODEとMAX関数を使用すると列で値を表示できる

--部署別の給与合計を行で出力
SQL> SELECT d.deptno, NVL(SUM(e.sal), 0) sal
  2  FROM emp e, dept d
  3  WHERE e.deptno(+) = d.deptno
  4  GROUP BY d.deptno
  5  ORDER BY d.deptno;

    DEPTNO        SAL
---------- ----------
        10       8750
        20      10875
        30       8150
        40          0


--部署別の給与合計を列で出力
SQL> SELECT MAX(NVL(SUM(DECODE(deptno, 10, sal)), 0)) deptno10,
  2         MAX(NVL(SUM(DECODE(deptno, 20, sal)), 0)) deptno20,
  3         MAX(NVL(SUM(DECODE(deptno, 20, sal)), 0)) deptno30,
  4         MAX(NVL(SUM(DECODE(deptno, 20, sal)), 0)) deptno40
  5  FROM emp
  6  GROUP BY deptno;

  DEPTNO10   DEPTNO20   DEPTNO30   DEPTNO40
---------- ---------- ---------- ----------
      8750      10875      10875      10875

■CASE
　-CASE関数はDECODE関数でカバー出来ない比較演算を解決できる関数
　-DECODE関数での比較演算はGREATEST、LEAST等の関数を使用するが、
　　CASE関数では条件演算子をすべて使用できる。
　-CASE関数はIF..THEN..ELSEと似ている。WHEN句の次に色んな条件を入れられる。

--上記DECEODE例をCASE関数で検索した例
SQL> SELECT deptno,
  2  CASE deptno
  3    WHEN 10 THEN 'ACCOUNTING'
  4    WHEN 20 THEN 'RESEARCH'
  5    WHEN 30 THEN 'SALES'
  6    ELSE 'OPERATIONS'
  7  END as "Dept Name"
  8  FROM dept;

    DEPTNO Dept Name
---------- ----------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

WHEN句の次に演算子が来る例

--給与別に違うUP率を適用
SQL> SELECT ename,
  2     CASE
  3             WHEN sal < 1000 THEN sal+(sal*0.8)
  4             WHEN sal BETWEEN 1000 AND 2000 THEN sal+(sal*0.5)
  5             WHEN sal BETWEEN 2001 AND 3000 THEN sal+(sal*0.3)
  6             ELSE sal+(sal*0.1)
  7     END sal
  8  FROM emp;

ENAME             SAL
---------- ----------
SMITH            1440
ALLEN            2400
JONES          3867.5
MARTIN           1875
BLAKE            3705
CLARK            3185
SCOTT            3900
KING             5500
TURNER           2250
ADAMS            1650
JAMES            1710
........