Solved

Java prepared statement returns incorrect results

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

856 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