Good morning SET. ;)

I thought of the concatenation trick, but found it ugly as a solution, your
second query
is cleaner, I think.


With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!



-----Original Message-----
From: Svein Erling Tysvær svein.erling.tysvaer@...
[firebird-support]
Sent: Tuesday, October 14, 2014 9:23 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] How do I return an accurate COUNT(*) when a
JOIN is involved?

>ACCT_CASE: Case Management table
>ACCT_ID INTEGER NOT NULL PK
>CASE_ID SMALLINT NOT NULL PK
>CLT_ID INTEGER NOT NULL FK to CLIENT table << Need this for the
>JOIN

>DEBTOR_CASE_DEBT: Allows for multiple PERSON's to be associated with a
>DEBT
>ACCT_ID INTEGER NOT NULL PK
>CASE_ID SMALLINT NOT NULL PK
>DEBT_NO SMALLINT NOT NULL PK
>PERSON_ID INTEGER NOT NULL PK
>STATUS_DATE TIMESTAMP NOT NULL
>STATUS_CODE CHAR(1) NOT NULL

>What am I attempting to do? I need to know how many records are in the
>DEBTOR_CASE_DEBT table that have a STATUS_DATE between '09/01/14' and
>'09/30/14'
>and the STATUS_CODE = 'B" (Bankruptcy Filed) and is for a specific CLT_ID
>(thus the join to ACCT_CASE to use CLT_ID). I do not want to include the
>PERSON_ID when fetching a COUNT() of the record, I only need to know how
>many debts are in this status for the client. So only concerned with
>ACCT_ID,
>CASE_ID and DEBT_NO.
>
>So this SQL will return the correct number of records, now I just have to
>figure out how to return a count in one record.
>
> SELECT 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
>
>Did I provide enough information this time? If not feel free to ask...

This is close to a perfect problem description, Mike, well done! The only
(minor) thing lacking is a reason for you not wanting CLT_ID included...

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)

Myself, I generally prefer to have single field primary keys, one benefit of
this is that you can use solution a) without having to do tricks with
concatenation.

HTH,
Set


------------------------------------
Posted by: =?utf-8?B?U3ZlaW4gRXJsaW5nIFR5c3bDpnI=?=
<Svein.Erling.Tysvaer@...>
------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu
there.

Also search the knowledgebases at
http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links