2017.06.22
Ted Holt 著

不一致データを管理する 3つの方法

あってはならないことですが、私なら失敗したRPG CHAIN (ランダム読み取り) 命令を無視します。私はいつも正しく操作しています。行う操作は状況に応じて変わります。同じことが、2 次テーブルで一致するデータが見つからない外部結合にも当てはまります。SQL を使って外部結合の不一致データを扱うには 3つの方法を紹介します。

説明するために、非常に単純化した総勘定元帳システムから 3つのテーブルを使いましょう。最初のテーブルは、業務を部門に分けています。2 番目は勘定科目です。3 番目は総勘定元帳にデータを提供するトランザクション・ファイルです。テーブルはそれぞれ以下のようになります。

技術情報code01
ID NAME
1 Accounting
2 Sales
3 Shipping
4 Receiving
技術情報code02
ID NAME
1000 Cash in bank
1010 Petty cash
1020 Accounts receivable
2000 Accounts payable
2010 Notes payable
3000 Owner’s equity
4000 Sales
5000 Salaries
5010 Employee benefits
5020 Taxes
5030 Office expenses
技術情報code03
ID DATE DEPT ACCOUNT AMOUNT
1 2017/04/24 1 5030 100.00
2 2017/04/24 3 5030 150.00
3 2017/04/24 5 5090 300.00
4 2017/04/24 2 5030 200.00
5 2017/04/24 1 5000 1,000.00
6 2017/04/24 2 5000 2,000.00
7 2017/04/24 3 5000 1,500.00
8 2017/04/24 4 5000 1,250.00

トランザクション 3 の部門 ID と口座番号が、部門と勘定科目のテーブルのいずれの行とも一致していない点に注目してください。

方法 1: NULL 値による処理

デフォルトでは、データベース・マネージャーは、行が 1 次テーブルの行と一致する行がない場合、2 次テーブルの列に NULL 値を返します。そのクエリーは以下のようになります。

技術情報code03
ID DATE DEPT DEPARTMENT ACCOUNT ACCOUNT AMOUNT
1 2017/04/24 1 Accounting 5030 Office expenses 100.00
2 2017/04/24 3 Shipping 5030 Office expenses 150.00
3 2017/04/24 5 null 5090 null 300.00
4 2017/04/24 2 Sales 5030 Office expenses 200.00
5 2017/04/24 1 Accounting 5000 Salaries 1,000.00
6 2017/04/24 2 Sales 5000 Salaries 2,000.00
7 2017/04/24 3 Shipping 5000 Salaries 1,500.00
8 2017/04/24 4 Receiving 5000 Salaries 1,250.00

部門名と勘定名は、結果セットの行 3 について NULL です。これには全く問題はありません。DEPARTMENT 列と ACCOUNT 列に NULL 値がある可能性があると考え、それにしたがってプログラムする必要があります。RPG プログラムでは 5 桁の整数である、NULL 標識変数を使う必要があります。負の値は、対応する列が NULL であることを意味します。

方法 2: デフォルト値を入力する

NULL 値の扱いは厄介です。COALESCE、VALUE、IFNULL の各関数を使って NULL 値を NULL 以外の値に変換できます。これら 3つの関数は引数 (パラメーター) のリストで最初の NULL 以外の値を返します。すべての引数が NULL の場合、これらの関数は NULL 値を返します。私なら 3つのうちCOALESCE 関数を使います。なぜなら、3つの中で最も標準的で、3つ以上の引数を許可できるためです。

技術情報code04
ID DATE DEPT DEPARTMENT ACCOUNT ACCOUNT AMOUNT
1 2017/04/24 1 Accounting 5030 Office expenses 100.00
2 2017/04/24 3 Shipping 5030 Office expenses 150.00
3 2017/04/24 5 **INVALID** 5090 **INVALID** 300.00
4 2017/04/24 2 Sales 5030 Office expenses 200.00
5 2017/04/24 1 Accounting 5000 Salaries 1,000.00
6 2017/04/24 2 Sales 5000 Salaries 2,000.00
7 2017/04/24 3 Shipping 5000 Salaries 1,500.00
8 2017/04/24 4 Receiving 5000 Salaries 1,250.00

方法 3: エラーを上げる

不一致データが見つかった場合は、クエリーを取り消すようクエリーに命令できます。結局、結果セットが使用できない場合は、なぜ多数の行を返すのでしょうか。

技術情報code05

RAISE_ERROR 関数は SQL ステートメントを強制終了させます。返す SQL 状態と、エラーを記述するメッセージ・テキストという 2つの引数があります。この関数を 2 番目の引数として COALESCE 関数に配置しました。トランザクション・テーブルの部門列が、部門テーブルの 1つ以上の行の部門 ID と一致する場合、結果セットには部門名が含まれます。そうでない場合は、データベース・マネージャーは、 SQL 状態値 88001 でクエリーを取り消します。一致していない口座番号は、 SQL 状態 88002 で取り消されます。それらの場合の SQL コード値は -438 です。ジョブ・ログにメッセージ CPF503E が表示されます。

技術情報code06

そして SQL0438 が表示されます。

技術情報code07

これらはいずれも不一致データを処理する優れた方法です。選択肢があることはいいことです。

ページトップ

ボタン