Java prepared statement returns incorrect results
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!
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!
Who is Participating?
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month7 days, 10 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
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")