SELECT 2019
,6
,SDAN8
,SDDGL
,SDDOCO
,SDDCTO
,SDLITM
,(SELECT IMITM FROM F4101 WHERE SDLITM = IMLITM)
,NVL(SUM(CASE WHEN SDDCTO IN ('CO','C5') THEN SDSOQS * -1 ELSE SDSOQS END), 0) SDSOQS
,NVL(SUM(CASE WHEN SDDCTO IN ('CO','C5') THEN (SDAEXP + SAVAT) * -1 ELSE SDAEXP + SAVAT END), 0) SDAEXP
,0
,0
,N' '
,0
,'TEST2'
,'TEST1'
,1
,1
,'TEST'
FROM
(
SELECT SDAN8
,SDDGL
,SDDOCO
,SDDCTO
,SDLITM
,SDSOQS
,SDAEXP
,(CASE WHEN TRIM(SDEXR1) IN ('V','V1','V5') AND SDTAX1 = 'Y' AND SUBSTR(SDTXA1,3,2) IN ('10') THEN SDOPPID ELSE 0 END) SAVAT
FROM F4211
,F57KK99
WHERE ZZUSD1 = 119181
AND ZZYRA = 2019
AND ZZPN = 6
AND SDCNDJ = 0
)
GROUP BY SDAN8
,SDDGL
,SDDOCO
,SDDCTO
,SDLITM;
Q.위와 같은 쿼리에 총 레코드 수를 알고 싶은데 어떻게 해야할까요 ..?
SELECT MNAN8
,(NVL((SELECT SUM(LHF7A) FROM F57S0051 WHERE LHAN8 = MNAN8 AND LH57MALGU = '1') ,0) +
NVL((SELECT SUM(MHF1A) FROM F57S0054 WHERE MHAN8 = MNAN8 AND MH57MALSO = '1') ,0) +
NVL((SELECT SUM(EIF3A) FROM F57S0056 WHERE EIAN8 = MNAN8 AND EI57MALGU = '1' AND EI57DAMGU IN ('1','2')), 0)) * 100 SBFCAMT01 -- 담보설정액
,(NVL((SELECT SUM(LHF5A) FROM F57S0051 WHERE LHAN8 = MNAN8 AND LH57MALGU = '1') ,0) +
NVL((SELECT SUM(MHF2A) FROM F57S0054 WHERE MHAN8 = MNAN8 AND MH57MALSO = '1') ,0) +
NVL((SELECT SUM(EIF1A) FROM F57S0056 WHERE EIAN8 = MNAN8 AND EI57MALGU = '1' AND EI57DAMGU IN ('1','2')), 0)) * 100 SBFCAMT02 -- 여신한도(실담보력)
,NVL((SELECT SUM(VTAA - VTAG)
FROM F59C947V
,F0901
WHERE VTAID = GMAID
AND ((GMOBJ = '10201' AND GMSUB IN ('01','02','11','14')) -- 01,02상품대 11:어음 14:부도
OR (GMOBJ = '10211' AND GMSUB IN ('02','21','31','41'))) -- 02:사용료,21:공병,31:상자,41:팔레트,61:카드 (,'61' 제외 20190410 이재욱D)
AND VTAN8 = MNAN8
AND VTCFY = PV_CFY
AND VTPNZ = PV_PN), 0) * 100 SBFCAMT04 -- 총 채권
,NVL((SELECT SUM(VTAG - VTAA)
FROM F59C947V
,F0901
WHERE VTAID = GMAID
AND GMOBJ IN ('20601')
AND GMSUB IN ('01')
AND VTAN8 = MNAN8
AND VTCFY = PV_CFY
AND VTPNZ = PV_PN), 0) * 100 SBFCAMT06 -- 선수금
,NVL((SELECT SUM(VTAA - VTAG)
FROM F59C947V
,F0901
WHERE VTAID = GMAID
AND GMOBJ IN ('10201')
AND GMSUB IN ('01','02')
AND VTAN8 = MNAN8
AND VTCFY = PV_CFY
AND VTPNZ = PV_PN), 0) * 100 SBFCAMT07 -- 외상매출금
,NVL((SELECT SUM(VTAA - VTAG)
FROM F59C947V
,F0901
WHERE VTAID = GMAID
AND GMOBJ = '10201'
AND GMSUB = '11'
AND VTAN8 = MNAN8
AND VTCFY = PV_CFY
AND VTPNZ = PV_PN), 0) * 100 SBFCAMT08 -- 미결어음
,NVL((SELECT SUM(VTAA - VTAG)
FROM F59C947V
,F0901
WHERE VTAID = GMAID
AND GMOBJ IN ('10211')
AND GMSUB IN ('02')
AND VTAN8 = MNAN8
AND VTCFY = PV_CFY
AND VTPNZ = PV_PN), 0) * 100 SBFCAMT09 -- 냉온수기 사용료
,NVL((SELECT SUM(VTAA - VTAG)
FROM F59C947V
,F0901
WHERE VTAID = GMAID
AND GMOBJ = '10211'
AND GMSUB = '21'
AND VTAN8 = MNAN8
AND VTCFY = PV_CFY
AND VTPNZ = PV_PN), 0) * 100 SBFCAMT10 -- 공병보증금
,NVL((SELECT SUM(VTAA - VTAG)
FROM F59C947V
,F0901
WHERE VTAID = GMAID
AND GMOBJ = '10211'
AND GMSUB = '31'
AND VTAN8 = MNAN8
AND VTCFY = PV_CFY
AND VTPNZ = PV_PN), 0) * 100 SBFCAMT11 -- 상자보증금
,NVL((SELECT SUM(VTAA - VTAG)
FROM F59C947V
,F0901
WHERE VTAID = GMAID
AND GMOBJ = '10211'
AND GMSUB = '41'
AND VTAN8 = MNAN8
AND VTCFY = PV_CFY
AND VTPNZ = PV_PN), 0) * 100 SBFCAMT12 -- 팔레트보증금
,(NVL((SELECT SUM(VTAA) -- SUM(VTAA - VTAG)
FROM F59C945V -- F59C943V
,F0901
WHERE VTAID = GMAID
AND ((GMOBJ = '10101')
-- OR (GMOBJ = '10211' AND GMSUB IN ('61')) 상품대카드 제외
)
AND VTDGJ BETWEEN PV_FR_DGL AND PV_TO_DGL
AND VTAN8 = MNAN8), 0)
-- +
-- NVL((SELECT SUM(RPAG) * -1 AS RPAG
-- FROM F03B11
-- WHERE RPDGJ BETWEEN PV_FR_DGL AND PV_TO_DGL
-- AND RPAN8 = MNAN8
-- AND RPDCT IN ('RF', 'RP')), 0)
) * 100 SBFCAMT14 -- 상품대예금 (입금액 : 현금 + 카드 + 기타대체) --기타대체 제외
,NVL((SELECT SUM(VTAA) -- SUM(VTAA - VTAG)
FROM F59C945V -- F59C947V
,F0901
WHERE VTAID = GMAID
AND GMOBJ = '10201'
AND GMSUB = '11'
AND VTAN8 = MNAN8
AND VTCFY = PV_CFY
AND VTPNZ = PV_PN), 0) * 100 SBFCAMT15 -- 상품대어음
,NVL((SELECT SUM(SDAEXP+SDOPPID)
FROM F4211
WHERE SDDGL BETWEEN PV_FR_DGL AND PV_TO_DGL
AND SDAN8 = MNAN8
AND SDDCTO = 'SU'
AND SDCNDJ = 0), 0) * 100 SBFCAMT16 -- 상품대부도 -> 사용료합계로 대체(20181127)
,NVL((SELECT SUM(RPAG) * -1 AS RPAG
FROM F03B11
WHERE RPDCT IN ('RF', 'RP')
AND RPAN8 = MNAN8
AND RPDGJ BETWEEN PV_FR_DGL AND PV_TO_DGL), 0) * 100 SBFCAMT19 -- 상품대기타대체
,NVL((SELECT SUM(VTAA) -- SUM(VTAA - VTAG)
FROM F59C945V -- F59C947V
,F0901
WHERE VTAID = GMAID
AND GMOBJ = '10211'
AND GMSUB = '61'
AND VTAN8 = MNAN8
AND VTCFY = PV_CFY
AND VTPNZ = PV_PN), 0) * 100 SBFCAMT20 -- 상품대카드 (ga전표로 처리 로직 추가 필요)
,NVL((SELECT SUM(RPAG) FROM F03B11
WHERE RPAN8 = MNAN8
AND RPSDCT > ' '
AND RPFY = SUBSTR(PV_CFY,3,2)
AND RPPN = PV_PN
AND RPDCT NOT IN ('RF','RP','R1','RU','RJ')
AND SUBSTR(RPGLC,1,1) IN 'Y'), 0) * 100 SBFCAMT26 -- *판매합계*
,NVL((SELECT SUM(CINQ04)
FROM F57S016
WHERE CICFY = PV_CFY
AND CIPN = PV_PN
AND CIAN8 = MNAN8
AND CILITM = 'DG0004'), 0) SBNQ01 -- 퓨) 공병 12.5 L
,NVL((SELECT SUM(CINQ04)
FROM F57S016
WHERE CICFY = PV_CFY
AND CIPN = PV_PN
AND CIAN8 = MNAN8
AND CILITM = 'DG0003'), 0) SBNQ02 -- 퓨) 공병 18.9 L
,NVL((SELECT SUM(CINQ04)
FROM F57S016
WHERE CICFY = PV_CFY
AND CIPN = PV_PN
AND CIAN8 = MNAN8
AND CILITM = 'DP0004'), 0) SBNQ03 -- 퓨) 피박스 12.5 L
,NVL((SELECT SUM(CINQ04)
FROM F57S016
WHERE CICFY = PV_CFY
AND CIPN = PV_PN
AND CIAN8 = MNAN8
AND CILITM = 'DP0003'), 0) SBNQ04 -- 퓨) 피박스 18.9 L
,NVL((SELECT SUM(CINQ04)
FROM F57S016
WHERE CICFY = PV_CFY
AND CIPN = PV_PN
AND CIAN8 = MNAN8
AND CILITM = 'DG0002'), 0) SBNQ05 -- 석) 공병 13.0 L
,NVL((SELECT SUM(CINQ04)
FROM F57S016
WHERE CICFY = PV_CFY
AND CIPN = PV_PN
AND CIAN8 = MNAN8
AND CILITM = 'DG0001'), 0) SBNQ06 -- 석) 공병 18.9 L
,NVL((SELECT SUM(CINQ04)
FROM F57S016
WHERE CICFY = PV_CFY
AND CIPN = PV_PN
AND CIAN8 = MNAN8
AND CILITM = 'DP0002'), 0) SBNQ07 -- 석) 피박스 13.0 L
,NVL((SELECT SUM(CINQ04)
FROM F57S016
WHERE CICFY = PV_CFY
AND CIPN = PV_PN
AND CIAN8 = MNAN8
AND CILITM = 'DP0001'), 0) SBNQ08 -- 석) 피박스 18.9 L
,NVL((SELECT SUM(CINQ04)
FROM F57S016
WHERE CICFY = PV_CFY
AND CIPN = PV_PN
AND CIAN8 = MNAN8
AND CILITM = 'DF0002'), 0) SBNQ09 -- 대형팔레트
,NVL((SELECT SUM(CINQ04)
FROM F57S016
WHERE CICFY = PV_CFY
AND CIPN = PV_PN
AND CIAN8 = MNAN8
AND CILITM = 'DF0006'), 0) SBNQ10 -- 퓨리스 파레트
,NVL((SELECT FV57CURQTY
FROM F57S068V1 MN
WHERE FVAN8 = MNAN8
AND FVADDJ <= PV_TO_DGL
AND ROWNUM = 1), 0) SBNQ11 -- 임대냉온수기
-- ,NVL((SELECT FV57LNUM04
-- FROM F57S068V MN
-- WHERE FVAN8 = MNAN8
-- AND FVADDJ <= PV_TO_DGL
-- AND ROWNUM = 1), 0) SBNQ11 -- 임대냉온수기
,NVL((SELECT SUM(CINQ04)
FROM F57S016
WHERE CICFY = PV_CFY
AND CIPN = PV_PN
AND CIAN8 = MNAN8
AND CILITM = 'DF0003'), 0) SBNQ12 -- 석수 소형팔레트
FROM (SELECT ABAN8 MNAN8 FROM F0101 WHERE ABAT1 IN ('C ','NK ')) SD
Q. 거래처 별 금액을 집계하는 쿼리인데요 퍼포먼스가 좋지않아 튜닝을 생각하고 있는데 더 좋은 방법이 있을까요 ?
F59C947V 테이블에서 금액 집계 시 각 조건 별로 집계가 되는데 이 부분을 더 좋게 수정할 방법이 있을까요 ?
서브쿼리를 너무 많이 쓰네요.
서브쿼리는 조인으로 변경 가능하고,
동일 테이블을 조건만 달리하는 경우라면 테이블 한번만 읽고 처리하도록 변경 가능합니다.
다만 쿼리가 테이블명.컬럼명 이나, 알리아스.컬럼명 형태가 아니라서
테이블간의 관계를 유추하기 어려워 직접 수정해 드리기가 어렵네요.
간단한 예제 드릴께요.
-- 테이블 한번만 읽기 기본 포멧 SELECT SUM(DECODE(deptno, 10, sal)) sal_10 , SUM(DECODE(deptno, 20, sal)) sal_20 , SUM(CASE WHEN deptno = 10 AND job = 'SALES' THEN sal END) sal_10_sales -- 개별조건은 집계함수 안에서 CASE / DECODE 등으로 처리 -- FROM emp WHERE -- 공통조건은 여기에 -- ;