At 05:57 PM 7/10/2004 -0400, you wrote:

>The following SQL returns zero when executed through Jaybird, and
>returns 75 if executed through Database Workbench. There are no
>exceptions thrown at any point, and the classes being passed to
>setObject() are confirmed as java.sql.Date and java.lang.String.
>
>I'm using FB 1.5.1 and JB 1.5.1 (Sep 17 version).
>
>
>SELECT COUNT(CPR1.CLAIMSTS) - (SELECT COUNT(CPR2.CLAIMSTS) FROM
>CLAIMSPAIDREVERSED CPR2 WHERE
>CPR2.DATESBM>=?
>AND CPR2.DATESBM<=?
>AND CPR2.CLAIMSTS='X' AND CPR2.ACCOUNTID=?)
> FROM CLAIMSPAIDREVERSED CPR1 WHERE CPR1.DATESBM>=?
> AND CPR1.DATESBM<=?
> AND CPR1.CLAIMSTS='P' AND CPR1.ACCOUNTID=?

If this really is the query, then it should produce an invalid result, no
matter where you run it.

You are querying two tables without a join or a subquery. If it actually
runs at all (which presumably it does) then you are getting some kind of
Cartesian product to which the where clause cannot be applied. As for your
calculation, it is giving the result of subtracting the total number of
rows from the total number of rows in the same table, so it's probably the
only part of the result that you could rely on to be correct -- even though
it's not what you intended.

Depending on what your intention is, your query should be expressed
something like this:

SELECT
(COUNT(CPR1.CLAIMSTS) - (SELECT COUNT(CPR2.CLAIMSTS)) as something
FROM CLAIMSPAIDREVERSED CPR2
join claimspaidreversed cpr1
on cpr1.accountid = cpr2.accountid /* or whatever the join criteria
actually are */
WHERE
CPR2.DATESBM>=?
AND CPR2.DATESBM<=?
AND CPR2.CLAIMSTS='X' AND CPR2.ACCOUNTID=?)
/* FROM CLAIMSPAIDREVERSED CPR1 WHERE */
and CPR1.DATESBM>=?
AND CPR1.DATESBM<=?
AND CPR1.CLAIMSTS='P' /* AND CPR1.ACCOUNTID=? */

Any further problems with the SQL, ask on Firebird-support.

Helen