IFファイルを作るときに、ヘッダー行、明細行、明細行…、ヘッダー行、明細行、明細行…のような作りのことがあります。
こんなファイルですね。
| ヘッダ | 項目A | 項目B |
| 明細 | 項目C | 項目D |
| 明細 | 項目C | 項目D |
| ヘッダ | 項目A | 項目B |
| 明細 | 項目C | 項目D |
| 明細 | 項目C | 項目D |
それをSQLで頑張って出せるようにしてみました。
備忘録を兼ねて紹介します。
デモンストレーションで動かした動画を載せておきます。
サンプルの発注・発注明細データ
扱うテーブルは以下のようなデータです。
発注テーブル
| 列名(英語) | 列名(日本語) | 備考 |
| BUYING_ID | 発注ID | PK |
| DENPYO_NO | 伝票NO | |
| HACHU_DATE | 発注日 | |
| MARCHANT_CD | 仕入先CD | |
| BUYING_COUNT | 発注合計数 | |
| ARRIVAL_LOCATION_CD | 入荷場所CD | |
| SHIPMENT_LOCATION_CD | 出荷場所CD | |
| SHIPPING_COMPANY_CD | 配送会社CD | |
| REGISTER_USERCD | 登録ユーザCD |
発注明細テーブル
| 列名(英語) | 列名(日本語) | 備考 |
| BUYING_ID | 発注ID | PK |
| BUYING_DETAIL_ID | 発注明細ID | PK |
| DENPYO_NO | 伝票NO | |
| DENPYO_ROW_NO | 伝票行NO | |
| SKU | SKU | |
| PURCHASE_PRICE | 仕入価格 | |
| BUYING_DEATIL_COUNT | 発注明細数 |
サンプルSQL
サンプルのSQLを見ていきましょう。
SQLの解説は下の方でします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
SELECT A.COL1 'ヘッダ/明細区分' ,A.COL2 '発注ID/発注ID' ,A.COL3 '伝票番号/発注明細CD' ,A.COL4 '発注日/伝票番号' ,A.COL5 '仕入先CD/伝票行番号' ,A.COL6 '合計発注数/SKU' ,A.COL7 '入荷倉庫CD/仕入価格（税抜）' ,A.COL8 '入荷倉庫名称/発注明細数' ,A.COL9 '出荷倉庫CD/' ,A.COL10 '出荷倉庫名称/' ,A.COL11 '運送会社CD/' ,A.COL12 '運送会社名称/' ,A.COL13 '登録ユーザCD/' FROM ( SELECT '01' AS 'COL1' ,CONVERT(VARCHAR(50),BUYING.BUYING_ID) AS 'COL2' ,CONVERT(VARCHAR(50),BUYING.DENPYO_NO) AS 'COL3' ,CONVERT(VARCHAR(50),BUYING.HACHU_DATE) AS 'COL4' ,CONVERT(VARCHAR(50),BUYING.MARCHANT_CD) AS 'COL5' ,CONVERT(VARCHAR(50),BUYING.BUYING_COUNT) AS 'COL6' ,CONVERT(VARCHAR(50),ARRIVAL_LOCATION_CD) AS 'COL7' ,CONVERT(VARCHAR(50),ARRIVAL_LOCATION_NAME) AS 'COL8' ,CONVERT(VARCHAR(50),SHIPMENT_LOCATION_CD) AS 'COL9' ,CONVERT(VARCHAR(50),SHIPMENT_LOCATION_NAME) AS 'COL10' ,CONVERT(VARCHAR(50),SHIPPING_COMPANY_CD) AS 'COL11' ,CONVERT(VARCHAR(50),SHIPPING_COMPANY_NAME) AS 'COL12' ,CONVERT(VARCHAR(50),REGISTER_USERCD) AS 'COL13' FROM BUYING UNION SELECT '02' AS 'COL1' ,CONVERT(VARCHAR(50),BUYING_DETAIL.BUYING_ID) AS 'COL2' ,CONVERT(VARCHAR(50),BUYING_DETAIL.BUYING_DETAIL_ID) AS 'COL3' ,CONVERT(VARCHAR(50),BUYING_DETAIL.DENPYO_NO) AS 'COL4' ,CONVERT(VARCHAR(50),BUYING_DETAIL.DENPYO_ROW_NO) AS 'COL5' ,CONVERT(VARCHAR(50),BUYING_DETAIL.SKU) AS 'COL6' ,CONVERT(VARCHAR(50),BUYING_DETAIL.PURCHASE_PRICE) AS 'COL7' ,CONVERT(VARCHAR(50),BUYING_DETAIL.BUYING_DEATIL_COUNT) AS 'COL8' ,NULL AS 'COL9' ,NULL AS 'COL10' ,NULL AS 'COL11' ,NULL AS 'COL12' ,NULL AS 'COL13' FROM BUYING_DETAIL ) A ORDER BY A.COL2 ,A.COL1 |
抽出結果
ちょっと見づらいですが、ヘッダー行と明細行で順番に抽出できています。
ヘッダーと明細行を1SQLにする考え方
どんなふうにヘッダー行と明細行を出していくかを書いておきます。
考え方としては以下の2ステップです。
①列数が違うテーブルをとりあえず一つのテーブルにする
②順番をヘッダー、明細、項目でソート
①列数が違うテーブルを一つのテーブルにする
そもそも列数が違うテーブルを1つの抽出結果にすることは難しいです。
なので、ひとまず、以下のように1つの抽出結果にします。
②順番をヘッダー、明細、項目でソート
一つにできれば、何となく形が見えてきますよね。
後は順番をヘッダー、明細、ヘッダー、明細と並べ替えるだけです。
並べ替えるとこんな感じになります。
SQLの解説
考え方の①と②に当てはめて解説していきます。
①列数が違うテーブルを一つのテーブルにする
18行目～49行目で発注と発注明細のテーブルを1つの抽出結果にしています。
UNIONでヘッダと明細を合せる
UNIONを使って1つの抽出結果にします。
ただし、発注明細の方が列数が少ないので、NULLを取得して列数を合せて抽出をしています。
UNION後の列の型を変換
そのままでは、ヘッダと明細で列の型が異なる値が入るため、エラーが出ます。
例えば、8列目です。
ヘッダーのARRIVAL_LOCATION_NAMEは文字型
明細のBUYING_DEATIL_COUNTは数値型
UNIONするとエラーになります。
そのため、CONVERT(VARCHAR(50),列名)を使って、型を合せるようにします。
ヘッダと明細区分を付ける
発注はヘッダ、発注明細は明細行なので、抽出するときに01と02で区分を付与しています。
この次にORDER BYでソートするために使います。
②順番をヘッダー、明細、項目でソート
17行目～49行目で元データは作成できました。
なので、そのテーブルをAとして抽出をしています。
この時、データの順番はそのままではだめなので、
[ヘッダ/明細]、[発注ID]、[発注明細ID]の順にORDER BYでソートしています。
ソート時はUNIONするテーブルの二つの同じ項目(列)がないとダメです。
COL1の18行目と36行目は同じ項目(列)の「ヘッダ/明細区分」です。
COL2の19行目と37行目も同じ項目(列)の「発注ID」です。
例えば、仮にCOL2が「発注ID」と「伝票番号」だとソートしたら、数値が若い順に並んでしまいます。
IFファイルとして出力する方法も書いているので良ければみて下さい。
