insert one account for each row and every dates and payments associated with that account base on the each sequence from JAN-DEC
/* 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_SE开发者_如何学PythonQ = 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.
This is the 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;
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;
加载中,请稍侯......
精彩评论