너무 허접이라서 쿼리 좀 도움을 부탁드립니다. 0 3 950

by 왕보초 [Oracle 기초] 쿼리 오라클 통계 레포트 [2020.04.05 03:01:25]


data.xls (34,816Bytes)
캡처.PNG (69,906Bytes)

네가 허접하게 쿼리를 짜서 여러분들에 조언 부탁드립니다.

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 기준에 합산 기본금액을 보여주는 쿼리입니다.

 

by 마농 [2020.04.06 09:04:27]
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
;

 


by 왕보초 [2020.04.06 10:01:24]

A 인라인뷰에   A.apply_no 추가해야되는것죠??


by 왕보초 [2020.04.06 09:59:24]

언제나 정확한 답변 감사드립니다. 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입