同じ条件に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と今回の代入成功を確認してから後続へ渡します。

