不一致データを管理する 3つの方法
あってはならないことですが、私なら失敗したRPG CHAIN (ランダム読み取り) 命令を無視します。私はいつも正しく操作しています。行う操作は状況に応じて変わります。同じことが、2 次テーブルで一致するデータが見つからない外部結合にも当てはまります。SQL を使って外部結合の不一致データを扱うには 3つの方法を紹介します。
説明するために、非常に単純化した総勘定元帳システムから 3つのテーブルを使いましょう。最初のテーブルは、業務を部門に分けています。2 番目は勘定科目です。3 番目は総勘定元帳にデータを提供するトランザクション・ファイルです。テーブルはそれぞれ以下のようになります。
| ID | NAME |
|---|---|
| 1 | Accounting |
| 2 | Sales |
| 3 | Shipping |
| 4 | Receiving |
| 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 |
| 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 値を返します。そのクエリーは以下のようになります。
| 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つ以上の引数を許可できるためです。
| 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: エラーを上げる
不一致データが見つかった場合は、クエリーを取り消すようクエリーに命令できます。結局、結果セットが使用できない場合は、なぜ多数の行を返すのでしょうか。
RAISE_ERROR 関数は SQL ステートメントを強制終了させます。返す SQL 状態と、エラーを記述するメッセージ・テキストという 2つの引数があります。この関数を 2 番目の引数として COALESCE 関数に配置しました。トランザクション・テーブルの部門列が、部門テーブルの 1つ以上の行の部門 ID と一致する場合、結果セットには部門名が含まれます。そうでない場合は、データベース・マネージャーは、 SQL 状態値 88001 でクエリーを取り消します。一致していない口座番号は、 SQL 状態 88002 で取り消されます。それらの場合の SQL コード値は -438 です。ジョブ・ログにメッセージ CPF503E が表示されます。
そして SQL0438 が表示されます。
これらはいずれも不一致データを処理する優れた方法です。選択肢があることはいいことです。
