Solved

Java prepared statement returns incorrect results

Posted on 2004-08-20
5
207 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sumDigits  challenge 7 60
changePi Challenge 15 75
Eclipse Neon and jdk 1.8.0 11 67
Error with Java/Cache JDBC Classpath 2 19
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 …
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now