使用full outer join 分析,校驗數據

   在erp系統中, 當使用者輸入一筆單據, 經常會需要處理到很多不同的Table.
  如一筆收貨數據,會回饋採購檔已收貨量,入收貨檔,入庫存檔,庫存累計檔.等等.

各個帳目間的數據是否一致?
   1.用量檔中已採購量總量和多次採購累計,
   2.已同一採購單的多次收貨量和該單的已收量.
   3.當日收貨和當日庫存量是否一致.
   4.收貨量和付款量數據
   5.出庫量和派工量.
   6.是否有程序bug,資料寫入部份寫入錯誤.
   ......

   可用匯總比較兩邊數據是否一致作為一種校驗手段.
   這時候用left join或right join就不合適了, 可能會存在左邊有,右邊沒有, 或左邊沒有右邊有的情況.
   需要用到full outer join 來關聯.

場景1. 庫存檔的主檔和出入庫明細按料號匯總比校.
有差異就表示數據出現問了.需要請赶緊分析問題,修正bug, 調整數據.

with aa as (
SELECT fact_no, SUBSTR(check_date,1,6) "YYMM", mat_no,
           NVL(SUM(check_qty),0) + NVL(SUM(overtop_qty),0) in_qty,
           NVL(SUM(shoot_qty),0) shoot_qty
    FROM y3_matkinm
    WHERE check_date >=to_char(sysdate-7,'YYYYMM')
    GROUP BY fact_no, SUBSTR(check_date,1,6), mat_no
  ) ,
bb AS (
    SELECT fact_no, yymm, mat_no,
           SUM(NVL(in_stock,0)) in_stock,
           SUM(NVL(out_stock,0)) out_stock
    FROM y3_ymkstock
    WHERE yymm >=to_char(sysdate-7,'YYYYMM')
    GROUP BY fact_no, yymm, mat_no
)

SELECT yymm, fact_no, mat_no,
        aa.in_qty, bb.in_stock, NVL(aa.in_qty,0) - NVL(bb.in_stock,0) in_cy,
        aa.shoot_qty, bb.out_stock, NVL(aa.shoot_qty,0) - NVL(bb.out_stock,0) out_cy
    FROM aa
    FULL OUTER JOIN bb
    USING ( fact_no, yymm, mat_no )
 WHERE ( NVL(aa.in_qty, 0) <> NVL(bb.in_stock,0) OR NVL(aa.shoot_qty, 0) <> NVL(bb.out_stock,0))

阿飛
2015/04/29