今回の心
お客様からのEDIデータにより売上計上するバッチを動かしています。お客様がセットしている注文単価（会社内から見ると販売単価）と社内の販売単価マスタが合致しているか否かを調査するためのORACLEのSQL文となります。これも極私的メモ
テーブル 単価マスタ
仕事で使っている単価マスタは数量によって価格が変わる場合を考慮している。
略したレイアウトは以下の通りで数量LOTのカラムは1列です。
|
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 |
CREATE TABLE TIGER.TEST_TOKTN ( TOKCD NUMBER(4,0) NOT NULL, SIRCD NUMBER(4,0) NOT NULL, SYOCD VARCHAR2(23) NOT NULL, SUULOT NUMBER(9,0) NOT NULL, HANTAN NUMBER(9,2) NOT NULL, CONSTRAINT "PK_TEST-TOKTN" PRIMARY KEY (TOKCD, SIRCD, SYOCD, SUULOT) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE FUJIWA STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT) LOGGING ENABLE ) PCTFREE 10 MAXTRANS 255 TABLESPACE FUJIWA STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT) NOCACHE LOGGING / COMMENT ON TABLE TIGER.TEST_TOKTN IS 'テスト販売単価マスタ' / COMMENT ON COLUMN TIGER.TEST_TOKTN.TOKCD IS '得意先CD' / COMMENT ON COLUMN TIGER.TEST_TOKTN.SIRCD IS '仕入先CD' / COMMENT ON COLUMN TIGER.TEST_TOKTN.SYOCD IS 'メーカ品番' / COMMENT ON COLUMN TIGER.TEST_TOKTN.SUULOT IS '数量LOT' / COMMENT ON COLUMN TIGER.TEST_TOKTN.HANTAN IS '販売単価' |
テストとしてTEST_TOKTNには以下のデータを投入しました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
+-----+-----+-----+------+------+ |TOKCD|SIRCD|SYOCD|SUULOT|HANTAN| +-----+-----+-----+------+------+ | 5000| 3000|C | 0| 100| +-----+-----+-----+------+------+ | 5000| 1000|A | 0| 100| +-----+-----+-----+------+------+ | 5000| 1000|A | 500| 10| +-----+-----+-----+------+------+ | 5000| 1000|A | 1000| 5| +-----+-----+-----+------+------+ | 5000| 2000|B | 1| 200| +-----+-----+-----+------+------+ | 5000| 2000|B | 10| 20| +-----+-----+-----+------+------+ | 5000| 2000|B | 100| 10| +-----+-----+-----+------+------+ |
LEADを用いたクエリ
BETWEENで照合させたいためORACLEの分析関数LEADを使ってみます。
Part 11：分析関数:ランキング、LEAD/LAG、レポート | Oracle 日本
Part 11：分析関数:ランキング、LEAD/LAG、レポート
www.oracle.com
より
現在処理中の行の前（LEAD）または後（LAG）にある行に関するレポートを生成する
後の問合せでBETWEENが使えるようにLEADをつかって単価表のクエリを作成しました。実在しない最大の数999999999をMAXとしましたが、もう少しスマートな方法がありそうです。
|
1 2 3 4 5 6 7 8 9 |
SELECT TOKCD ,SIRCD ,SYOCD ,SUULOT SUULOT_1 ,LEAD(SUULOT,1,999999999) OVER (PARTITION BY TOKCD,SIRCD,SYOCD ORDER BY SUULOT) -1 SUULOT_2 ,HANTAN FROM TEST_TOKTN |
SUULOT_2はLEADを使って作成したカラムです。ココが今回のミソ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
+-----+-----+-----+--------+---------+------+ |TOKCD|SIRCD|SYOCD|SUULOT_1|SUULOT_2 |HANTAN| +-----+-----+-----+--------+---------+------+ | 5000| 1000|A | 0| 499| 100| +-----+-----+-----+--------+---------+------+ | 5000| 1000|A | 500| 999| 10| +-----+-----+-----+--------+---------+------+ | 5000| 1000|A | 1000|999999998| 5| +-----+-----+-----+--------+---------+------+ | 5000| 2000|B | 1| 9| 200| +-----+-----+-----+--------+---------+------+ | 5000| 2000|B | 10| 99| 20| +-----+-----+-----+--------+---------+------+ | 5000| 2000|B | 100|999999998| 10| +-----+-----+-----+--------+---------+------+ | 5000| 3000|C | 0|999999998| 100| +-----+-----+-----+--------+---------+------+ |
テーブル ジャーナル（売上）
|
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 |
CREATE TABLE TIGER.TEST_URIKE ( TOKCD NUMBER(4,0) NOT NULL, SIRCD NUMBER(4,0) NOT NULL, SYOCD VARCHAR2(23) NOT NULL, JYUSUU NUMBER(9,0) NOT NULL, HTANKA NUMBER(9,2) NOT NULL ) PCTFREE 10 MAXTRANS 255 TABLESPACE FUJIWA STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT) NOCACHE LOGGING / COMMENT ON TABLE TIGER.TEST_URIKE IS 'テスト売上計上' / COMMENT ON COLUMN TIGER.TEST_URIKE.TOKCD IS '得意先CD' / COMMENT ON COLUMN TIGER.TEST_URIKE.SIRCD IS '仕入先CD' / COMMENT ON COLUMN TIGER.TEST_URIKE.SYOCD IS 'メーカ品番' / COMMENT ON COLUMN TIGER.TEST_URIKE.JYUSUU IS '受注数量' / COMMENT ON COLUMN TIGER.TEST_URIKE.HTANKA IS '販売単価' / |
テストとしてTEST_URIKEには以下のデータを投入しました。
|
1 2 3 4 5 6 7 8 9 10 11 |
+-----+-----+-----+------+------+ |TOKCD|SIRCD|SYOCD|JYUSUU|HTANKA| +-----+-----+-----+------+------+ | 5000| 1000|A | 5| 200| +-----+-----+-----+------+------+ | 5000| 1000|A | 100| 5| +-----+-----+-----+------+------+ | 5000| 2000|B | 500| 10| +-----+-----+-----+------+------+ | 5000| 2000|B | 50| 15| +-----+-----+-----+------+------+ |
JNLとマスタの比較を行うクエリ
|
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 |
SELECT U.TOKCD 得意先CD ,U.SIRCD 仕入先CD ,U.SYOCD メーカ品番 ,U.JYUSUU 受注数 ,M.SUULOT_1 ,M.SUULOT_2 ,U.HTANKA JNL販売単価 ,M.HANTAN マスタ販売単価 ,CASE WHEN U.HTANKA = M.HANTAN THEN '◯' ELSE '×' END 単価判定 FROM TEST_URIKE U INNER JOIN ( SELECT TOKCD ,SIRCD ,SYOCD ,SUULOT SUULOT_1 ,LEAD(SUULOT,1,999999999) OVER (PARTITION BY TOKCD,SIRCD,SYOCD ORDER BY SUULOT) -1 SUULOT_2 ,HANTAN FROM TEST_TOKTN ) M ON U.TOKCD = M.TOKCD AND U.SIRCD = M.SIRCD AND U.SYOCD=M.SYOCD WHERE 1=1 --U.SYOCD = 'A' AND U.JYUSUU = 5 AND U.JYUSUU >= SUULOT_1 AND U.JYUSUU <= SUULOT_2 |
|
1 2 3 4 5 6 7 8 9 10 11 |
+--------+--------+----------+------+--------+---------+-----------+--------------+--------+ |得意先CD|仕入先CD|メーカ品番|受注数|SUULOT_1|SUULOT_2 |JNL販売単価|マスタ販売単価|単価判定| +--------+--------+----------+------+--------+---------+-----------+--------------+--------+ | 5000| 1000|A | 100| 0| 499| 5| 100|× | +--------+--------+----------+------+--------+---------+-----------+--------------+--------+ | 5000| 1000|A | 5| 0| 499| 200| 100|× | +--------+--------+----------+------+--------+---------+-----------+--------------+--------+ | 5000| 2000|B | 50| 10| 99| 15| 20|× | +--------+--------+----------+------+--------+---------+-----------+--------------+--------+ | 5000| 2000|B | 500| 100|999999998| 10| 10|◯ | +--------+--------+----------+------+--------+---------+-----------+--------------+--------+ |
右端の判定が×はJNL値とマスタ値が違う数値となっています。
コメント