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行が選択されました。
