Java prepared statement returns incorrect results
Posted on 2004-08-20
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
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
FROM INSTRUMENT i,
WHERE it.instid != i.inst_id