inserting one account for each row and every dates and payments associated with that account in one row each in (ORACLE)

Question

This how the script run from the begin to the end

INSERT INTO TAIWOS.TEMP_CAPONE_DATE_JUL2--------- This is working fine 
SELECT  FACS_ACCT_NUM, PROMISE_DATE, PROMISE_AMT, '0'
FROM    MGMT.FACS_PDC_PROMISE 
WHERE   CLIENT_CD IN ('CAP20J', 'CAP20U', 'CAP21E', 'CAP20E', 'CAP21J', 'CAP22E', 'CAP22J', 'CAP22U',
                        'CAP23E', 'CAP23J', 'CAP30E', 'CAP30J', 'CAP30U', 'CAP31E', 'CAP31J', 'CAP31U',
                        'CAP36E', 'CAP36J', 'CAP36U', 'CAP37E', 'CAP37J', 'CAP37U', 'CAP38E', 'CAP38J', 'CAP38U')
AND    PROMISE_DATE >= trunc(ADD_MONTHS(sysdate,0),'MM') -- ENTIRE PRIOR MONTHPRIOR MON
AND      (PROMISE_BROKEN_DATE IS NULL or promise_broken_date >=  trunc(ADD_MONTHS(sysdate,0),'MM'))
--AND     P.FACS_ACCT_NUM = '17938393'
ORDER BY 1;

COMMIT;

--SELECT * FROM TAIWOS.TEMP_CAPONE_DATE_JUL2

update taiwos.Temp_capone_date_jul2
set dt_seq =1
where (facs_acct_num, pdc_dt_1) in
(select facs_acct_num, min(pdc_dt_1) 
from taiwos.Temp_capone_date_jul2 
where dt_seq = 0 group by facs_acct_num)
and dt_seq = 0;

COMMIT;

 update taiwos.Temp_capone_date_jul2
set dt_seq =2
where (facs_acct_num, pdc_dt_1) in
(select facs_acct_num, min(pdc_dt_1) 
from taiwos.Temp_capone_date_jul2 
where dt_seq = 0 group by facs_acct_num)
and dt_seq = 0;

COMMIT;

 update taiwos.Temp_capone_date_jul2
set dt_seq =3
where (facs_acct_num, pdc_dt_1) in
(select facs_acct_num, min(pdc_dt_1) 
from taiwos.Temp_capone_date_jul2 
where dt_seq = 0 group by facs_acct_num)
and dt_seq = 0;

COMMIT;

 update taiwos.Temp_capone_date_jul2
set dt_seq =4
where (facs_acct_num, pdc_dt_1) in
(select facs_acct_num, min(pdc_dt_1) 
from taiwos.Temp_capone_date_jul2 
where dt_seq = 0 group by facs_acct_num)
and dt_seq = 0;

COMMIT;

 update taiwos.Temp_capone_date_jul2
set dt_seq =5
where (facs_acct_num, pdc_dt_1) in
(select facs_acct_num, min(pdc_dt_1) 
from taiwos.Temp_capone_date_jul2 
where dt_seq = 0 group by facs_acct_num)
and dt_seq = 0;

COMMIT;

  update taiwos.Temp_capone_date_jul2
set dt_seq =6
where (facs_acct_num, pdc_dt_1) in
(select facs_acct_num, min(pdc_dt_1) 
from taiwos.Temp_capone_date_jul2 
where dt_seq = 0 group by facs_acct_num)
and dt_seq = 0;

COMMIT;

 update taiwos.Temp_capone_date_jul2
set dt_seq =7
where (facs_acct_num, pdc_dt_1) in
(select facs_acct_num, min(pdc_dt_1) 
from taiwos.Temp_capone_date_jul2 
where dt_seq = 0 group by facs_acct_num)
and dt_seq = 0;

COMMIT;

 update taiwos.Temp_capone_date_jul2
set dt_seq =8
where (facs_acct_num, pdc_dt_1) in
(select facs_acct_num, min(pdc_dt_1) 
from taiwos.Temp_capone_date_jul2 
where dt_seq = 0 group by facs_acct_num)
and dt_seq = 0;

COMMIT;

 update taiwos.Temp_capone_date_jul2
set dt_seq = 9
where (facs_acct_num, pdc_dt_1) in
(select facs_acct_num, min(pdc_dt_1) 
from taiwos.Temp_capone_date_jul2 
where dt_seq = 0 group by facs_acct_num)
and dt_seq = 0;

COMMIT;

 update taiwos.Temp_capone_date_jul2
set dt_seq =10
where (facs_acct_num, pdc_dt_1) in
(select facs_acct_num, min(pdc_dt_1) 
from taiwos.Temp_capone_date_jul2 
where dt_seq = 0 group by facs_acct_num)
and dt_seq = 0;

COMMIT;

 update taiwos.Temp_capone_date_jul2
set dt_seq =11
where (facs_acct_num, pdc_dt_1) in
(select facs_acct_num, min(pdc_dt_1) 
from taiwos.Temp_capone_date_jul2 
where dt_seq = 0 group by facs_acct_num)
and dt_seq = 0;

COMMIT;

 update taiwos.Temp_capone_date_jul2
set dt_seq =12
where (facs_acct_num, pdc_dt_1) in
(select facs_acct_num, min(pdc_dt_1) 
from taiwos.Temp_capone_date_jul2 
where dt_seq = 0 group by facs_acct_num)
and dt_seq = 0;

COMMIT;

This script was suppose to insert one account for each row and every dates and payments associated with that account base on the each sequence from 1- 12 representing each month , Instead the script is inserting one account for each row with the same payments in the entire records\ is not working right .

 INSERT INTO TAIWOS.TEMP_CAPONE_PART12 (FACS_ACCT_NUM, PDC_DT_1, PDC_AMT_1, PDC_DT_2, PDC_AMT_2, PDC_DT_3, PDC_AMT_3,
                                    PDC_DT_4, PDC_AMT_4, PDC_DT_5, PDC_AMT_5, PDC_DT_6, PDC_AMT_6, PDC_DT_7, PDC_AMT_7, 
                                    PDC_DT_8, PDC_AMT_8, PDC_DT_9, PDC_AMT_9, PDC_DT_10, PDC_AMT_10, PDC_DT_11, PDC_AMT_11, 
                                    PDC_DT_12, PDC_AMT_12)
SELECT  DISTINCT FACS_ACCT_NUM,
        (SELECT PDC_DT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2  
         WHERE DT_SEQ = 1 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_DT_1,
        (SELECT PDC_AMT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 1 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_AMT_1,
        (SELECT PDC_DT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2 
         WHERE DT_SEQ = 2 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_DT_2,
        (SELECT PDC_AMT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 2 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_AMT_2,
        (SELECT PDC_DT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 3 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_DT_3,
        (SELECT PDC_AMT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 3 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_AMT_3,
        (SELECT PDC_DT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2  
         WHERE DT_SEQ = 4 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_DT_4,
        (SELECT PDC_AMT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 4 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_AMT_4,
        (SELECT PDC_DT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 5 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_DT_5,
        (SELECT PDC_AMT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 5 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_AMT_5,
        (SELECT PDC_DT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 6 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_DT_6,
        (SELECT PDC_AMT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2 
         WHERE DT_SEQ = 6 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_AMT_6,
        (SELECT PDC_DT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2  
         WHERE DT_SEQ = 7 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_DT_7,
        (SELECT PDC_AMT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 7 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_AMT_7,
        (SELECT PDC_DT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2  
         WHERE DT_SEQ = 8 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_DT_8,
        (SELECT PDC_AMT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2 
         WHERE DT_SEQ = 8 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_AMT_8,
        (SELECT PDC_DT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 9 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_DT_9,
        (SELECT PDC_AMT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 9 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_AMT_9,
        (SELECT PDC_DT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 10 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_DT_10,
        (SELECT PDC_AMT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 10 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_AMT_10,
        (SELECT PDC_DT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 11 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_DT_11,
        (SELECT PDC_AMT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2  
         WHERE DT_SEQ = 11 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_AMT_11,
        (SELECT PDC_DT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2  
         WHERE DT_SEQ = 12 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_DT_12,
        (SELECT PDC_AMT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 12 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum <= 1) PDC_AMT_12
FROM    TAIWOS.TEMP_CAPONE_DATE_JUL2 
WHERE   DT_SEQ = 1;  

COMMIT;

This is working fine

INSERT INTO TAIWOS.CAP_ONE_MONTHLY_WINS
SELECT  A.REF_NO as debt_mgr_rtr_acct_num, D.PNET_ID as pnet_id,
        SUM(case when F.IS_CONTACT >= 1 then 1 else 0 end) AS rpc_cnt,
        SUM(case when F.ACTION_CD IN ('TR','TW','TO') then 1 else 0 end) AS man_atts,
        SUM(case when F.ACTION_CD IN ('DO', 'DR', 'DS', 'DW') then 1 else 0 end) AS auto_atts,
        TO_CHAR(PDC_DT_1, 'YYYY-MM-DD') as pdc_dt_1,PDC_AMT_1 as pdc_amt_1,TO_CHAR(PDC_DT_2, 'YYYY-MM-DD') as pdc_dt_2,PDC_AMT_2 as pdc_amt_2,
        TO_CHAR(PDC_DT_3, 'YYYY-MM-DD') as pdc_dt_3,PDC_AMT_3 as pdc_amt_3,TO_CHAR(PDC_DT_4, 'YYYY-MM-DD') as pdc_dt_4,PDC_AMT_4 as pdc_amt_4,
        TO_CHAR(PDC_DT_5, 'YYYY-MM-DD') as pdc_dt_5,PDC_AMT_5 as pdc_amt_5,TO_CHAR(PDC_DT_6, 'YYYY-MM-DD') as pdc_dt_6,PDC_AMT_6 as pdc_amt_6,
        TO_CHAR(PDC_DT_7, 'YYYY-MM-DD') as pdc_dt_7,PDC_AMT_7 as pdc_amt_7,TO_CHAR(PDC_DT_8, 'YYYY-MM-DD') as pdc_dt_8,PDC_AMT_8 as pdc_amt_8,
        TO_CHAR(PDC_DT_9, 'YYYY-MM-DD') as pdc_dt_9,PDC_AMT_9 as pdc_amt_9,TO_CHAR(PDC_DT_10, 'YYYY-MM-DD') as pdc_dt_10,PDC_AMT_10 as pdc_amt_10,
        TO_CHAR(PDC_DT_11, 'YYYY-MM-DD') as pdc_dt_11,PDC_AMT_11 as pdc_amt_11,TO_CHAR(PDC_DT_12, 'YYYY-MM-DD') as pdc_dt_12,PDC_AMT_12 as pdc_amt_12,
        TO_CHAR(A.ACCT_PLACE_DATE, 'YYYY-MM-DD') AS plcmt_strt_dt
FROM  MGMT.FACS_ACCT_EVAL A
  LEFT OUTER JOIN MGMT.FACS_ACTION_RESULTS F
    ON F.SOURCE_SYSTEM_CD = A.SOURCE_SYSTEM_CD AND F.ACCT_NUM = A.ACCT_NUM
     LEFT OUTER JOIN MISRPT.DEF_DU_ARROW_FINANCIALS M
      ON M.SOURCE_SYS_CD = A.SOURCE_SYSTEM_CD AND M.ACCOUNT_NUM = A.ACCT_NUM
       LEFT OUTER JOIN TAIWOS.TEMP_CAPONE_PART1 T
        ON T.FACS_ACCT_NUM = A.ACCT_NUM
         LEFT OUTER JOIN MISRPT.DEF_DU_CAP_SUPPL_PHONE D
          ON A.ACCT_NUM = D.ACCOUNT_NUM AND A.SOURCE_SYSTEM_CD = D.SOURCE_SYS_CD
WHERE   M.HARD_RECALL_DTE is null
AND     A.CLIENT_NO IN ('CAP20J', 'CAP20U', 'CAP21E', 'CAP20E', 'CAP21J', 'CAP22E', 'CAP22J', 'CAP22U',
                        'CAP23E', 'CAP23J', 'CAP30E', 'CAP30J', 'CAP30U', 'CAP31E', 'CAP31J', 'CAP31U',
                        'CAP36E', 'CAP36J', 'CAP36U', 'CAP37E', 'CAP37J', 'CAP37U', 'CAP38E', 'CAP38J', 'CAP38U')
GROUP BY A.REF_NO, D.PNET_ID,
        PDC_DT_1,PDC_AMT_1,PDC_DT_2,PDC_AMT_2,
        PDC_DT_3,PDC_AMT_3,PDC_DT_4,PDC_AMT_4,
        PDC_DT_5,PDC_AMT_5,PDC_DT_6,PDC_AMT_6,
        PDC_DT_7,PDC_AMT_7,PDC_DT_8,PDC_AMT_8,
        PDC_DT_9,PDC_AMT_9,PDC_DT_10,PDC_AMT_10,
        PDC_DT_11,PDC_AMT_11,PDC_DT_12,PDC_AMT_12,
        TO_CHAR(A.ACCT_PLACE_DATE, 'YYYY-MM-DD');
COMMIT;

Answer 1

Create a procedure with a cursor to hold your insert statement and a record to hold your cursor. Then scroll through the record to update your records matching your criteria.

cursor t1_cur is  SELECT  DISTINCT FACS_ACCT_NUM, 
        (SELECT PDC_DT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2   
         WHERE DT_SEQ = 1 AND FACS_ACCT_NUM = FACS_ACCT_NUM and rownum = x) PDC_DT_x, 
        (SELECT PDC_AMT_1 FROM TAIWOS.TEMP_CAPONE_DATE_JUL2
...
 type t1_type is table of t1_cur%ROWTYPE;
In execution
...
           -- insert an initial row
           INSERT INTO TAIWOS.TEMP_CAPONE_PART12 (FACS
...
open t1_cur;
 loop
      fetch t1 bulk collect into my_rec1 limit 200000;
      for i in 1 .. my_rec1.count loop
          if  rec1(i).DT_SEQ = 1 AND FACS_ACCT_NUM = FACS_ACCT_NUM\
          update foo set bar
          ...
          elsif
          ...
          elsif
          ...
          else
...