|
shar_wani
Starting Member
2 Posts |
 Posted - 2012-07-23 : 04:38:50
|
| i have a set of duplicate data and i need to insert into main table.for now my sql statement is like this : INSERT INTO ACCOUNT ( acct_no ,state_code ,sys_appl_id ,cust_id ,bill_period ,acct_status ,acct_name ,addr_line1 ,addr_line2 ,addr_line3 ,addr_line4 ,addr_line5 ,addr_line6 ,addr_line7 ,addr_line8 ,postal_code ,final_acct_date ,last_upd_date ,rev_code ,npcs_acct_cat ,bill_sys_appl_id ) SELECT acct1_acct_no ,acct1_state_code ,acct1_sys_appl_id ,acct1_cust_id ,acct1_bill_period ,acct1_acct_status ,acct1_acct_name ,acct1_addr_line1 ,acct1_addr_line2 ,acct1_addr_line3 ,acct1_addr_line4 ,acct1_addr_line5 ,acct1_addr_line6 ,acct1_addr_line7 ,acct1_addr_line8 ,acct1_postal_code ,acct1_final_acct_date --,acct1_last_upd_date ,SYSDATE ,NULL ,NULL ,acct1_bill_sys_appl_id FROM ( SELECT distinct /*+ordered */ T1.acct_no acct1_acct_no ,T1.state_code acct1_state_code ,T1.sys_appl_id acct1_sys_appl_id ,T1.cust_id acct1_cust_id ,T1.bill_period acct1_bill_period ,T1.acct_status acct1_acct_status ,T1.acct_name acct1_acct_name ,T1.addr_line1 acct1_addr_line1 ,T1.addr_line2 acct1_addr_line2 ,T1.addr_line3 acct1_addr_line3 ,T1.addr_line4 acct1_addr_line4 ,T1.addr_line5 acct1_addr_line5 ,T1.addr_line6 acct1_addr_line6 ,T1.addr_line7 acct1_addr_line7 ,T1.addr_line8 acct1_addr_line8 ,T1.postal_code acct1_postal_code ,T1.final_acct_date acct1_final_acct_date --,T1.update_date acct1_last_upd_date ,SYSDATE ,NULL ,NULL ,T1.bill_sys_appl_id acct1_bill_sys_appl_id ,T2.acct_no ,T2.sys_appl_id ,T2.cust_id FROM ICP_NPCS_INBND_PROF_ACCOUNT T1 ,ACCOUNT T2 WHERE T1.acct_no = T2.acct_no AND T1.sys_appl_id = T2.sys_appl_id AND T1.update_date = (select max(a.update_date) from ICP_NPCS_INBND_PROF_ACCOUNT a, ACCOUNT b where a.acct_no = b.acct_no) AND T1.update_date >= trunc(T2.last_upd_date) AND T1.error_code is null ) 0 record(s) affected for this sql!same statement for update function. but in insert function seems didn't work. to differentiate the latest data is by update_date.is the something wrong with my sql? |
|
get latest date to insert into table