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行が選択されました。 |
本書用のWebアプリを使ってSQLを実行し学習していきます。初心者の方に分かりやすく、図表を多く使って解説されています。 学習の最後には200問越えのドリルが付いていてSQLのスキルが試せる様になっています。