[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?
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?
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 0
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_BEG
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 >
[firebird-support] How do I count the number of duplicate rows in a table?
Greetings All, Firebird 1.5.3 Should be elementary but, I'm drawing a blank on how to accomplish this. I have a table (ACCT_CASE_COURT) that contains these fields (among others): ACCT_CASE_COURT_IDINTEGERNOT NULLPK ACCT_IDINTEGERNOT NULL CASE_IDSMALLINTNOT NULL CASE_NUMBERVARCHAR(20)NOT NULL ... How do I form a SQL Select statement that would contain CASE_NUMBER in the first column (sorted) and number of times that the case number is found in the table in the second column (CNT)? Any help truely 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 count the number of duplicate rows in a table?
Hi Martijn, I did finally come up with this SELECT DISTINCT(ACC.CASE_NUMBER), (SELECT COUNT(ACC2.ACCT_CASE_COURT_ID) FROM ACCT_CASE_COURT ACC2 WHERE ACC2.CASE_NUMBER = ACC.CASE_NUMBER) AS CNT FROM ACCT_CASE_COURT ACC GROUP BY 1 Nut, I like yours better. Now I need to exclude any that have a count less than 2 and I know I have to use HAVING but have not got it figured out yet. Mike - Original Message - From: 'Martijn Tonies (Upscene Productions)' m.ton...@upscene.com [firebird-support] To: firebird-support@yahoogroups.com Sent: Tuesday, October 28, 2014 7:18 AM Subject: Re: [firebird-support] How do I count the number of duplicate rows in a table? select count(case_number) as counted, case_number from ACCT_CASE_COURT group by case_number ? 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: Tuesday, October 28, 2014 1:13 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] How do I count the number of duplicate rows in a table? Greetings All, Firebird 1.5.3 Should be elementary but, I'm drawing a blank on how to accomplish this. I have a table (ACCT_CASE_COURT) that contains these fields (among others): ACCT_CASE_COURT_IDINTEGERNOT NULLPK ACCT_IDINTEGERNOT NULL CASE_IDSMALLINTNOT NULL CASE_NUMBERVARCHAR(20)NOT NULL ... How do I form a SQL Select statement that would contain CASE_NUMBER in the first column (sorted) and number of times that the case number is found in the table in the second column (CNT)? Any help truely appreciated. Thanks, Mike -- This email is free from viruses and malware because avast! Antivirus protection is active. --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com
Re: [firebird-support] How do I count the number of duplicate rows in a table?
Got IT... Love this group. SELECT CASE_NUMBER, COUNT(*) CNT FROM ACCT_CASE_COURT GROUP BY CASE_NUMBER HAVING COUNT(*) > 2 Thanks All, Mike - Original Message - From: Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] To: firebird-support@yahoogroups.com Sent: Tuesday, October 28, 2014 7:23 AM Subject: Re: [firebird-support] How do I count the number of duplicate rows in a table? >Greetings All, > >Firebird 1.5.3 > >Should be elementary but, I'm drawing a blank on how to accomplish this. > >I have a table (ACCT_CASE_COURT) that contains these fields (among others): > >ACCT_CASE_COURT_ID INTEGER NOT NULL PK >ACCT_ID INTEGER NOT NULL >CASE_ID SMALLINT NOT NULL >CASE_NUMBER VARCHAR(20) NOT NULL >... > >How do I form a SQL Select statement that would contain CASE_NUMBER in the first column (sorted) and >number of times that the case number is found in the table in the second column (CNT)? From your description, I'd say the answer is simply SELECT CASE_NUMBER, COUNT(*) CNT FROM ACCT_CASE_COURT GROUP BY CASE_NUMBER (feel free to add ORDER BY CASE_NUMBER, I didn't since all existing Firebird versions happens to do it implicitly when having GROUP BY). If this is not the answer, then please try to formulate your question differently so that we understand what you want. HTH, Set --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com
Re: [firebird-support] Add 1 minute to a TimeStamp
In the code provided below I meant to type 2012 not 201 - Original Message - From: 'Softtech Support' stwiz...@att.net [firebird-support] To: firebird-support@yahoogroups.com Sent: Tuesday, March 10, 2015 3:01 PM Subject: Re: [firebird-support] Add 1 minute to a TimeStamp Firebird v1.5.3 Does anyone know what's going on here? DECLARE VARIABLE tsTranCreateDate TimeStamp; DECLARE VARIABLE tsTest TimeStamp; Starting Value: tsTranCreateDate = 10/31/201 7:50:16 AM Two days: tsTest = (:tsTranCreateDate - (2.0)); Correctly returns 10/29/201 7:50:16 AM One hour: tsTest = (:tsTranCreateDate - (1.0/24.0)); Incorrectly returns 10/31/201 6:52:40 AM One minute: tsTest = (:tsTranCreateDate - (1.0/1440.0)); Incorrectly returns 10/31/201 7:50:16 AM One second: tsTest = (:tsTranCreateDate - (1.0/86400.0)); Incorrectly returns 10/31/201 7:50:16 AM Subtract 2 days works fine. Subtract 1 hour is wrong. Subtract one minute or 1 second does nothing Any ideas? Mike - Original Message ----- From: 'Softtech Support' stwiz...@att.net [firebird-support] To: Firebird Support Sent: Tuesday, March 10, 2015 2:22 PM Subject: [firebird-support] Add 1 minute to a TimeStamp Greetings All, What is the best way in a stored procedure to add a minute or a few seconds to a TimeStamp? Thanks, Mike This email has been checked for viruses by Avast antivirus software. www.avast.com This email has been checked for viruses by Avast antivirus software. www.avast.com --- This email has been checked for viruses by Avast antivirus software. http://www.avast.com
Re: [firebird-support] Add 1 minute to a TimeStamp
Turns out that firebird uses the precision of the used decimal, so instead of
(1.0/24.0) I used (1./24.0)
Here is a version that works correctly:
DECLARE VARIABLE tsTranCreateDate TimeStamp;
DECLARE VARIABLE tsTest TimeStamp;
Starting Value:
tsTranCreateDate = 10/31/2012 7:50:16 AM
Two days:
tsTest = (:tsTranCreateDate - (2.0));
Correctly returns 10/29/2012 7:50:16 AM
One hour:
tsTest = (:tsTranCreateDate - (1.0/24.0));
Correctly returns 10/31/2012 6:50:16 AM
One minute:
tsTest = (:tsTranCreateDate - (1.0/1440.0));
Correctly returns 10/31/2012 7:49:16 AM
One second:
tsTest = (:tsTranCreateDate - (1.0/86400.0));
Incorrectly returns 10/31/2012 7:50:15 AM
Mike
- Original Message -
From: 'Softtech Support' stwiz...@att.net [firebird-support]
To: firebird-support@yahoogroups.com
Sent: Tuesday, March 10, 2015 3:02 PM
Subject: Re: [firebird-support] Add 1 minute to a TimeStamp
In the code provided below I meant to type 2012 not 201
- Original Message -
From: 'Softtech Support' stwiz...@att.net [firebird-support]
To: firebird-support@yahoogroups.com
Sent: Tuesday, March 10, 2015 3:01 PM
Subject: Re: [firebird-support] Add 1 minute to a TimeStamp
Firebird v1.5.3
Does anyone know what's going on here?
DECLARE VARIABLE tsTranCreateDate TimeStamp;
DECLARE VARIABLE tsTest TimeStamp;
Starting Value:
tsTranCreateDate = 10/31/201 7:50:16 AM
Two days:
tsTest = (:tsTranCreateDate - (2.0));
Correctly returns 10/29/201 7:50:16 AM
One hour:
tsTest = (:tsTranCreateDate - (1.0/24.0));
Incorrectly returns 10/31/201 6:52:40 AM
One minute:
tsTest = (:tsTranCreateDate - (1.0/1440.0));
Incorrectly returns 10/31/201 7:50:16 AM
One second:
tsTest = (:tsTranCreateDate - (1.0/86400.0));
Incorrectly returns 10/31/201 7:50:16 AM
Subtract 2 days works fine. Subtract 1 hour is wrong. Subtract one minute
or 1 second does nothing
Any ideas?
Mike
- Original Message -----
From: 'Softtech Support' stwiz...@att.net [firebird-support]
To: Firebird Support
Sent: Tuesday, March 10, 2015 2:22 PM
Subject: [firebird-support] Add 1 minute to a TimeStamp
Greetings All,
What is the best way in a stored procedure to add a minute or a few
seconds to a TimeStamp?
Thanks,
Mike
--
This email has been checked for viruses by Avast antivirus
software.
www.avast.com
--
This email has been checked for viruses by Avast antivirus
software.
www.avast.com
This email has been checked for viruses by Avast antivirus
software.
www.avast.com
---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com
[firebird-support] Add 1 minute to a TimeStamp
Greetings All, What is the best way in a stored procedure to add a minute or a few seconds to a TimeStamp? Thanks, Mike --- This email has been checked for viruses by Avast antivirus software. http://www.avast.com
Re: [firebird-support] Add 1 minute to a TimeStamp
Firebird v1.5.3 Does anyone know what's going on here? DECLARE VARIABLE tsTranCreateDate TimeStamp; DECLARE VARIABLE tsTest TimeStamp; Starting Value: tsTranCreateDate = 10/31/201 7:50:16 AM Two days: tsTest = (:tsTranCreateDate - (2.0)); Correctly returns 10/29/201 7:50:16 AM One hour: tsTest = (:tsTranCreateDate - (1.0/24.0)); Incorrectly returns 10/31/201 6:52:40 AM One minute: tsTest = (:tsTranCreateDate - (1.0/1440.0)); Incorrectly returns 10/31/201 7:50:16 AM One second: tsTest = (:tsTranCreateDate - (1.0/86400.0)); Incorrectly returns 10/31/201 7:50:16 AM Subtract 2 days works fine. Subtract 1 hour is wrong. Subtract one minute or 1 second does nothing Any ideas? Mike - Original Message - From: 'Softtech Support' stwiz...@att.net [firebird-support] To: Firebird Support Sent: Tuesday, March 10, 2015 2:22 PM Subject: [firebird-support] Add 1 minute to a TimeStamp Greetings All, What is the best way in a stored procedure to add a minute or a few seconds to a TimeStamp? Thanks, Mike -- This email has been checked for viruses by Avast antivirus software. www.avast.com --- This email has been checked for viruses by Avast antivirus software. http://www.avast.com
[firebird-support] Connection rejected by remote interface
Greetings All, Setting up a new Windows 7 computer and installed Delphi XE5 and Firebird 1.5.6 I am able to open and compile my Delphi source just fine. When I try to connect to the database using the TIBDatabase component I receive the "connection rejected by remote interface" error FBCLIENT.DLL is in the Windows\SysWOW64 folder, so is GDS32.DLL I thought if I remove the GDS32.DLL then it would find the FBCLIENT.DLL and use it, bad idea as Delphi complains that it is missing when you try to open the source and then will proptly delete the TIBDatabase component. Delphi XE5 and Firebird 1.5.3 worked just fine with the TIBQuery, TIBDatabase, and TIBTransaction on Windows XP I'm stuck, any ideas what I need to do to fix this? Mike --- This email has been checked for viruses by Avast antivirus software. http://www.avast.com
Re: [firebird-support] Connection rejected by remote interface
Guys, I have figured out the issue. I uninstalled the Interbase app that was installed with Delphi (I should have deselected that option), cleared any remaining folders, and removed FBCLIENT.DLL and GDS32.DLL from the SysWOW64 directory, then reinstalled FB this time with the "Generate client library as GDS32.DLL for legacy app support?" selected. The GDS32.DLL is required to use the Interbase components in Delphi. All is well now and thought I should let others know what I had to do. Mike - Original Message - From: 'Softtech Support' stwiz...@att.net [firebird-support] To: firebird-support@yahoogroups.com Sent: Friday, April 17, 2015 8:21 AM Subject: [firebird-support] Connection rejected by remote interface Greetings All, Setting up a new Windows 7 computer and installed Delphi XE5 and Firebird 1.5.6 I am able to open and compile my Delphi source just fine. When I try to connect to the database using the TIBDatabase component I receive the "connection rejected by remote interface" error FBCLIENT.DLL is in the Windows\SysWOW64 folder, so is GDS32.DLL I thought if I remove the GDS32.DLL then it would find the FBCLIENT.DLL and use it, bad idea as Delphi complains that it is missing when you try to open the source and then will proptly delete the TIBDatabase component. Delphi XE5 and Firebird 1.5.3 worked just fine with the TIBQuery, TIBDatabase, and TIBTransaction on Windows XP I'm stuck, any ideas what I need to do to fix this? Mike -- This email has been checked for viruses by Avast antivirus software. www.avast.com --- This email has been checked for viruses by Avast antivirus software. http://www.avast.com
[firebird-support] Soc Sec No comparison using Firebird
Greetings All, I would like to pass into a stored procedure two social security numbers for comparison and have the result tell me if one character has changed, or if two characters were switched or if it does not compare at all. Has anyone done anything like this they could share? Or is it even possible? Thoughts? Thanks, Mike . --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
Re: [firebird-support] Soc Sec No comparison using Firebird
Thanks Svein I'll dig into this to see if something like this might work. Mike - Original Message - From: Svein Erling Tysværsvein.erling.tysv...@kreftregisteret.no [firebird-support] To: firebird-support@yahoogroups.com Sent: Wednesday, June 24, 2015 3:06 PM Subject: Re: [firebird-support] Soc Sec No comparison using Firebird >Greetings All, > >I would like to pass into a stored procedure two social security numbers for comparison and have the result tell me if one character has changed, >or if two characters were switched or if it does not compare at all. > >Has anyone done anything like this they could share? Or is it even possible? Never done anything similar before, but strictly speaking, the following would answer your question: execute block (ss1 varchar(20) = :ss1, ss2 varchar(20) = :ss2) returns (comparison varchar(50)) as declare variable maxlen integer; declare variable curpos integer; declare variable ss1a char(1); declare variable ss1b char(1); declare variable ss2a char(1); declare variable ss2b char(1); begin Comparison = ''; maxlen = character_length(ss1); if (maxlen < character_length(ss2)) then maxlen = character_length(ss2); if (ss1 = ss2) then comparison = 'Equal'; if (comparison = '' and (ss1 starting ss2 or ss2 starting ss1)) then comparison = 'Substring'; if (comparison = '') then begin curpos = 1; while (curpos <= maxlen) do begin ss1a = substring(ss1 from curpos for 1); ss1b = substring(ss1 from curpos+1 for 1); ss2a = substring(ss2 from curpos for 1); ss2b = substring(ss2 from curpos+1 for 1); if (ss1a is distinct from ss2a) then begin if (comparison > '') then comparison = 'Differ more than one character'; if (comparison='') then begin if (ss1a = ss2b and ss1b = ss2a) then begin comparison = 'swapped'; curpos = curpos+1; end if (comparison='') then comparison = 'One character differs'; end end curpos = curpos+1; end end suspend; end I say strictly speaking, because whereas this would catch the difference between 'Hello' and 'Helol' or 'Hello' and 'Hel1o', it wouldn't catch additional or missing characters like 'Hello' vs 'Helo'. Should be fixable, but I'll leave that task for you (I'd expect you to need curpos1 and curpos2). Having said all this, it wouldn't surprise me if there existed a UDF that did something similar to my execute block, I simply don't know since I've never needed one. HTH, Set --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
Re: [firebird-support] Soc Sec No comparison using Firebird
Bogdan, thanks for the reply. My version of Firebird v1.5.3 complains about the variable "i" in this line if (substring(x1 from i for 1) <> substring(x2 from i for 1)) then So I'm guessing that later versions of Firebird allow for variables in substring? Thanks, Mike - Original Message - From: 'Bogdan' bog...@mordicom.si [firebird-support] To: firebird-support@yahoogroups.com Sent: Thursday, June 25, 2015 5:39 AM Subject: RE: [firebird-support] Soc Sec No comparison using Firebird create procedure social_number_comparison ( i1 varchar(20), i2 varchar(20) ) returns ( result varchar(100) ) as declare variable x1 char(20); declare variable x2 char(20); declare variable e smallint; declare variable e1 smallint; declare variable e2 smallint; declare variable i smallint; begin i = 0; x1 = i1; x2 = i2; e = 0; while (i < 20 and e < 3) do begin i = i + 1; if (substring(x1 from i for 1) <> substring(x2 from i for 1)) then begin e = e + 1; if (e = 1) then e1 = i; else if (e = 2) then e2 = i; end end if (e = 0) then result = 'OK'; else if (e = 3) then result = 'Not equal'; else if (e = 1) then result = e1 || '. character ' || substring(i1 from i for 1) || ' has changed'; else begin if (substring(x1 from e1 for 1) = substring(x2 from e2 for 1) and substring(x1 from e2 for 1) = substring(x2 from e1 for 1)) then result = e1 || ' and ' || e2 || ' were swapped'; end suspend; end Regards, Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Wednesday, June 24, 2015 5:12 PM To: Firebird Support Subject: [firebird-support] Soc Sec No comparison using Firebird Greetings All, I would like to pass into a stored procedure two social security numbers for comparison and have the result tell me if one character has changed, or if two characters were switched or if it does not compare at all. Has anyone done anything like this they could share? Or is it even possible? Thoughts? Thanks, Mike . -- This email has been checked for viruses by Avast antivirus software. www.avast.com -- Ta e-pošta je bila pregledana z Avast protivirusnim programom. www.avast.com --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
Re: [firebird-support] Soc Sec No comparison using Firebird
According to this I was correct - startpos and length must be integer literals http://www.firebirdsql.org/refdocs/langrefupd15-substring.html - Original Message - From: 'Bogdan' bog...@mordicom.si [firebird-support] To: firebird-support@yahoogroups.com Sent: Thursday, June 25, 2015 5:39 AM Subject: RE: [firebird-support] Soc Sec No comparison using Firebird create procedure social_number_comparison ( i1 varchar(20), i2 varchar(20) ) returns ( result varchar(100) ) as declare variable x1 char(20); declare variable x2 char(20); declare variable e smallint; declare variable e1 smallint; declare variable e2 smallint; declare variable i smallint; begin i = 0; x1 = i1; x2 = i2; e = 0; while (i < 20 and e < 3) do begin i = i + 1; if (substring(x1 from i for 1) <> substring(x2 from i for 1)) then begin e = e + 1; if (e = 1) then e1 = i; else if (e = 2) then e2 = i; end end if (e = 0) then result = 'OK'; else if (e = 3) then result = 'Not equal'; else if (e = 1) then result = e1 || '. character ' || substring(i1 from i for 1) || ' has changed'; else begin if (substring(x1 from e1 for 1) = substring(x2 from e2 for 1) and substring(x1 from e2 for 1) = substring(x2 from e1 for 1)) then result = e1 || ' and ' || e2 || ' were swapped'; end suspend; end Regards, Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Wednesday, June 24, 2015 5:12 PM To: Firebird Support Subject: [firebird-support] Soc Sec No comparison using Firebird Greetings All, I would like to pass into a stored procedure two social security numbers for comparison and have the result tell me if one character has changed, or if two characters were switched or if it does not compare at all. Has anyone done anything like this they could share? Or is it even possible? Thoughts? Thanks, Mike . -- This email has been checked for viruses by Avast antivirus software. www.avast.com -- Ta e-pošta je bila pregledana z Avast protivirusnim programom. www.avast.com --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
Re: [firebird-support] Soc Sec No comparison using Firebird
Greetings Bogdan, Yes, SUBSTR() does exists in v1.5 so I converted it successfully, I've alwasy used SUBSTRING() before. However, when I tried to run a few simple comparisons with 1 character off in the strings it always returned OK for the result. So I declared a few new variables (sOldSSNChar, sNewSSNChar) to see what is going on while debugging and to my surprise these varaibles would hold the first characted for both the old and new SSN, but then the rest (2nd thru 11th position) would all be blank (empty string), thus thinking they matched. Any idea why this may be happening?. Thanks, Mike Here is the slightly modified SP: SET TERM ^^ ; CREATE PROCEDURE SPS_SOCIAL_NUMBER_COMPARISON ( I1 VarChar(20), I2 VarChar(20)) returns ( RESULT VarChar(100)) AS declare variable x1 char(20); declare variable x2 char(20); declare variable e smallint; declare variable e1 smallint; declare variable e2 smallint; declare variable i smallint; DECLARE VARIABLE sOldSSNChar Char(1); DECLARE VARIABLE sNewSSNChar Char(1); begin i = 0; x1 = i1; x2 = i2; e = 0; while (i < 20 and e < 3) do begin i = i + 1; sOldSSNChar = substr(x1, i, 1); sNewSSNChar = substr(x2, i, 1); if (substr(x1, i, 1) <> substr(x2, i, 1)) then begin e = e + 1; if (e = 1) then e1 = i; else if (e = 2) then e2 = i; end end if (e = 0) then result = 'OK'; else if (e = 3) then result = 'Not equal'; else if (e = 1) then result = e1 || '. character ' || substr(i1, i, 1) || ' has changed'; else begin if (substr(x1, e1,1) = substr(x2, e2, 1) and substr(x1, e2, 1) = substr(x2, e1, 1)) then result = e1 || ' and ' || e2 || ' were swapped'; end suspend; end ^^ SET TERM ; ^^ --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[firebird-support] Firebird 1.5.3 on Windows 2012 R2 Server
Greetings All, Can anyone tell me if Firebird 1.5.3 will run on Windows 2012 R2 Server? Our Delphi application has run since 1997 on one version or another of windows server. It is currently on Windows 2003 Server and we have had no issues at all. Our office is wanting to install a new server with Windows 2012 R2 and I need to know if there are any issues I should be aware of or if Firebird will install and run without any issues. Is there a place to go on the web that discusses this in depth (If necessary)? Thanks for anyone that takes there time to answer. If I'm ever given any breathing room, I would eventually like to move to Firebird 2.5.4 and would also like to know if I should move in stages like to 2.1 then to 2.5, etc. Any documentaion on that would be helpful as well. Thanks all, Mike --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
