Hi,

> 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?

SELECT
COUNT(CASE WHEN CPR1.CLAIMSTS = 'P' THEN 1 ELSE 0 END) -
COUNT(CASE WHEN CPR1.CLAIMSTS = 'X' THEN 1 ELSE 0 END)
FROM
CLAIMSPAIDREVERSED CPR1
WHERE
CPR1.DATESBM >= ?
AND CPR1.DATESBM <= ?
AND CPR1.ACCOUNTID = ?

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info