I'm using it with Firebird 1.5:

private static final String SQL_AUDIT_ADD =
"INSERT INTO CLAIMAUDIT
(RXCLAIMNBR,RXCLAIMSEQ,CLAIMSTS,AUDITSTATUS,REASON) " +
"SELECT DISTINCT ?,?,'P',0,? " +
"FROM CLAIMAUDIT " +
"WHERE NOT EXISTS" +
" (SELECT CLMSEQNBR FROM CLAIMSPAIDREVERSED WHERE
RXCLAIMNBR=? AND CLMSEQNBR<=? and CLAIMSTS='X') " +
"AND NOT EXISTS" + // TODO: change reason to reasoncode
" (SELECT ID FROM CLAIMAUDIT WHERE RXCLAIMNBR=? AND
RXCLAIMSEQ=? AND REASON=?)";

You're right, the syntax I presented is incorrect. It needs to be
reformed to match what I have above. Sorry for the confusion.

-----Original Message-----
From: Roman Rokytskyy [mailto:rrokytskyy@...]
Sent: Wednesday, January 12, 2005 2:06 PM
To: Firebird-Java@yahoogroups.com
Subject: Re: [Firebird-Java] Re: Transaction concurrency

> insert into reservations(id,starttime,endtime) values
> (_id,_start,_end) where not exists (
> select id from reservations
> where (_start <= starttime and _end > starttime)
> or (_start > starttime and _end < endtime)
> or (_start < endtime and _end > starttime))
>
> I'm pretty sure the where statement is wrong.

Firebird does not support such INSERT statements (btw, which database
supports this?!).

Roman




Yahoo! Groups Links