안녕하세요. left outer 조인을 이용해서 아이템의 수불현황을 나타내는 쿼리를 구루비형님들의 도움으로 짯습니다. SELECT 0 id , a.occr_date , a.item_code , a.item_desc , NVL(b.st_qty, 0) A, NVL(e.acpt_qty_yesterday, 0) B, NVL(f.iss_qty_yesterday , 0) C, NVL(b.st_qty, 0) + NVL(e.acpt_qty_yesterday, 0) - NVL(f.iss_qty_yesterday , 0) AS inv_qty_yesterday, NVL(e.acpt_qty_today , 0) AS acpt_qty_today , NVL(f.iss_qty_today , 0) AS iss_qty_today , NVL(b.st_qty, 0) + NVL(e.acpt_qty_yesterday, 0) - NVL(f.iss_qty_yesterday , 0) + NVL(e.acpt_qty_today , 0) - NVL(f.iss_qty_today , 0) AS inv_qty_today, a.inv_qty , a.awat_qty , a.rsv_qty , NVL(c.iss_qty , 0) AS iss_qty , NVL(d.acpt_qty , 0) AS acpt_qty , a.maj_grp_code FROM (SELECT SUBSTR(occr_date, 1, 6) AS sttl_mnth , b.occr_date , a.item_code , a.item_desc , a.inv_qty , a.awat_qty , a.rsv_qty , a.maj_grp_code , DECODE(a.prch_gubun, 'O', 'D','I','D', a.prch_gubun) AS maj_ord_type FROM hm.gl001m a, hm.pp001c b WHERE b.occr_date BETWEEN '20190301' AND '20190331' AND a.maj_grp_code LIKE '%' ) a LEFT OUTER JOIN hm.gl010m b ON a.sttl_mnth = b.sttl_mnth AND a.maj_ord_type = b.maj_ord_type AND a.item_code = b.item_code LEFT OUTER JOIN hm.mm036m c ON a.occr_date = c.iss_date AND a.item_code = c.item_code AND EXISTS (SELECT 1 FROM hm.pp031m x, hm.gl001m y WHERE x.item_code = y.item_code AND x.pre_item_code = y.item_code AND y.prch_gubun = 'O' ) LEFT OUTER JOIN hm.mm036m d ON a.occr_date = d.acpt_date AND a.item_code = d.item_code AND EXISTS (SELECT 1 FROM hm.pp031m x, hm.gl001m y WHERE x.item_code = d.item_code AND x.pre_item_code = y.item_code AND y.prch_gubun = 'O' ) FULL OUTER JOIN (SELECT sttl_mnth, maj_ord_type, item_code, crnt_date, SUM(acpt_qty) acpt_qty_today, SUM(SUM(acpt_qty)) over(partition BY sttl_mnth, maj_ord_type,item_code, SUBSTR(crnt_date, 1, 6) order by crnt_date) - SUM(acpt_qty) acpt_qty_yesterday FROM gl011m WHERE sttl_mnth BETWEEN SUBSTR('20190301', 1, 6) AND SUBSTR('20190331', 1, 6) AND crnt_date BETWEEN SUBSTR('20190301', 1, 6) AND '20190331' GROUP BY sttl_mnth, maj_ord_type, item_code, crnt_date ) e ON a.sttl_mnth = e.sttl_mnth AND a.maj_ord_type = e.maj_ord_type AND a.item_code = e.item_code AND a.occr_date = e.crnt_date LEFT OUTER JOIN (SELECT sttl_mnth, maj_ord_type, item_code, crnt_date , SUM(iss_qty) iss_qty_today , SUM(SUM(iss_qty)) over (partition BY sttl_mnth, item_code, SUBSTR(crnt_date, 1, 6) order by crnt_date) - SUM(iss_qty) iss_qty_yesterday FROM gl012m WHERE sttl_mnth BETWEEN SUBSTR('20190301' , 1, 6) AND SUBSTR('20190331',1,6) AND crnt_date BETWEEN '20190301' AND '20190331' GROUP BY sttl_mnth, maj_ord_type, item_code, crnt_date ) f ON a.sttl_mnth = f.sttl_mnth AND a.maj_ord_type = f.maj_ord_type AND a.occr_date = f.crnt_date AND a.item_code = f.item_code WHERE 1 =1 AND 0 != ANY(NVL(e.acpt_qty_today,0), NVL(f.iss_qty_today,0),NVL(c.iss_qty,0), NVL(d.acpt_qty,0)) AND a.item_code = '3RLGP11' ORDER BY item_desc ASC, occr_date ASC;
아래의 DB표를 포시면 B열의 계산식이 서로 틀린데...
해본시도
1. LAG를 써서 해결하려 했지만 다음행만 반영되서 실패
2. b열값의 뽑아내는 left 조인이 잘못된것 같아서 수정중...
2일째 매달려도 B열값을 재대로 반영을 못시키겠네요...
어디가 잘못된건지 포인트만 잡아주셔도 대단히 감사드리겠습니다
감사합니다.!
-- 이부분에서 SUM(acpt_qty) 가 NULL 이 나오는 듯 하네요 -- , SUM(SUM(acpt_qty)) OVER( PARTITION BY sttl_mnth, maj_ord_type,item_code, SUBSTR(crnt_date, 1, 6) ORDER BY crnt_date) - SUM(acpt_qty) acpt_qty_yesterday -- 개선 -- , SUM(SUM(acpt_qty)) OVER( PARTITION BY sttl_mnth, maj_ord_type,item_code, SUBSTR(crnt_date, 1, 6) ORDER BY crnt_date) - NVL(SUM(acpt_qty), 0) acpt_qty_yesterday
다시 생각해 보니 인라인뷰 안에서 NULL 발생하는 문제가 아니라
입고날짜 자체가 비는게 문제네요.
아우터 조인으로 빈행이 조인되니 안나오는 거네요.
인라인뷰 안에서 acpt_qty_yesterday 를 구하면 안되겠네요.
아우터 조인 후에 누적합계를 이용해 구해야 하겠네요.
WITH t AS ( SELECT '20190304' occr_date, 10080 acpt_qty_today FROM dual UNION ALL SELECT '20190305', 8000 FROM dual UNION ALL SELECT '20190306', 0 FROM dual UNION ALL SELECT '20190307', 0 FROM dual UNION ALL SELECT '20190308', 0 FROM dual UNION ALL SELECT '20190311', 7560 FROM dual UNION ALL SELECT '20190312', 0 FROM dual UNION ALL SELECT '20190313', 0 FROM dual UNION ALL SELECT '20190314', 10080 FROM dual ) SELECT occr_date , acpt_qty_today , SUM(acpt_qty_today) OVER(ORDER BY occr_date) - acpt_qty_today b FROM t ;