Db2 for iのSELECT INTO・FETCH FIRST・JOIN|件数と合計の確認手順

同じ条件に2件あるとエラーになるSQLと、先頭の1件を返すSQLがあります。どちらも画面には一つの値を返せますが、「本来1件であることを確認する」と「複数件から一つを選ぶ」は別の仕様です。

さらに、元表には重複がなくても、結合によって行が増えることがあります。SQLを直す前に、入力から最終結果まで、それぞれ何行になるべきかを決めます。

1件の意味を先に決める

要件 確認したいこと
必ず一つの登録を参照する 一意性を担保するキー・制約・状態
履歴から最新を選ぶ 日付の意味、同時刻の優先順位
合計を求める 集計の単位と結合前の行数
全明細を処理する 先頭1件で打ち切っていないか

IBMのSELECT INTOの説明では、結果が空ならSQLSTATEは02000となり、変数への代入は行われません。複数行なら21000のエラーになります。返却値だけで成功を判定しないことが重要です。IBM:SELECT INTO

FETCH FIRSTを付けるだけでは、一意にならない

例えば、架空の料金履歴に同じ商品・同じ開始日が2行あり、値だけが違うとします。「エラーを消すために1件へ制限する」と、データの不整合を見えなくしてしまいます。

履歴から最新を選ぶ仕様なら、対象日以前という条件、日付の降順、同順位の場合の扱いまで決めます。並び順で区別できない行が残るなら、どちらを採用するかを仕様として確定する必要があります。最新という言葉だけでは、選択規則を説明できません。

一方、複数件が不正なら、制限する前に件数を調べ、登録経路や一意性の定義を直します。取得件数と順序の関係は、IBM:fetch-clauseでも説明されています。

最新の行と、最大の値も別

架空のセンサー履歴で、12時の測定値が35、13時が0だったとします。最新の値を求めるなら13時の0ですが、値のMAXを取ると35になります。0を空とみなして除外した場合も、古い35へ戻ってしまいます。

最新を決める時刻と、返したい測定値を分けます。同時刻の行、最新値が0・負数・NULLの場合、履歴がない場合を試します。何を返すべきかは対象の仕様で決め、値の大小を時系列の代わりにしません。

JOINで2行が6行になる例

次は自作の例です。

対象 キーXの行
作業明細 数量10と20の2行
補足情報 種類A・B・Cの3行

キーXだけで両者を結合すると、各明細が3回現れて6行になります。そこで数量を合計すれば90になり、元明細の合計30とは違います。GROUP BYを書いたことだけでは増幅を防げません。

IBMも結合条件の適切な指定を案内しています。IBM:Join performance tips この記事の例では、性能より先に、行の対応関係を確認します。

DISTINCTやMAXで隠す前に調べる

結合前後の件数、キー別の件数、合計を並べます。補足情報が複数あってよいなら、用途に合う行を選ぶ、必要な単位へ事前集約する、存在確認だけにする、といった設計を選びます。

DISTINCTは結果列の重複を除くもので、欠けた結合条件を復元する機能ではありません。MAXで名前や値を一つ選ぶ場合も、どれを選んでもよい根拠が必要です。

0件・1件・複数件を試す

通常の1件だけでなく、該当なし、意図しない重複、正当な履歴複数、同順位、結合先なしを用意します。状態と値を組にして判定し、失敗後の変数をそのまま使わないようにします。

移行データの照合でも、キーを一意にしてから比較する点は共通です。先頭1件が取れることと、正しい1件を選べたことを分けて検証してください。

業務表を使わず、JOIN増幅を追うSQL例

以下は自作の固定値だけを使うDb2 for i向け説明例です。IBM i実機では未実行で、表作成・データ更新はしません。実行する場合もSQL利用を許可された環境で、対応する構文を確認してください。

WITH detail (group_id, line_no, qty) AS (
    VALUES ('X', 1, 10), ('X', 2, 20)
), supplement (group_id, kind) AS (
    VALUES ('X', 'A'), ('X', 'B'), ('X', 'C')
)
SELECT COUNT(*) AS joined_rows, SUM(d.qty) AS joined_qty
  FROM detail d
  JOIN supplement s ON s.group_id = d.group_id;

期待結果はjoined_rowsが6、joined_qtyが90です。元明細の数量合計30と違いますが、SQLが壊れたのではなく、各明細が補足3行へ対応する結合条件になっています。

必要なのが「補足が存在する明細だけを集計すること」なら、同じWITH句を使い、SELECT以降を次の形に置き換える設計が考えられます。

SELECT COUNT(*) AS detail_rows, SUM(d.qty) AS detail_qty
  FROM detail d
 WHERE EXISTS (
       SELECT 1 FROM supplement s
        WHERE s.group_id = d.group_id
 );

この要件での期待結果は2行分・数量30です。補足情報の列そのものが必要な要件なら、この書換えでは満たせません。目的を変えて集計値だけ合わせないでください。IBMの表式の説明も構文確認に使えます。

先頭1件も、わざと同順位にして確かめる

架空の測定履歴を「時刻12:00・連番1・値35」「13:00・連番2・値0」「13:00・連番3・値-2」とします。「時刻が最新、同時刻なら連番最大」という仕様なら期待値は-2です。MAX(測定値)の35でも、0を除いて選ぶことでもありません。

0件、同順位、結合先がないケースでは、何を返すかを先に決めます。SELECT INTOが失敗した経路でホスト変数に値が見えても、それを成功した値として採用しません。SQLSTATEと今回の代入成功を確認してから後続へ渡します。

関連する確認手順