DATOR


대용량 DATA CONVERSION Application Development


PURPOSE

      이력관리에서 점형태의 DATA를 선분형태의 DATA로 변경하는 방법

SCOPE & APPLICATION

  • 기존 데이터 형태

계정과목

금액

발생일

10000001

100

19990201

10000001

200

19990321

10000001

300

19990414

10000001

100

19990508

10000001

200

19990620

10000001

300

19990702

10000001

100

19990822

10000001

200

19990913

     .

         .

     .

     .

         .

     .

     .

         .

     .

  • 변경하고자 하는 데이터 형태
     

계정과목

금액

누계

발생일

종료일

10000001

100

100

19990201

19990320

10000001

200

300

19990321

19990413

10000001

300

600

19990414

19990507

10000001

100

700

19990508

19990619

10000001

200

1000

19990620

19990701

10000001

300

1300

19990702

19990821

10000001

100

1400

19990822

19990912

10000001

200

1600

19990913

99991231

    .

      .

      .

     .

     .

DESCRIPTION

          INSERT INTO XXXXXXX        
          select  /*+ parallel(x 10) parallel(y 10) ordered use_nl(x y) */
                x.acts_cd,  x.st_d,
                min(decode(sign(seq2-seq1),1,y.st_d,'99991231')) end_d,
                nvl(min(x.bal),0) + nvl(sum(decode(sign(x.st_d-y.st_d),1,y.bal)),0) bal,
                nvl(min(x.bsbl),0)+ nvl(sum(decode(sign(x.st_d-y.st_d),1,y.bsbl)),0) bsbl
         from (select acts_cd, st_d, bal, bsbl rownum seq1
                 from (select  br_cd,  acts_cd, st_d, bal,bsbl, yr_dr_acm,yr_cr_acm
                        from  ( select acts_cd, rtrim(fnc_prcs_d)   st_d,
                                       nvl((dr_amt - cr_amt),0) bal,   0   bsbl
                                 from   t02_slp_d_bh_hst
                       where  fnc_prcs_d between '19990101' and '19991025'
                        and   slp_tp = '1'
                       union all    
                       select acts_cd, rtrim(st_d) st_d, bal, bsbl, yr_dr_acm, yr_cr_acm
                         from   t02_acts_bal_hst
                       where '19990831' between st_d and end_d )
                      group by  acts_cd,  st_d, bal, bsbl ))  x,
              (select acts_cd,   st_d, bal, bsbl, rownum seq2
                from (select  acts_cd, st_d, bal, bsbl
                       from ( select  acts_cd,  rtrim(fnc_prcs_d)   st_d,        
                                     nvl((dr_amt - cr_amt),0) bal, 0 bsbl
                               from   t02_slp_d_bh_hst
                               where  fnc_prcs_d between '19990101' and '19991025'
                                 and   slp_tp = '1'
                              union all    
                              select acts_cd,  rtrim(st_d) st_d,        bal, bsbl
                                from   t02_acts_bal_hst
                               where '19990831' between st_d and end_d )
                     group by  acts_cd,   st_d, bal, bsbl ) ) y
         where x.acts_cd =  y.acts_cd
          and (x.st_d >= y.st_d)
       group by  x.acts_cd,  x.st_d

 

     

Leave Comments