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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 get latest date to insert into table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 07/23/2012 :  04:38:50  Show Profile  Reply with Quote
i have a set of duplicate data and i need to insert into main table.
for now my sql statement is like this :

( acct_no
SELECT acct1_acct_no
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
,T1.bill_sys_appl_id acct1_bill_sys_appl_id
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?

Flowing Fount of Yak Knowledge

8781 Posts

Posted - 07/23/2012 :  05:40:46  Show Profile  Visit webfred's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000