Please help in refining/modifying the sql. [message #264841] |
Tue, 04 September 2007 13:54  |
anilreddy76
Messages: 9 Registered: September 2007
|
Junior Member |
|
|
Hi,
Could any body advise me on this query to check the following.
I've one table Test1 which contains "Id" ,"Location", "Input_Date","Value1" columns and other table in remote database which contains same columns.
I'm trying to insert the values into "Test1" based on the values from remote databse table "Test2" by checking the following condition in the query.
Here I'm facing one problem,I need to check for any new locations that are existing in Test2 against that input_date and insert the same location in Test1 table for same input_date which is not happing in this query.
Could any body advise me in refining this query..
Insert into Test1(ID,LOCATION,INPUT_DATE,Value1)
select ID_sequence.nextval,LOCATION,to_date(input_date,'MM/DD/YYYY'),Value1
from DBLink@Test2 Test where to_char(to_date(Test.input_date,'MM/DD/YYYY'), 'MM/DD/YYYY')
not in(select TO_CHAR(INPUT_DATE,'MM/DD/YYYY') from Test1) and
Test.Location not in(select distinct(INPUT_DATE) from Test1 where TO_CHAR(INPUT_DATE,'MM/DD/YYYY')=to_char(to_date(Test.input_date,'MM/DD/YYYY'),'MM/DD/YYYY'));
Thanks,
Anil
|
|
|
|
Re: Please help in refining/modifying the sql. [message #264843 is a reply to message #264841] |
Tue, 04 September 2007 14:12   |
anilreddy76
Messages: 9 Registered: September 2007
|
Junior Member |
|
|
Hi,
I didn't understand what you are saying?
I've written the following query and it is executing with out any errors but not fetching the desired results.
Insert into Test1(ID,LOCATION,INPUT_DATE,Value1)
select ID_sequence.nextval,LOCATION,to_date(input_date,'MM/DD/YYYY'),Value1
from DBLink@Test2 Test where to_char(to_date(Test2.input_date,'MM/DD/YYYY'), 'MM/DD/YYYY')
not in(select TO_CHAR(INPUT_DATE,'MM/DD/YYYY') from Test1) and
Test2.Location not in(select distinct(INPUT_DATE) from Test1 where TO_CHAR(INPUT_DATE,'MM/DD/YYYY')=to_char(to_date(Test.input_date,'MM/DD/YYYY'),'MM/DD/YYYY'));
|
|
|
|
Re: Please help in refining/modifying the sql. [message #264858 is a reply to message #264841] |
Tue, 04 September 2007 17:13   |
anilreddy76
Messages: 9 Registered: September 2007
|
Junior Member |
|
|
Example on how I am writing the query and facing the problem..
SQL> select * from test1;
LOCATION INPUT_DAT VALUE1
-------------------------------- --------- --------------------------------
Atlanta 03-SEP-07 125
Alabama 03-SEP-07 200
Alpharetta 03-SEP-07 23456
Dunwoody 03-SEP-07 2679
SQL> select * from test2;
LOCATION INPUT_DAT VALUE1
-------------------------------- --------- --------------------------------
Atlanta 03-SEP-07 125
Alabama 03-SEP-07 200
Alpharetta 03-SEP-07 23456
Dunwoody 03-SEP-07 2679
Rosewell 03-SEP-07 5000
SQL> ed
Wrote file afiedt.buf
1 Insert into Test1(LOCATION,INPUT_DATE,Value1)
2 select Location,input_date,Value1
3 from Test2 Tes2 where input_date
4 not in(select INPUT_DATE from Test1) and
5* Tes2.location not in(select distinct(LOCATION) from test1 where INPUT_DATE=Tes2.input_date)
SQL> /
0 rows created. [mod-edit]Added code tags. Do so yourself next time. Here you'll find more information.
[Updated on: Wed, 05 September 2007 01:14] by Moderator Report message to a moderator
|
|
|
Re: Please help in refining/modifying the sql. [message #265204 is a reply to message #264858] |
Wed, 05 September 2007 14:21   |
skooman
Messages: 913 Registered: March 2005 Location: Netherlands
|
Senior Member |
|
|
The reason nothing is inserted is the following line:
input_date NOT IN (SELECT input_date FROM test1)
Here you're stating: if any row in test2 has an input_date that occurs (in any row) in test1, then I don't want it. Hence, the last row of test2 with input_date 3-sep-2007 doesn't meet that criterium (since it happens to be the case that ALL rows in test1 have that input_date) and is not inserted.
BTW: always use to_date and a proper date format while working with dates, like:
to_date('01-SEP-2007','DD-MON-YYYY')
|
|
|
Re: Please help in refining/modifying the sql. [message #265218 is a reply to message #264841] |
Wed, 05 September 2007 15:38  |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Try the following
Insert into Test1(LOCATION,INPUT_DATE,Value1)
select Location,input_date,Value1
from Test2 Tes2
where not exists
(select null
from test1 a
where tes2.location = a.location
and tes2.input_date = a.input_date);
|
|
|