any SQL gurus?

[lozina]

Technologies involved:
Java
Oracle 10g
iBATIS SQL maps

I'm trying to do something where I insert rows only if certain parts of the data does not already exist. So I put a where not exists clause in my insert statement and it will work fine when I am comparing strings and/or numbers however when I introduce Timestamps it begans to act funny.

So basically I did something like this:

INSERT INTO MY_TABLE ( COL1, COL2)
SELECT #col1#, #col2# FROM DUAL
WHERE NOT EXISTS
(SELECT 1 FROM MY_TABLE WHERE COL1 = #col1# AND COL2 = #col2#)

(The #col1# syntax is for iBATIS sql maps- consider it same as using ? )

When COL1 and COL2 are either strings and/or numbers this works as expected- no rows duplicated, however if either of the columns are a Timestamp it fails.It duplicates every row.

I tried converting the Timestamps to strings like this:

AND to_char(COL2, 'mm-dd-yyyy hh24:mi:ss') = to_char(#col2#, 'mm-dd-yyyy hh24:mi:ss')

but it still does not work- now it duplicates some but not all the rows. I also tried to do it:

AND COL2 = to_timestamp(#col2#, 'mm-dd-yyyy hh24:mi:ss')

where col2 now is a string representing the date,allowing Oracle to convert it to timestamp and compare it to existing timestamp col and it still isnt working.

any ideas?

 

[lozina]

The only thing that seems to work is if I make a separate column in the table to store the timestamp in string (varchar2) form, and then in my compare compare the string representations of the dates. This works, but why can't the timestamp compare work so I dont have to do such a awkward workaround?