OracleSQLメモ_LEAD

今回の心

お客様からのEDIデータにより売上計上するバッチを動かしています。お客様がセットしている注文単価（会社内から見ると販売単価）と社内の販売単価マスタが合致しているか否かを調査するためのORACLEのSQL文となります。これも極私的メモ

テーブル　単価マスタ

仕事で使っている単価マスタは数量によって価格が変わる場合を考慮している。
略したレイアウトは以下の通りで数量LOTのカラムは1列です。

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には以下のデータを投入しました。

+-----+-----+-----+------+------+
|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としましたが、もう少しスマートな方法がありそうです。

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を使って作成したカラムです。ココが今回のミソ

+-----+-----+-----+--------+---------+------+
|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|
+-----+-----+-----+--------+---------+------+

 

テーブル　ジャーナル（売上）

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には以下のデータを投入しました。

+-----+-----+-----+------+------+
|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とマスタの比較を行うクエリ

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 

+--------+--------+----------+------+--------+---------+-----------+--------------+--------+
|得意先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値とマスタ値が違う数値となっています。

コメント