【虎の巻】第6回「SQL CASE式のユースケース」

データの値に応じて実行時にSQL文を動的に変化させることで、SQLの可能性が大きく広がります。
今回はSQLのCASE式を取り上げ、データに基づいて実行時にSQL文を変化させることで、どのように便利なことが容易に実現できるのかを例題を使って分かり易く解説します。（編集部）

SELECT文において、条件テストや選択肢を実行するにはCASE式を使用します。
本稿では、リック・フラグラーがSQLのCASE式のユースケースについて考察します。

10/11/2021 リック・フラグラー

SQLのSELECT文で条件に基づいて何か処理をしたいと思ったことはありませんか？きっとある筈です。
なぜなら、それは決して珍しいことではないからです。
RPGやその他の言語には、そのためにIF文やSELECT文があります。SQLプロシージャではIF文を使うこともできます。
しかし、SELECT文では条件に応じてテストしたり、選択肢を実行したりするにはCASE式を使います。

CASE式の基本形には次の２つがあります。

Case    when <条件文 1> then <文 1> 

            when <条件文 2> then <文 2> 

            else   <文 3> 

End

形式 1.

Case    <変数　または　式>

            when <値 1> then <文 1> 

            when <値 2> then <文 2> 

            else   <文 3> 

End

形式 2.

上記の形式で、条件文、式または変数の部分のお陰で、処理に最も便利な方を使うことができます。
複雑な述部または計算は、どの文が実行されるかを決めるために、条件文に置き換えてから値を調べることができます。
そして、文もまた簡単なまたは複雑な計算である可能性があります。

図１に列の値を判別して新しい記述列を作成するために、SELECT文に形式１および形式２のCASE式を挿入した例を示します。
例ではACCTYPEフィールドが繰り返し使用されているのに対し、例Bでは1回だけ使用されています。
ここでの鍵は、各WHEN文節で同じ値を検査したいということであり、それ故に形式Bが手っ取り早い方法となります。
これらのSELECT文が実行されると、各CASE式は同じ条件処理を行い、Statusという名前の列に結果を入れます。

虎の巻6_1 図1

他の形式と使用法

CASE式にとってのもう1つの難問は、日付が特別な意味をもつ値を含んでいる可能性のある、数値フィールド内の日付値の有効性検査をすることかもしれません。

手持ちのERPシステムで、まだ値が入力されていない数値日付が、ゼロまたは多分9999-99-99の様に表される値で、将来の日付でまだ未確定な日付を代表するものとしましょう。

DATE日付型は更なる柔軟性を提供しますから、その列をDATA型に変換したいと思うかも知れません。
これに対処するSQLは、次のようにまず内容が特別な値か検査するためにCASE式を用い、次いで実際の日付またはヌル値を含む有効なDATE列を作成するというものになるかも知れません。
0001-01-01または9999-12-31のような他の値は有効な日付であり、ユーザーに視覚的に「日付無し」または「将来の日付」であることを知らせるかも知れませんが、日付計算に使用された場合に大きな結果になるリスクがあります。
自社のサイトまたは環境に何が最善かを判断する必要があります。SQLは変換された列に無効な日付が入力される可能性を減らすでしょう。

図2では、XOSDAT（出荷日）というデータベース列は０または99999999である可能性があり、これらの値はそれぞれ未定義および将来日付であることを意味しています。
従って、CASE式はこれらの特殊値を検知し、それらをヌルまたは9999-12-31に設定します。
それが特殊値でない場合、SUBSTR関数が再形式化を行い、日付の各パーツをDATEキーワードで使用できる構文にまとめ上げます。
結果として出来上がるSHIPDT_REALDATE列は日付計算に使用出来たり、それを年、月などの構成パーツに分解したりできます。

最後のレコードの日付20210431はお気付きのように無効な日付です。
なぜなら4月は30日までしかないからです。したがって、変換された日付はヌルになります。
特異な日付を検査し、それらを修正するのは別な記事の話題かも知れませんが、日付を操作したり有効性を調べたりするのに役立つであろうTO_DATEやLAST_DAYのようなDb2 for iの日付関数について読んで研究することをお勧めします。

虎の巻6_2 図2

単一のWHEN文節で実行できるよりももっと多くのIF/THEN論理を要する処理ロジックがあるとしましょう。
複数のCASE式を一緒に使用できますし、あるいは必要であればそれらを入れ子状にすることもできます。
図3はStatus列に追加情報を提供するために、複数のCASE式を直列に使用したSQL文の出力データを示しています。
これについては、最初に結果を見てから図４のSQL文を再考察するのが役に立ちます。

虎の巻6_3 図3

虎の巻6_4 図4

何が起こっているかを知るために図4をバラバラにする必要があります。
ActiveおよびOpenという状況について、それらが何年その状況にあるのか確認し、その期間をテキストの一部としてリストするために、コードを意味のある記述に変換できるようにしたかったのです。
顧客を今年取引があった顧客とそれ以前にしか取引がなかった顧客とに区別したかったのです。

最初のCASE式の下に別のCASE式を入れ子にすることで、図のAとBで今年とそれ以前の年を区別できます。
図のCで、昨年以前の場合の情報を年数計算の結果と結合（||またはCONCAT）することで、その数値を作成した文字列に加えてStatusに対する最終結果を作ることができます。
最後に、図のDでは各WHEN文節で様々な計算と関数を使ったCASE式を使用し、2つの日付の関係を判別してTIME_SINCE_LAST_ACTIVITY列として結果を表示しています。

上記のCASE式のチュートリアルにはいくつかのでっち上げた例が含まれていますが、将来の報告書作りに役立つかもしれないフレーズの様々な実装を示しました。

報告書の例でCASE式を使用する

次に、実行時に渡されたパラメータに基づいて利用者に様々なデータを提供するために、SQLプロシージャ内でCASEロジックをどのように使用し得るかを考えます。
SQLプロシージャは、DECLAREで定義されハードコードされたSQL文または実行時にその場で構築されたSQL文を使用することができます。
実行時にその場で構築する利点は、柔軟性が増すと共に、渡されたパラメータに基づいて異なるレポートを作成することができるので、単一のレポートよりも利用者にとって実用的であることです。
例として、図5はYahoo Financeの個別株のウェブページのスナップショットを示しています。
このページには、株に関する情報に加え、長期的な運用実績専用のチャート欄があります。

虎の巻6_5 図5

差し当たり、そのページのチャート面だけを考えてみましょう。

図5aの左側の赤色は、ある1日の株価のチャートです。
そのパネルの上部にある薄緑色の網掛けの部分には、1D、5D、1Mなどのリンクがあります。最初は上部にある1Dが選択されているので、1日の運用実績が描かれています。
この場合、株が下がっているので赤で表示されています。

ヤフーのページでは、見たい期間をクリックすることでビューを変えられます。
1Dから5Dに切り替えると右の緑のチャートになり、5日間の上昇を表示しています。
この2つを見ることで、私達は単一日の画像とは異なる傾向を理解します。
この変化を可能にするテクニックは、私達の強みとして真似できるものかも知れません。

虎の巻6_5a 図5a

例えば、ビジネスアナリストが販売データや傾向を確認できるように、同様のアプリケーションを作成したいとします。
ヤフーのシナリオと同様に、目的は様々な期間と指定された期間のデータを要約することでしょう。
SQLはこれをどのようにして可能にするのでしょうか？

先に説明したCASE式と同様の構文を使ってSQLのロジックを開発し、異なるパラメータを渡すことで、様々な結果セットを返すSQL文を作成、実行することができるかもしれません。
そのような結果は、テキストやグラフの形で表示することができるかもしれません。
ヤフーの例と同様のチャートが私にとって最も魅力的なので、データを様々な形の結果に加工するストアドプロシージャによって駆動される、Db2 Web Query for iの単一の報告書定義からそのようなチャートを実行する例を示します。

以下の図6、7、8は、そのようなチャートの例です。
パラメータとしてPeriod（期）とDuration（期間）を使用することで、表示される結果が変化します。
可能な期としては、年、四半期、月、週、日を指定することができます。期間には1から90の数字が使えます。
横軸は期間、縦軸は収入額（ドル）、各期のプロットの丸印の大きさは利益額（ドル）を示しています。
ユーザーは、図のように時間軸を変更し、期毎に情報を抽出することができ、丸印の上にカーソルを置くとツールチップ・テキストが表示されます。

虎の巻6_6 図6：4四半期

虎の巻6_7 図7：6週間

虎の巻6_8 図8：6カ月

視点を調整するために実行中の臨機応変の変化を使用している上記の例は、SQLでCASE式を使用する1つの潜在的な利点を示しています。

様々な方法で結果セットに値を供給するプロシージャのコードを、下記のテキストに示します。

CREATE OR REPLACE PROCEDURE GetordSP (

    IN PPERIOD CHAR(8), 

    IN PDURATION INTEGER)

  DYNAMIC RESULT SETS 1

  LANGUAGE SQL

  SPECIFIC GETORDSP

  NOT DETERMINISTIC

  MODIFIES SQL DATA

  CALLED ON NULL INPUT


BEGIN

 DECLARE STMT1 CHAR(2000) DEFAULT ' ';

 DECLARE STMT2 CHAR(2000) DEFAULT

'select char(aaaa1,100) as Heading1, char(aaaa2,100) as Heading2, producttype, xxxx as period , SUM(quantity) AS TOTQTY, SUM(Linetotal) AS TOTRev, SUM(costofgoodssold) AS TOTCost, SUM(Linetotal-costofgoodssold) AS TOTProfit from qwqcent.orders o

 join qwqcent.inventory i on o.productnumber=i.productnumber WHERE shipdate BETWEEN bbbb and eeee GROUP BY  producttype, xxxx order by 1,2,3,4 '
;

 DECLARE STMT3 CHAR(2000);

 DECLARE STMT4 CHAR(2000);

 DECLARE RANGEBGN DATE DEFAULT '0001-01-01';

 DECLARE RANGEEND DATE DEFAULT '2099-12-31';

 DECLARE TEXTMSG CHAR(100);

 DECLARE DATERANGE CHAR(50);

 DECLARE C1 CURSOR FOR S1;

 CASE

  WHEN PPERIOD = 'YEARS' THEN 

    SET STMT1 = ' char( year(shipdate)) ';

    SET TEXTMSG = 'Shipments by Year for ' || TRIM(CHAR(PDURATION)) || ' years';

  WHEN PPERIOD = 'QUARTERS' THEN 

    SET STMT1 = ' char( year(shipdate) || ''-Q'' || quarter(shipdate) ) '  ;

    SET TEXTMSG = 'Shipments by Quarter for ' || TRIM(CHAR(PDURATION)) || ' quarters’;

  WHEN PPERIOD = 'MONTHS' THEN 

    SET STMT1 =  ' concat(year(shipdate) || ''-'' ,  substr(digits(month(shipdate)),9,2)) ‘;

    SET TEXTMSG = 'Shipments by Month for ' || TRIM(CHAR(PDURATION)) || ' months';

  WHEN PPERIOD = 'WEEKS' THEN 

    SET STMT1 =  ' char( concat(year(shipdate) || ''-'' ,  substr(digits(week(shipdate)),9,2))) ';

    SET TEXTMSG = 'Shipments by Week for ' || TRIM(CHAR(PDURATION)) || ' weeks';

  ELSE 

    SET STMT1 = ' char(shipdate) ';

    SET TEXTMSG = 'Shipments by Day for ' || TRIM(CHAR(PDURATION)) || ' days';

 END CASE;

-- 日付範囲の始まりと終わりを判別する

 SET RANGEEND = current date; 


-- 現在日付から逆算して期間を算定する

set pduration = pduration - 1;

  set  RANGEBGN = 

  case 

    when PPERIOD = 'YEARS'        then current date - PDURATION years

    when PPERIOD = 'QUARTERS'  then current date - (PDURATION*3) months

    when PPERIOD = 'MONTHS'     then current date - PDURATION months

    when PPERIOD = 'WEEKS'       then current date - (PDURATION*7) days

    when PPERIOD = 'DAYS'          then current date - PDURATION days

  end ;


 SET STMT3 = CHAR(REPLACE(STMT2, 'xxxx', TRIM(STMT1)), 2000);

 SET STMT4 = REPLACE(STMT3, 'eeee', '''' || CHAR(RANGEEND) || '''');

 SET STMT3 = STMT4;

 SET STMT4 = REPLACE(STMT3, 'bbbb', '''' || CHAR(RANGEBGN) || '''');

 SET STMT3 = STMT4;

 SET STMT4 = REPLACE(STMT3, 'aaaa1', '''' || trim(TEXTMSG) || '''');

 SET STMT3 = STMT4;

 SET DATERANGE = CHAR(RANGEBGN) || ' thru ' ||  CHAR(RANGEEND);

 SET STMT4 = REPLACE(STMT3, 'aaaa2', '''' || DATERANGE || ‘''');


PREPARE S1 FROM STMT4;

 OPEN C1;

 SET RESULT SETS CURSOR C1;

END;

（上図はコピー／ペースト用バージョン、下図はコードイメージ）

虎の巻6_9 図9

このコードで、赤字で強調した行はPERIODパラメータに応じて処理を行うSQL文を構築するために、どのようにCASE式が使われているかを示しています。
このパラメータの値がYEARSの場合に1つの処理、MONTHSの場合に別の処理を行うという具合になります。
期間の計算もまた、使用するデータの範囲を決定するのにCASE式の影響を受けます。
実行時に構築される文はSQLのREPLACE関数を使って基本的なSELECT文に埋め込まれます。

このテクニックは、他の報告書作成シナリオでも使える可能性があります。
こうしたやり方でCASE式に触れることは有益であり、これが報告書作成で使用するアイディアを提供することを願います。