>>I can think of two possible solutions:
>>
>>a)
>> SELECT COUNT(DISTINCT DCD.ACCT_ID||'-'||DCD.CASE_ID||'-'||DCD.DEBT_NO)
>> FROM DEBTOR_CASE_DEBT DCD
>> JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
>> AND AC.CASE_ID = DCD.CASE_ID
>> WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
>> AND DCD.STATUS_CODE = 'B'
>> AND AC.CLT_ID = :V_CLT_ID
>>
>>b)
>> SELECT COUNT(*)
>> FROM DEBTOR_CASE_DEBT DCD
>> WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
>> AND DCD.STATUS_CODE = 'B'
>> AND EXISTS(SELECT * FROM ACCT_CASE AC
>> WHERE AC.ACCT_ID = DCD.ACCT_ID
>> AND AC.CASE_ID = DCD.CASE_ID
>> AND AC.CLT_ID = :V_CLT_ID)

>Good morning SET. ;)
>
>I thought of the concatenation trick, but found it ugly as a solution, your second query is cleaner, I think.

Morning, Martijn!

Agree that the second solution is cleaner, but indexes and the table content could mean a difference in performance (if there are billions of records in DCD with appropriate STATUS_DATE and STATUS_CODE, whereas starting from the CLT_ID requirement and joining to ACCT_ID & CASE_ID only involves a handful of them, then solution a) would be tempting).

Firebird 2.5 would have provided Mike with more options, but not necessarily better ones.

Set