Oracle SQL 関数 : DECODE : コードデータ置換え

 DECODE( <式1>,<判定値1>,<結果値1>[,<判定値2>,<結果値2>, ... ] [ ,<デフォルト値> ] )

 DECODEは<式1>を<判定値1>から判定し等しい場合はその<結果値1>を返します。等しくない場合は順次
 <判定値2>,<判定値3>...と処理を進めます。全て等しくない場合はデフォルト値が設定されている場合
 そのデフォルト値を返します。全て一致しない場合はNULLを返します。
 このDECODEを利用することで1個のSQL文の中でIF~THEN~ELSEの処理を記述することができます。

SQL> SELECT DECODE(1,1,'CODE1',2,'CODE2',3,'CODE3','OTHER') AS DEC1,
  2         DECODE(3,1,'CODE1',2,'CODE2',3,'CODE3','OTHER') AS DEC3,
  3         DECODE(0,1,'CODE1',2,'CODE2',3,'CODE3','OTHER') AS DEC0,
  4         DECODE(0,1,'CODE1',2,'CODE2',3,'CODE3') AS DECN
  5  FROM DUAL;

DEC1  DEC3  DEC0  D
----- ----- ----- -
CODE1 CODE3 OTHER

4番目のDECODEは結果をNULLを返していますが、以下のSQLであればNULLであることがはっきりします。

SQL> SELECT NVL(DECODE(0,1,'CODE1',2,'CODE2',3,'CODE3'),'This is NULL.') FROM DUAL;

NVL(DECODE(0,
-------------
This is NULL.

その他のDECODEの便利な使い方としては、DECODEの結果とCOUNT,SUM等の関数を組み合わせるといろいろな
応用が利くと思います。以下に、EMP表を用いてDEPTNO毎の件数とSALの合計を計算しています。

SQL> SELECT SUM(DECODE(DEPTNO,10,1,0)),SUM(DECODE(DEPTNO,20,1,0)),SUM(DECODE(DEPTNO,30,1,0)),
  2         SUM(DECODE(DEPTNO,10,SAL,0)),SUM(DECODE(DEPTNO,20,SAL,0)),SUM(DECODE(DEPTNO,30,SAL,0))
  3  FROM EMP;

SUM(DECODE(DEPTNO,10,1,0)) SUM(DECODE(DEPTNO,20,1,0)) SUM(DECODE(DEPTNO,30,1,0))
-------------------------- -------------------------- --------------------------
SUM(DECODE(DEPTNO,10,SAL,0)) SUM(DECODE(DEPTNO,20,SAL,0)) SUM(DECODE(DEPTNO,30,SAL,0))
---------------------------- ---------------------------- ----------------------------
                         3                          5                          6
                        8750                        10875                         9400

上のSELECT文の件数を取得する部分を以下のSELECT文の様にCOUNT()関数を用いても可能です。
COUNTはNULL値を計数しないのでDECODEのデフォルト値をNULLにしています。
(COUNT,SUM,AVGのグループ関数はNULLを処理の対象としないことを利用しています)

SQL> SELECT COUNT(DECODE(DEPTNO,10,1,NULL)),COUNT(DECODE(DEPTNO,20,1,NULL)),COUNT(DECODE(DEPTNO,30,1,NULL))
  2  FROM EMP;

COUNT(DECODE(DEPTNO,10,1,NULL)) COUNT(DECODE(DEPTNO,20,1,NULL)) COUNT(DECODE(DEPTNO,30,1,NULL))
------------------------------- ------------------------------- -------------------------------
                              3                               5                               6


さらに SIGN 関数と前述の DECODE 関数を組み合わせる例を示します。
(この内容は SIGN 関数でも同じ内容を説明しています)
「TM_担当者」の部門コードでコードの値が「1」の担当者のみを「1」として返し、 その他を「0」として返します。

SQL> SELECT DECODE(SIGN(部門コード - 1), 0, 1, 0) FROM TM_担当者;

DECODE(SIGN(部門コード-1),0,1,0)
--------------------------------
                               1
                               1
                               1
                               1
                               0
                               0
                               0
                               0
                               0

9行が選択されました。

そこで部門コードが「2」の担当者のみを「1」その他を「0」とする DECODE 関数処理と、 部門コードが「3」の担当者のみを「1」その他を「0」とする DECODE 関数処理を追加します。
そうすると、以下の様に部門コード毎に対応するレコードが抽出できます。

SQL> SELECT
  2   DECODE(SIGN(部門コード - 1), 0, 1, 0) AS 部門1
  3  ,DECODE(SIGN(部門コード - 2), 0, 1, 0) AS 部門2
  4  ,DECODE(SIGN(部門コード - 3), 0, 1, 0) AS 部門3
  5  FROM TM_担当者;

     部門1      部門2      部門3
---------- ---------- ----------
         1          0          0
         1          0          0
         1          0          0
         1          0          0
         0          1          0
         0          1          0
         0          0          1
         0          0          1
         0          0          1

9行が選択されました。

上の例の「部門1」「部門2」「部門3」のところで SUM 関数をとってやれば、 一括で部門毎の件数が取得できます。

SQL> SELECT
 2   SUM(DECODE(SIGN(部門コード - 1), 0, 1, 0)) AS 部門1
 3  ,SUM(DECODE(SIGN(部門コード - 2), 0, 1, 0)) AS 部門2
 4  ,SUM(DECODE(SIGN(部門コード - 3), 0, 1, 0)) AS 部門3
 5  FROM TM_担当者;

    部門1      部門2      部門3
--------- ---------- ----------
        4          2          3


DECODE 関数はOracle特有の関数ですので他のSQL言語では CASE 式になりますので、 上の処理をそのまま CASE 式に置き換えて以下の様にしても同様の処理ができます。

SQL> SELECT
  2   SUM(
  3      CASE 部門コード - 1
  4          WHEN 0 THEN 1
  5          ELSE 0
  6      END
  7   ) AS 部門1
  8  ,SUM(
  9      CASE 部門コード - 2
 10          WHEN 0 THEN 1
 11          ELSE 0
 12      END
 13   ) AS 部門2
 14  ,SUM(
 15      CASE 部門コード - 3
 16          WHEN 0 THEN 1
 17          ELSE 0
 18      END
 19   ) AS 部門3
 20  FROM TM_担当者;

     部門1      部門2      部門3
---------- ---------- ----------
         4          2          3

「部門コード - 1」の部分は WHEN で直接値を見た方がすっきりします。

SQL> SELECT
 SUM(
    CASE 部門コード
        WHEN 1 THEN 1
        ELSE 0
    END
 ) AS 部門1
,SUM(
    CASE 部門コード
        WHEN 2 THEN 1
        ELSE 0
    END
 ) AS 部門2
,SUM(
    CASE 部門コード
        WHEN 3 THEN 1
        ELSE 0
    END
 ) AS 部門3
FROM TM_担当者;

     部門1      部門2      部門3
---------- ---------- ----------
         4          2          3

■DECODE : NULL値 の判定


DECODE 関数は NULL値 の判定も行えますので NULL値 以外のものとの違いを処理できます。
以下の例は「上司コード」の NULL 判定を行い NULL の場合には値を「0」と表示します。

SQL> SELECT
  2    上司コード
  3   ,DECODE(上司コード, NULL, 0, 1, 100, 上司コード) AS 変換上司コード
  4  FROM TM_担当者;

上司コード 変換上司コード
---------- --------------
                        0
                        0
                        0
         1            100
         1            100
         1            100
         2              2
         2              2
         3              3

9行が選択されました。

■DECODE : NULL値 の判定を CASE文 に置き換える


DECODE 関数は NULL を判定値とした場合 TRUE となりますが CASE文 では IS NULL の判定式を使います。
以下は、上記の例の「上司コード」のDECODE判定を CASE文 に置き換えています。

SQL> SELECT
  2    上司コード
  3   ,CASE
  4     WHEN 上司コード IS NULL THEN 0
  5     WHEN 上司コード = 1     THEN 100
  6     ELSE 上司コード
  7    END AS 変換上司コード
  8  FROM TM_担当者;

上司コード 変換上司コード
---------- --------------
                        0
                        0
                        0
         1            100
         1            100
         1            100
         2              2
         2              2
         3              3

9行が選択されました。

■DECODE : WHERE句 での使用


DECODE 関数は WHERE句 の中でも使用できます。
以下の例は「上司コード」が NULL の場合には値を「0」として判定し、「上司コード」が「1」以下のデータを表示します。

SQL> SELECT
  2    上司コード
  3   ,担当者名
  4  FROM TM_担当者
  5  WHERE DECODE(上司コード, NULL, 0, 上司コード) <= 1;

上司コード 担当者名
---------- --------------------------------
           斎藤
           山田
           田中
         1 島田
         1 鈴木
         1 田村

6行が選択されました。

■DECODE : ORDER BY句 での使用


DECODE 関数は ORDER BY句 の中でも使用できます。
以下の例は「上司コード」のみを ORDER BY句 に指定した場合と、 DECODE 関数で「上司コード」が NULL の場合には値を「0」として判定し指定した場合を示します。

SQL> SELECT
  2    上司コード
  3   ,担当者名
  4  FROM TM_担当者
  5  ORDER BY 上司コード;

上司コード 担当者名
---------- --------------------------------
         1 鈴木
         1 田村
         1 島田
         2 山下
         2 山村
         3 多田
           田中
           山田
           斎藤

9行が選択されました。

SQL> SELECT
  2    上司コード
  3   ,担当者名
  4  FROM TM_担当者
  5  ORDER BY DECODE(上司コード, NULL, 0, 上司コード);

上司コード 担当者名
---------- --------------------------------
           斎藤
           山田
           田中
         1 島田
         1 鈴木
         1 田村
         2 山下
         2 山村
         3 多田

9行が選択されました。

通常 NULL を含むカラムを ORDER BY句 に指定すると、NULL データは後ろの方に出てきます。
そこで DECODE 関数で「上司コード」を変換する様にすると、先頭の方に出力されます。
強制的に後の方にするのであれば、以下の様にします。

SQL> SELECT
  2    上司コード
  3   ,担当者名
  4  FROM TM_担当者
  5  ORDER BY DECODE(上司コード, NULL, 99999, 上司コード);

上司コード 担当者名
---------- --------------------------------
         1 鈴木
         1 田村
         1 島田
         2 山下
         2 山村
         3 多田
           田中
           山田
           斎藤

9行が選択されました。