지난번 질문에 답해 주셔서 감사합니다. 데이터 입력이 잘못되어서 결과가 잘못나왔었습니다.(제 탓인거지요) ;;

근데 본섭이 8i 퍼스널이 깔렸는지 함수 지원을 안해서 self join 을 사용하였습니다.

쿼리가 넘길어져서. 이거 말고 다른 방법이 없는지 궁금해서.. 다시 문의를 드립니다.

테이블은.

tbl_code

CODE VARCHAR2(10)
IN_ORDER NUMBER(6,0)
MASTERCODE VARCHAR2(6)
DATA VARCHAR2(20)
IN_DATE VARCHAR2(8)
CONTENTS VARCHAR2(100)

과

tbl_kftc_data

KFTC_DATE CHAR(8)
CODE VARCHAR2(10)
IN_ORDER NUMBER(6,0)
COUNT NUMBER(10,0)
AMOUNT NUMBER(20,0)

입니다.

작성한 쿼리는

select  a.kftc_date,a.aplc_amount,  a.over_amount, a.error_amount, a.rst_amount, c.data - sum(b.rst_amount)
from (
 SELECT kftc_date
     , SUM(DECODE(code,'pm_aplc',count )) AS aplc_count
     , SUM(DECODE(code,'pm_aplc',amount)) AS aplc_amount
     , SUM(DECODE(code,'pm_over',count )) AS over_count
     , SUM(DECODE(code,'pm_over',amount)) AS over_amount
     , SUM(DECODE(code,'pm_rst' ,count )) AS error_count
     , SUM(DECODE(code,'pm_rst' ,amount)) AS error_amount
     , SUM(DECODE(code,'pm_aplc',amount)) - nvl(SUM(DECODE(code,'pm_over',amount)),0) - nvl(SUM(DECODE(code,'pm_rst' ,amount)),0) rst_amount
  FROM tbl_kftc_data
  where code IN ('pm_aplc','pm_over','pm_rst')
 GROUP BY kftc_date) a,
  (
 SELECT kftc_date
     , SUM(DECODE(code,'pm_aplc',count )) AS aplc_count
     , SUM(DECODE(code,'pm_aplc',amount)) AS aplc_amount
     , SUM(DECODE(code,'pm_over',count )) AS over_count
     , SUM(DECODE(code,'pm_over',amount)) AS over_amount
     , SUM(DECODE(code,'pm_rst' ,count )) AS error_count
     , SUM(DECODE(code,'pm_rst' ,amount)) AS error_amount
     , SUM(DECODE(code,'pm_aplc',amount)) - nvl(SUM(DECODE(code,'pm_over',amount)),0) - nvl(sum(DECODE(code,'pm_rst' ,amount)),0) rst_amount
  FROM tbl_kftc_data
  where code IN ('pm_aplc','pm_over','pm_rst')
 GROUP BY kftc_date) b
 , (   select a.code, a.in_date AS start_date, b.in_date as end_date, a.data
  from tbl_code a, tbl_code b
  where a.code = 'max_amo'
     and   b.code = 'max_amo'         
     and  a.in_order = (b.in_order - 1)
union all
  select code, in_date, null, data  from tbl_code
  where code = 'max_amo'
  and in_date = (
      select max(in_date) from tbl_code
        where  code = 'max_amo')
    ) c
 where a.kftc_date >= b.kftc_date
 and substr(a.kftc_date, 1,6) = substr(b.kftc_date, 1,6)
 and ((substr(a.kftc_date, 1,6) >= start_date  and c.end_date is null)
    or (substr(a.kftc_date, 1,6) >= start_date  and substr(a.kftc_date, 1,6) < c.end_date))
 and substr(a.kftc_date, 1, 6) = '200805'
 group by a.kftc_date,a.aplc_amount,  a.over_amount, a.error_amount, a.rst_amount,c.data ;

입니다.~~

헉 죄송합니다. 다른 쿼리엿습니다. ;; 에효~ 부탁하는 입장에서.. 잘못된걸... 붙이다니. ;;

다들 좋은 하루 되십시요