데이터베이스 질문과 답변
안녕하세요. 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열값을 재대로 반영을 못시키겠네요...
어디가 잘못된건지 포인트만 잡아주셔도 대단히 감사드리겠습니다
감사합니다.!
by 마농
[2019.04.18 16:58:16]
-- 이부분에서 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
by 마농
[2019.04.18 17:12:00]
다시 생각해 보니 인라인뷰 안에서 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
;
by 치비
[2019.04.18 17:41:50]
정말 감사합니다.
PARTIOTION BY 에대한 개념이 부족했네요...
날짜와 아이템코드값을 기준으으로 PARTITION BY 문을 이요해서.
sum(NVL(e.acpt_qty_today, 0)) over(partition by a.item_code order by a.occr_date) - nvl(e.acpt_qty_today,0) 요렇게
처리해니까 정상적으로 출력되었습니다 정말감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.