Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
Good Morning All, I'm back from vacation and wanted to check in to see if there was any other ideas on this issue since I sent this reply on Oct 14th. Thanks, Mike - Original Message - From: 'Softtech Support' stwiz...@att.net [firebird-support] To: firebird-support@yahoogroups.com Sent: Tuesday, October 14, 2014 7:24 AM Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Greetings Set, I appreciate you joining in. Note: Because STATUS_DATE is a TimeStamp I modified your example for solution A to: 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 || ' 00:00:00' AND :V_END_DATE || ' 23:59:59' AND DCD.STATUS_CODE = 'B' AND AC.CLT_ID = :V_CLT_ID Solution A returned accurate results but was painfully slow. 09/01/2014 thru 09/30/2014 took 7.25 secs - 34 Count 01/01/2014 thru 09/30/2014 took 1 Min, 6.312 secs - 196 Count Here is the plan it used: PLAN JOIN (AC INDEX (REFCLIENT457),DCD INDEX (REFDEBT134,IX_DCD_STATUS_DATE_AND_CODE)) REFCLIENT457 is a FK to CLIENT which uses CLT_ID(Integer) for the PK REFDEBT134 is a FK to DEBT which uses ACCT_ID(Integer), DEBT_NO(SmallInt) for the PK IX_DCD_STATUS_DATE_AND_CODE is a new index I just added uses STATUS_DATE(TimeStamp) and STATUS_CODE(Char(1)) Solution B returned inaccurate results but was quick 09/01/2014 thru 09/30/2014 took 0.063 secs - 35 Count 01/01/2014 thru 09/30/2014 took 0.031 secs - 205 Count By changing solution B to the following I was able to determine it was not counting distinct records as in 167565-3-3 was listed twice for 09/01/2014 thru 09/30/2014 and similar duplicates for the YTD results. SELECT DCD.ACCT_ID, DCD.CASE_ID, DCD.DEBT_NO FROM DEBTOR_CASE_DEBT DCD WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE || ' 00:00:00' AND :V_END_DATE || ' 23:59:59' 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) ORDER BY 1,2,3 Here is the plan it used: PLAN (AC INDEX (PK_CASE)) Set, you mention The only (minor) thing lacking is a reason for you not wanting CLT_ID included... I'm not sure what you meant here. CLT_ID is found only in the ACCT_CASE and CLIENT tables. DEBTOR_CASE_DEBT does not include CLT_ID soas to normalize the data, thus the join from DEBTOR_CASE_DEBT to ACCT_CASE to use CLT_ID. Am I missing something? Thanks again to both Martijn and Set for your help, Mike PS: I'll be on vacation starting today thru next Monday, so may be slow to respond. - Original Message - From: Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] To: firebird-support@yahoogroups.com Sent: Tuesday, October 14, 2014 2:23 AM Subject: RE: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? ACCT_CASE: Case Management table ACCT_IDINTEGERNOT NULLPK CASE_IDSMALLINTNOT NULLPK CLT_IDINTEGERNOT NULLFK to CLIENT table Need this for the JOIN DEBTOR_CASE_DEBT: Allows for multiple PERSON's to be associated with a DEBT ACCT_IDINTEGERNOT NULLPK CASE_IDSMALLINTNOT NULLPK DEBT_NOSMALLINTNOT NULLPK PERSON_IDINTEGERNOT NULLPK STATUS_DATETIMESTAMPNOT NULL STATUS_CODECHAR(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
RE: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
ACCT_CASE: Case Management table ACCT_IDINTEGER 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_IDINTEGER 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
Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
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.tysv...@kreftregisteret.no [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_IDINTEGERNOT NULLPK CASE_IDSMALLINTNOT NULLPK CLT_IDINTEGERNOT NULLFK to CLIENT table Need this for the JOIN DEBTOR_CASE_DEBT: Allows for multiple PERSON's to be associated with a DEBT ACCT_IDINTEGERNOT NULLPK CASE_IDSMALLINTNOT NULLPK DEBT_NOSMALLINTNOT NULLPK PERSON_IDINTEGERNOT NULLPK STATUS_DATETIMESTAMPNOT NULL STATUS_CODECHAR(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.tysv...@kreftregisteret.no ++ 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
Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
Hello Mike, In the second query, you’re counting records and then do the DISTINCT, so the result is 32, and if you “distinct” that result, there’s only 1 record, with a value of 32. What you seem to want, is to COUNT(DISTINCT(...)) 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! From: mailto:firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 8:50 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Greetings All, Firebird 1.5.3 (Yes I know it is old) Using the following syntax with 09/01/14 and 09/04/14 for the parameters fetches 20 distinct records SELECT DISTINCT DCD.ACCT_ID, AC.CLT_ID 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' Using this syntax with 09/01/14 and 09/04/14 for the parameters fetches a count of 32 SELECT DISTINCT COUNT(DCD.PERSON_ID) 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' How do I accurately return the correct count using the COUNT() function? In this case it should beturn 20 not 32 Any ideas appreciated. Thanks, Mike
Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
Greetings Martijn, Just happening to be testing this in Database Workbench. Have used this product for years and just love it. Thanks for you reply. So my second SQL should have been as follows? It results in an error Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 34 , SELECT COUNT(DISTINCT DCD.ACCT_ID, AC.CLT_ID) 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' So not sure if this is how I should have done it, but it appears to work as it returns 20 SELECT COUNT(DISTINCT DCD.ACCT_ID || AC.CLT_ID) 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' Is that correct? Mike - Original Message - From: 'Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support] To: firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 1:56 PM Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Hello Mike, In the second query, you’re counting records and then do the DISTINCT, so the result is 32, and if you “distinct” that result, there’s only 1 record, with a value of 32. What you seem to want, is to COUNT(DISTINCT(...)) 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! From: mailto:firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 8:50 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Greetings All, Firebird 1.5.3 (Yes I know it is old) Using the following syntax with 09/01/14 and 09/04/14 for the parameters fetches 20 distinct records SELECT DISTINCT DCD.ACCT_ID, AC.CLT_ID 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' Using this syntax with 09/01/14 and 09/04/14 for the parameters fetches a count of 32 SELECT DISTINCT COUNT(DCD.PERSON_ID) 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' How do I accurately return the correct count using the COUNT() function? In this case it should beturn 20 not 32 Any ideas appreciated. Thanks, Mike --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com
Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
Hello Mike, Just happening to be testing this in Database Workbench. Have used this product for years and just love it. Thank you, that’s good to hear. Thanks for you reply. So my second SQL should have been as follows? It results in an error Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 34 , COUNT only works on single column or *, so using COUNT on two columns won’t work. I’m not sure what you’re trying to DISTINCT here, as the previous query counted PERSON_ID values in the result set. SELECT COUNT(DISTINCT DCD.ACCT_ID, AC.CLT_ID) 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' So not sure if this is how I should have done it, but it appears to work as it returns 20 SELECT COUNT(DISTINCT DCD.ACCT_ID || AC.CLT_ID) 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' Is that correct? Don’t think this will work for all combinations of ACCT_ID and CLT_ID, imagine: 101 || 1 is the same as 10 || 11 Question is: what exactly are you trying to get from your query? 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: mailto:m.ton...@upscene.com [firebird-support] To: firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 1:56 PM Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Hello Mike, In the second query, you’re counting records and then do the DISTINCT, so the result is 32, and if you “distinct” that result, there’s only 1 record, with a value of 32. What you seem to want, is to COUNT(DISTINCT(...)) 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! From: mailto:firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 8:50 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Greetings All, Firebird 1.5.3 (Yes I know it is old) Using the following syntax with 09/01/14 and 09/04/14 for the parameters fetches 20 distinct records SELECT DISTINCT DCD.ACCT_ID, AC.CLT_ID 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' Using this syntax with 09/01/14 and 09/04/14 for the parameters fetches a count of 32 SELECT DISTINCT COUNT(DCD.PERSON_ID) 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' How do I accurately return the correct count using the COUNT() function? In this case it should beturn 20 not 32 Any ideas appreciated.
Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
Hi Martijn, I knew I was going to get in trouble by not providing enought information as I thought by proving less it would be just a little bit clearer to understand, my bad... Let's start over with an simplified explanation of the tables: ACCT_CASE: Case Management table ACCT_IDINTEGERNOT NULLPK CASE_IDSMALLINTNOT NULLPK CLT_IDINTEGERNOT NULLFK to CLIENT table Need this for the JOIN ... DEBTOR_CASE_DEBT: Allows for multiple PERSON's to be associated with a DEBT ACCT_IDINTEGERNOT NULLPK CASE_IDSMALLINTNOT NULLPK DEBT_NOSMALLINTNOT NULLPK PERSON_IDINTEGERNOT NULLPK STATUS_DATETIMESTAMPNOT NULL STATUS_CODECHAR(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... Thanks so much, Mike - Original Message - From: 'Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support] To: firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 2:20 PM Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Hello Mike, Just happening to be testing this in Database Workbench. Have used this product for years and just love it. Thank you, that’s good to hear. Thanks for you reply. So my second SQL should have been as follows? It results in an error Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 34 , COUNT only works on single column or *, so using COUNT on two columns won’t work. I’m not sure what you’re trying to DISTINCT here, as the previous query counted PERSON_ID values in the result set. SELECT COUNT(DISTINCT DCD.ACCT_ID, AC.CLT_ID) 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' So not sure if this is how I should have done it, but it appears to work as it returns 20 SELECT COUNT(DISTINCT DCD.ACCT_ID || AC.CLT_ID) 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' Is that correct? Don’t think this will work for all combinations of ACCT_ID and CLT_ID, imagine: 101 || 1 is the same as 10 || 11 Question is: what exactly are you trying to get from your query? 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: mailto:m.ton...@upscene.com [firebird-support] To: firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 1:56 PM Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Hello Mike, In the second query, you’re counting records and then do the DISTINCT, so the result is 32, and if you “distinct” that result, there’s only 1 record, with a value of 32. What you seem to want, is to COUNT(DISTINCT(...)) 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! From: mailto:firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 8:50 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Greetings All, Firebird 1.5.3 (Yes I know it is old) Using the following syntax with 09/01/14 and 09/04/14 for
Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
Hello Mike, So you want to count records in DEBT for a specific CLT_ID. Now, in the DEBT table, there are records for multiple PERSONs, but what about ACCT_ID, CASE_ID and DEBT_NO, which of these or what combination are unique with regard to each ACCT_CASE? If none, you would need a derived table, but these are available in Firebird 2 onward. The alternative is to create a VIEW for the DISTINCT query and COUNT on that. 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! From: mailto:firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 9:57 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Hi Martijn, I knew I was going to get in trouble by not providing enought information as I thought by proving less it would be just a little bit clearer to understand, my bad... Let's start over with an simplified explanation of the tables: ACCT_CASE: Case Management table ACCT_IDINTEGERNOT NULLPK CASE_IDSMALLINTNOT NULLPK CLT_IDINTEGERNOT NULLFK to CLIENT table Need this for the JOIN ... DEBTOR_CASE_DEBT: Allows for multiple PERSON's to be associated with a DEBT ACCT_IDINTEGERNOT NULLPK CASE_IDSMALLINTNOT NULLPK DEBT_NOSMALLINTNOT NULLPK PERSON_IDINTEGERNOT NULLPK STATUS_DATETIMESTAMPNOT NULL STATUS_CODECHAR(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... Thanks so much, Mike - Original Message - From: mailto:m.ton...@upscene.com [firebird-support] To: firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 2:20 PM Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Hello Mike, Just happening to be testing this in Database Workbench. Have used this product for years and just love it. Thank you, that’s good to hear. Thanks for you reply. So my second SQL should have been as follows? It results in an error Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 34 , COUNT only works on single column or *, so using COUNT on two columns won’t work. I’m not sure what you’re trying to DISTINCT here, as the previous query counted PERSON_ID values in the result set. SELECT COUNT(DISTINCT DCD.ACCT_ID, AC.CLT_ID) 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' So not sure if this is how I should have done it, but it appears to work as it returns 20 SELECT COUNT(DISTINCT DCD.ACCT_ID || AC.CLT_ID) 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' Is that correct? Don’t think this will work for all combinations of ACCT_ID and CLT_ID, imagine: 101 || 1 is the same as 10 || 11 Question is: what exactly are you trying to get from your query? 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: mailto:m.ton...@upscene.com [firebird-support] To: firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 1:56 PM Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Hello Mike, In
Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
Hi Martijn, I want to count the number of records in the DEBTOR_CASE_DEBT table (not DEBT) where the ACCT_ID, CASE_ID and DEBT_NO are distinct and specific to CLT_ID. Is this possible in v1.5.3? If not and I need to create a view and COUNT() that, can you provide an example or tell me where to find info on doing that? Mike - Original Message - From: 'Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support] To: firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 3:06 PM Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Hello Mike, So you want to count records in DEBT for a specific CLT_ID. Now, in the DEBT table, there are records for multiple PERSONs, but what about ACCT_ID, CASE_ID and DEBT_NO, which of these or what combination are unique with regard to each ACCT_CASE? If none, you would need a derived table, but these are available in Firebird 2 onward. The alternative is to create a VIEW for the DISTINCT query and COUNT on that. 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! From: mailto:firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 9:57 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Hi Martijn, I knew I was going to get in trouble by not providing enought information as I thought by proving less it would be just a little bit clearer to understand, my bad... Let's start over with an simplified explanation of the tables: ACCT_CASE: Case Management table ACCT_IDINTEGERNOT NULLPK CASE_IDSMALLINTNOT NULLPK CLT_IDINTEGERNOT NULLFK to CLIENT table Need this for the JOIN ... DEBTOR_CASE_DEBT: Allows for multiple PERSON's to be associated with a DEBT ACCT_IDINTEGERNOT NULLPK CASE_IDSMALLINTNOT NULLPK DEBT_NOSMALLINTNOT NULLPK PERSON_IDINTEGERNOT NULLPK STATUS_DATETIMESTAMPNOT NULL STATUS_CODECHAR(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... Thanks so much, Mike - Original Message - From: mailto:m.ton...@upscene.com [firebird-support] To: firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 2:20 PM Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Hello Mike, Just happening to be testing this in Database Workbench. Have used this product for years and just love it. Thank you, that’s good to hear. Thanks for you reply. So my second SQL should have been as follows? It results in an error Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 34 , COUNT only works on single column or *, so using COUNT on two columns won’t work. I’m not sure what you’re trying to DISTINCT here, as the previous query counted PERSON_ID values in the result set. SELECT COUNT(DISTINCT DCD.ACCT_ID, AC.CLT_ID) 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' So not sure if this is how I should have done it, but it appears to work as it returns 20 SELECT COUNT(DISTINCT DCD.ACCT_ID || AC.CLT_ID) 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
Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
Hello Mike, No doubt our favorite SQL wizard from Norway, SET, will join in, perhaps he has an idea. As for my answer with the VIEW, now that I think of it, you would need the date and status columns as well, so the DISTINCT is different. Darn. You could use a SELECT-able Stored Procedure, return DCD.ACCT_ID, DCD.CASE_ID, DCD.DEBT_NO and pass the STATUS_CODE, CLT_ID and DATEs as input parameters and COUNT the results from the query, which include the DISTINCT inside the procedure. Given that the routine would only return a few rows, it won’t be slow, I guess. 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! From: mailto:firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 10:19 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Hi Martijn, I want to count the number of records in the DEBTOR_CASE_DEBT table (not DEBT) where the ACCT_ID, CASE_ID and DEBT_NO are distinct and specific to CLT_ID. Is this possible in v1.5.3? If not and I need to create a view and COUNT() that, can you provide an example or tell me where to find info on doing that? Mike - Original Message - From: mailto:m.ton...@upscene.com [firebird-support] To: firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 3:06 PM Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Hello Mike, So you want to count records in DEBT for a specific CLT_ID. Now, in the DEBT table, there are records for multiple PERSONs, but what about ACCT_ID, CASE_ID and DEBT_NO, which of these or what combination are unique with regard to each ACCT_CASE? If none, you would need a derived table, but these are available in Firebird 2 onward. The alternative is to create a VIEW for the DISTINCT query and COUNT on that. 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! From: mailto:firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 9:57 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Hi Martijn, I knew I was going to get in trouble by not providing enought information as I thought by proving less it would be just a little bit clearer to understand, my bad... Let's start over with an simplified explanation of the tables: ACCT_CASE: Case Management table ACCT_IDINTEGERNOT NULLPK CASE_IDSMALLINTNOT NULLPK CLT_IDINTEGERNOT NULLFK to CLIENT table Need this for the JOIN ... DEBTOR_CASE_DEBT: Allows for multiple PERSON's to be associated with a DEBT ACCT_IDINTEGERNOT NULLPK CASE_IDSMALLINTNOT NULLPK DEBT_NOSMALLINTNOT NULLPK PERSON_IDINTEGERNOT NULLPK STATUS_DATETIMESTAMPNOT NULL STATUS_CODECHAR(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... Thanks so much, Mike - Original Message - From: mailto:m.ton...@upscene.com [firebird-support] To: firebird-support@yahoogroups.com Sent: Monday, October 13, 2014 2:20 PM Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Hello Mike, Just happening to be testing this in Database Workbench. Have used this product for years and just love it. Thank you, that’s good to hear. Thanks for you reply. So my second SQL should have been as follows? It results in an error Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 34 , COUNT
