Solved

Java prepared statement returns incorrect results

Posted on 2004-08-20
5
212 Views
Last Modified: 2010-03-31
Hi this is really an oracle question but because I'm doing the work in java (response times are java queries are cool), I thought I'd post the question here!

basically I have a oracle table as defined below.

CREATE TABLE INSTRUMENT
(
  INSTID              NUMERIC(10)     ,  (key field)
  CCY                 CHAR(3)             ,    
  PRICINGBASIS        NUMERIC(28,14)           ,
  INSTRUMENTCLASS     VARCHAR(20)         ,
  INSTRUMENTSUBCLASS  VARCHAR(20)        ,

)  APRROX 14 000  rows

I also have another oracle table INSTRUMENT_TEMP (approx 400 rows)  with the same table definition. This table brings
in new instruments every day but has some old instruments which will already be in INSTRUMENT.

I need to insert all data rows in  INSTRUMENT_TEMP which do not exists in INSTRUMENT ie all instid that are not there.

I'm doing this in a prepared statement, the java looks ok (has been checked by others) I just need to get the ORACLE
right .

When I do my query I end up with all the rows which are in INSTRUMENT and not in INSTRUMENT_TEMP!
but I'm interested in those which are in INSTRUMENT_TEMP but not in INSTRUMENT.

So if  INSTRUMENT_TEMP has 400 rows (INSTRUMENT has 14000) of which 100 are new my query should return 100 rows , I get
13900 rows in my resultset !

INSERT INTO INSTRUMENT
SELECT it.instind,
           it.....
FROM INSTRUMENT i,
         INSTRUMENT_TEMP it
WHERE it.instid != i.inst_id

Please help!





 
 

0
Comment
Question by:azsat
  • 2
5 Comments
 
LVL 35

Expert Comment

by:girionis
ID: 11853939
Hi azsat,

Try this

INSERT INTO INSTRUMENT
SELECT it.instind,
           it.....
FROM INSTRUMENT i,
         INSTRUMENT_TEMP it
WHERE it.instid = i.inst_id


Regards
0
 
LVL 21

Accepted Solution

by:
MogalManic earned 500 total points
ID: 11855052
The correct query is as follows:
INSERT INTO INSTRUMENT
SELECT it.instind,
           it.....
FROM INSTRUMENT i,
         INSTRUMENT_TEMP it
WHERE it.instid = i.inst_id(+)
and i.inst_id=null

This will retrive all of the records in INSTRUMENT_TEMP that do NOT have a corresponding primary key record in INSTRUMENT_TEMP.

NOTE:  This query is Oracle specific (the (+) for outer join is specific to Oracle).  To make it portable accross DB's, you would need to use OUTER JOIN expression (Google "ANSI SQL OUTER JOIN")
0
 
LVL 35

Expert Comment

by:girionis
ID: 11855238
I agree with Venabili, either this question neds to be moved (preferable) or post a link for 20 pts to this question in the Oracel TA.
0
 

Author Comment

by:azsat
ID: 11855263
Thank  you.  The question was urgent.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
topping2 challenge 13 90
GUI builder for Eclipse? 8 35
Configure a Bean in an XML file 4 31
What's wrong with this code? 4 23
After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This video teaches viewers about errors in exception handling.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question