insert an account for each row and all dates and payments associated with that account base on each JAN-DEC sequence

advertisements

/* 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;


The row inserted for each distinct account number will have Dates and Amounts depending on what rows are found in TEMP_CAPONE_DATE_JUL2 for that account number; are you sure there are rows with DT_SEQ = 1, 2, 3 .. 12, and each row has different dates and amounts?

For further diagnosis you need to show us some sample data - e.g. pick an account number and list all the rows in TEMP_CAPONE_DATE_JUL2 for that account number.