Inserting / not exist problem with date compare

[MrNobody <to...@pepsi.com>]

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#)

When COL1 and COL2 are either strings and/or numbers this works as expected,
however if one is 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')

and what happens now is only SOME rows get duplicated, not all of them like
before. Very bizarre...
-- 
View this message in context: http://www.nabble.com/Inserting---not-exist-problem-with-date-compare-tf4837196.html#a13838678
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.

Re: Inserting / not exist problem with date compare

[Nathan Maves <na...@gmail.com>]

well a few things catch my eye.

use $ for the first parameters in the sql.

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#)

and second, if you are using timestamps you might have to specify the jdbc
type

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

The second will only for for timestamps now and not normal strings.

Nathan

On Nov 19, 2007 8:56 AM, MrNobody <to...@pepsi.com> wrote:

>
> 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#)
>
> When COL1 and COL2 are either strings and/or numbers this works as
> expected,
> however if one is 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')
>
> and what happens now is only SOME rows get duplicated, not all of them
> like
> before. Very bizarre...
> --
> View this message in context:
> http://www.nabble.com/Inserting---not-exist-problem-with-date-compare-tf4837196.html#a13838678
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>