-- 변경전 쿼리의 decode함수는 조인된 총 자료건수만큼 수행됩니다.
-- 변경후 쿼리는 3년 * 4분기 = 총 12번만 수행됩니다.
SELECT NVL(MIN(DECODE(yyyy_q,(:ls_year - 2)||’-1’,sq)),0) before_sq1
     , NVL(MIN(DECODE(yyyy_q,(:ls_year - 2)||’-2’,sq)),0) before_sq2
     , NVL(MIN(DECODE(yyyy_q,(:ls_year - 2)||’-3’,sq)),0) before_sq3
     , NVL(MIN(DECODE(yyyy_q,(:ls_year - 2)||’-4’,sq)),0) before_sq4
     , NVL(MIN(DECODE(yyyy_q,(:ls_year - 1)||’-1’,sq)),0) last_sq1
     , NVL(MIN(DECODE(yyyy_q,(:ls_year - 1)||’-2’,sq)),0) last_sq2
     , NVL(MIN(DECODE(yyyy_q,(:ls_year - 1)||’-3’,sq)),0) last_sq3
     , NVL(MIN(DECODE(yyyy_q,(:ls_year - 1)||’-4’,sq)),0) last_sq4
     , NVL(MIN(DECODE(yyyy_q,(:ls_year    )||’-1’,sq)),0) cur_sq1
     , NVL(MIN(DECODE(yyyy_q,(:ls_year    )||’-2’,sq)),0) cur_sq2
     , NVL(MIN(DECODE(yyyy_q,(:ls_year    )||’-3’,sq)),0) cur_sq3
     , NVL(MIN(DECODE(yyyy_q,(:ls_year    )||’-4’,sq)),0) cur_sq4
  FROM (SELECT TO_CHAR(TO_DATE(a.issue_mm,’yyyymm’),’yyyy-Q’) yyyy_q
             , ROUND(SUM(b.amt)/1000) sq
          FROM rt_wongm01 a
             , rt_wongd01 b
         WHERE a.agency_id  = b.agency_id
           AND a.issue_mm   = b.issue_mm
           AND a.car_no     = b.car_no
           AND a.car_dae_no = b.car_dae_no
           AND b.acc_cd LIKE ’423%’
           AND a.cust_cd    = 거래처
           AND a.issue_mm  <= :ls_year || ’12’
           AND a.issue_mm  >= (:ls_year - 2) || ’01’
         GROUP BY TO_CHAR(TO_DATE(a.issue_mm,’yyyymm’),’yyyy-Q’)
        )