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