Home » SQL & PL/SQL » SQL & PL/SQL » Please help in refining/modifying the sql.
Please help in refining/modifying the sql. [message #264841] Tue, 04 September 2007 13:54 Go to next message
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 #264842 is a reply to message #264841] Tue, 04 September 2007 14:05 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It appears you did not read and follow the posting Guidelines as stated in the STICKY post at the top of this forum.

As a result IMO, there is not sufficient details provided to event start to guess where the problem may be.

>"which is not happing"
Ignoring the spelling problem such a declaration is 100% useless even though it is likely to be true.

Re: Please help in refining/modifying the sql. [message #264843 is a reply to message #264841] Tue, 04 September 2007 14:12 Go to previous messageGo to next message
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 #264844 is a reply to message #264841] Tue, 04 September 2007 14:28 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I've written the following query and it is executing with out any errors but not fetching the desired results.

Sorry to hear that you are having problems.

1) How are we supposed to know EXACTLY what is being returned?
2) How are we supposed to know EXACTLY what are the desired results?

Since you chose to NOT follow the posting guidelines, I chose to NOT guess at any possible solution.

You're On Your Own (YOYO)!
Re: Please help in refining/modifying the sql. [message #264858 is a reply to message #264841] Tue, 04 September 2007 17:13 Go to previous messageGo to next message
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 Go to previous messageGo to next message
skooman
Messages: 912
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 Go to previous message
Bill B
Messages: 1484
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);
Previous Topic: Correct script using indexes
Next Topic: use CLOB or BLOD for storing html generated text
Goto Forum:
  


Current Time: Thu Dec 08 19:59:14 CST 2016

Total time taken to generate the page: 0.09384 seconds