Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Java prepared statement returns incorrect results

Posted on 2004-08-20
5
Medium Priority
?
237 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
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.
Suggested Courses

885 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