Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 get latest date to insert into table

Author  Topic 

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?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-23 : 05:40:46
Looks to me like you are working on ORACLE because TRUNK() isn't known in MS SQL Server.
Maybe you can get better help in oracle forums because here we are on MS SQL Server only.

Try DBFORUMS.COM/ORACLE for example.


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
   

- Advertisement -