Solved

Java prepared statement returns incorrect results

Posted on 2004-08-20
5
220 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Which non-HTML GUI front end to use with Java? 3 67
Crystal Reports Licensing Questions 4 86
swing controls 2 37
dao vs facade design patterns 2 70
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…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
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:
Suggested Courses

734 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