DECODEやCASEでIF-THEN-ELSEを組み込む

条件次第で値を変更したいときはDECODEやCASEを使います。
SQLで取得条件を決めておけば、アプリ側で余分な処理を入れずに済みます。

覚えるべきキーワード
・DECODE(列名、条件1、値1、条件2、値2、デフォルト値)
・CASE 列名 WHEN 条件1THEN 値1WHEN 条件2 THEN 値2 ELSE デフォルト値 END

このサンプルテーブルを使って以下のSQLを解説します。

サンプルテーブル1

(テーブル名:employee、name:文字型、job:文字型、comission:数値)

name job comission
YUTO SE 20000
KENJI PG 10000
MAI TESTER 5000
YUJI PG 15000
RISA SE 18000

DECODE(列名、条件1、値1、条件2、値2、デフォルト値)

DECODE式は書き方がシンプルです。しかし、シンプルだからこそ中身を理解しておかないと誤った書き方になります。DECODE式を使ってSQLの例を書いてみます。

DECODE

SELECT name,job DECODE(job,’SE’,commission * 2,
’PG’ ,commission * 1.5,
commission) “JOB_RESULT”
FROM employee;

こちらはemployee表からname、job、comissionを取得していますが、comissionはjobの値に応じて、DECODEを使ってデータ取得をします。Jobの値が’SE’だったら、comissionに2を掛けて、PGだったら、1.5を掛けます。それ以外だったらcomissionをそのまま返します。列名は別列名でJOB_RESULTを指定します。
結果は以下になります。

name job JOB_RESULT
YUTO SE 40000
KENJI PG 15000
MAI TESTER 5000
YUJI PG 22500
RISA SE 36000

DECODEは、,(カンマ)で区切って構文を作ります。このSQLは条件が2つですが、数に縛りはありませんので、条件は書きたいだけ書けます。最後にデフォルト値(ここではcomission)を書きます。

CASE 列名 WHEN 条件1THEN 値1WHEN 条件2 THEN 値2 ELSE デフォルト値 END

CASE式の書き方は少し長いです。WHEN、THEN、ELSE、ENDがCASEのキーワードです。CASE式を使ってSQLの例を書いてみます。

CASE


SELECT name,job CASE job WHEN ’SE’ THEN commission * 2,
WHEN ’PG’ THEN commission * 1.5,
ELSE commission END “JOB_RESULT”
FROM employee;

こちらはDECODE式と同様にemployee表からname、job、comissionを取得していますが、comissionはjobの値に応じて、DECODEを使ってデータ取得をします。Jobの値が’SE’だったら、comissionに2を掛けて、PGだったら、1.5を掛けます。それ以外だったらcomissionをそのまま返します。列名は別列名でJOB_RESULTを指定します。DECODEのサンプルを見ながら、違いを認識してみてください。

DECODEは()で囲いますが、CASEはありませんね。

SUM関数と組み合わせる

DECODEはグループ関数のSUMやCOUNTと組み合わせて使うケースがあります。まずはSUM関数と組み合わせた書き方を紹介します。

SUMとDECODEを組み合わせる


SELECT SUM(DEOCODE(job,’SE’,1,0 “SE_COUNT”),
DEOCODE(job,’PG’,1,0 “PG_COUNT”),
DEOCODE(job,’TESTER’,1,0 “OTHERS”))
FROM employee;

JobがSEの時は1、PGの時は1、TESTERの時は1を指定して取得します。それ以外の場合は0を指定します。それぞれ、SE_COUNT、SE_COUNT、OTHERSの3つの列を表示します。
それぞれ、条件に該当しない場合(elseの場合)は0を指定していますが、これはnullじゃだめなのかを聞かれる時があります。答えはダメです。SUMを使っているので、NULLを対象にしたSUM関数はNULLになってしまうので、合計数もNULLになります。(NULLを計算対象にすると計算結果もnullになります。)SUMは値の合計値なので、対象の数を調べたいときにDECODEで指定する値は1と0が基本です。
SQLの実行結果は以下になります。

SE_COUNT PG_COUNT OTHERS
2 2 1

CASE式に変更しても同じです。下のSQLはDECODEをCASE式に変更しただけです。CASE式でも同じ要領です。

SUMとCASEを組み合わせる


SELECT SUM(CASE job WHEN ’SE’ THEN 1 ELSE 0 END “SE_COUNT”,
CASE job WHEN ’PG’ THEN 1 ELSE 0 END “PG_COUNT”,
CASE job WHEN ’TESTER’ THEN 1 ELSE 0 END “OTHERS”)
FROM employee;

COUNT関数と組み合わせる

DECODEとCOUNT関数を使って、対象データの数を調べたいときは任意の値とnullを使います。COUNTは値ではなく、値の数を調べるので、値が1だろうが、2だろうが、値の数は1つです。

COUNTとDECODEを組み合わせる


SELECT name,job,COUNT(DEOCODE(job,’SE’,1,null “SE_COUNT”),
DEOCODE(job,’PG’,2,null “PG_COUNT”),
DEOCODE(job,’TESTER’,3,null “OTHERS”))
FROM employee;

JobがSEの時は1、PGの時は2、TESTERの時は3を指定しています。(どんな値でも良いです。)そして、条件に該当しない場合(elseの場合)はnullを指定します。COUNT関数はnull値を無視するため、elseにnullを指定することで正確な値の数が集計できます。仮にelseに0を設定してしまうと、例えばjob値がPLの場合は、1つ目のDECODEで0、2つ目のDECODEも0、3つ目のDECODEも0になります。となるとCOUNTする際はそれぞれCOUNT数が1ずつ加算されてしまいます。合計数は誤った値になってしまいますね。ですので、elseには値が無視されるnullを指定します。

SQLの実行結果は以下になります。

SE_COUNT PG_COUNT OTHERS
2 2 1

CASE式とCOUNTを組み合わせる時も同じ要領でSQLを作れます。

COUNTとCASEをを組み合わせる


SELECT name,job,COUNT(CASE job WHEN ’SE’ THEN 1 ELSE null END “SE_COUNT”,
CASE job WHEN ’PG’ THEN 2 ELSE null END “PG_COUNT”,
CASE job WHEN ’TESTER’ THEN 3 ELSE null END “OTHERS”)
FROM employee;

DECODEやCASE式は頻繁に使います。DECODEで書ける条件はCASEで書けますし、逆もまた然りです。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です