The value for ClaimSts is always P or X. The intention is to find out
the difference between the count of rows with P and the count of rows
with X, filtered by various criteria such as a given DateSbm and/or
AccountId.
I assumed the original SQL was a subquery.
The problem with the suggested SQL is that the ClaimSts column is part
of the primary key, so the join always produces an empty set once the
where clauses are introduced.
Any suggestions on a correct SQL statement?

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Thursday, October 07, 2004 7:39 PM
To: Firebird-Java@yahoogroups.com
Subject: Re: [Firebird-Java] Query returns invalid result


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





Yahoo! Groups Links