네가 허접하게 쿼리를 짜서 여러분들에 조언 부탁드립니다.
Select TBL_BASSKEY, EXCUTE_DATE AS excutDe , BSNS_NAME AS bsnsNm, BUDGET_TAXITM_CODE AS budgetTaxitmIem, BUDGET_BUDGET_CODE AS budgetBudgetIem, (Select NVL(SUM(NCSSRY),0) From LP_APP_BIZ_BUDGET_ORIBDGT S where S.APPLY_NO = A.APPLY_NO AND S.BUDGET_TAXITM_CODE = A.BUDGET_TAXITM_CODE AND S.BUDGET_BUDGET_CODE = A.BUDGET_BUDGET_CODE) AS orgExpndtrAm, (Select NVL(SUM(SLFSHR),0) From LP_APP_BIZ_BUDGET_ORIBDGT S where S.APPLY_NO = A.APPLY_NO AND S.BUDGET_TAXITM_CODE = A.BUDGET_TAXITM_CODE AND S.BUDGET_BUDGET_CODE = A.BUDGET_BUDGET_CODE) AS orgincmeAm, NT_AM AS incmeAm, OS_AM AS expndtrAm From ( SELECT DISTINCT M.TBL_BASSKEY, M.EXCUTE_DATE, M.BSNS_NAME, M.APPLY_NO, M.BUDGET_TAXITM_CODE M.BUDGET_BUDGET_CODE, NT_AM, OS_AM FROM BUDGET_REPRT_01 M, (SELECT TBL_BASSKEY, BUDGET_GBL, EXCUTE_DATE, BSNS_NAME, BUDGET_TAXITM_CODE, BUDGET_BUDGET_CODE, SUM(BLCE) NT_AM FROM BUDGET_REPRT_01 WHERE 1 = 1 AND TBL_BASSKEY = '202003301703142' AND BUDGET_GBL = 'NTNTRS' GROUP BY TBL_BASSKEY, BUDGET_GBL, EXCUTE_DATE, BSNS_NAME, BUDGET_TAXITM_CODE, BUDGET_BUDGET_CODE) A, (SELECT TBL_BASSKEY, BUDGET_GBL, EXCUTE_DATE, BSNS_NAME, BUDGET_TAXITM_CODE, BUDGET_BUDGET_CODE, SUM(BLCE) OS_AM FROM BUDGET_REPRT_01 WHERE 1 = 1 AND TBL_BASSKEY = '202003301703142' AND BUDGET_GBL = 'ONSLF' GROUP BY TBL_BASSKEY, BUDGET_GBL, EXCUTE_DATE, BSNS_NAME, BUDGET_TAXITM_CODE, BUDGET_BUDGET_CODE) B WHERE 1 = 1 AND M.TBL_BASSKEY = '202003301703142' AND M.TBL_BASSKEY = A.TBL_BASSKEY (+) AND M.EXCUTE_DATE = A.EXCUTE_DATE (+) AND M.BSNS_NAME = A.BSNS_NAME (+) AND M.BUDGET_TAXITM_CODE = A.BUDGET_TAXITM_CODE (+) AND M.BUDGET_BUDGET_CODE = A.BUDGET_BUDGET_CODE (+) AND M.TBL_BASSKEY = B.TBL_BASSKEY (+) AND M.EXCUTE_DATE = B.EXCUTE_DATE (+) AND M.BSNS_NAME = B.BSNS_NAME (+) AND M.BUDGET_TAXITM_CODE = B.BUDGET_TAXITM_CODE (+) AND M.BUDGET_BUDGET_CODE = B.BUDGET_BUDGET_CODE (+) ORDER BY BSNS_NAME, EXCUTE_DATE ) A
쿼리에 확인 부탁드립니다...
해당 데이터 엑셀, 이미지와 쿼리를 돌릴 경우 나와야 하는 화면에 대한 이미지를 올려드립니다.
EXCUTE_DATE, BSNS_NAME, BUDGET_TAXITM_CODE, BUDGET_BUDGET_CODE 기준으로 금액에 합을 구하고
[테이블A] BUDGET_TAXITM_CODE, BUDGET_BUDGET_CODE 기준에 합산 기본금액을 보여주는 쿼리입니다.
SELECT a.tbl_basskey , a.bsns_name , a.budget_taxitm_code , a.budget_budget_code , a.nt_am , a.os_am , NVL(SUM(b.ncssry), 0) orgExpndtrAm , NVL(SUM(b.slfshr), 0) orgincmeAm FROM (SELECT tbl_basskey , bsns_name , apply_no -- 추가 , budget_taxitm_code , budget_budget_code , NVL(SUM(DECODE(budget_gbl, 'NTNTRS', blce)), 0) nt_am , NVL(SUM(DECODE(budget_gbl, 'ONSLF', blce)), 0) os_am FROM budget_reprt_01 WHERE tbl_basskey = '202003301703142' GROUP BY tbl_basskey , bsns_name , apply_no -- 추가 , budget_taxitm_code , budget_budget_code ) a , lp_app_biz_budget_oribdgt b WHERE 1=1 AND a.apply_no = b.apply_no (+) AND a.budget_taxitm_code = b.budget_taxitm_code(+) AND a.budget_budget_code = b.budget_budget_code(+) GROUP BY a.tbl_basskey , a.bsns_name , a.budget_taxitm_code , a.budget_budget_code , a.nt_am , a.os_am ;