[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
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
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
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
[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
[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] 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
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
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
[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 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
[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 09/01/14 and 09/04/14 for
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
[firebird-support] Firebird v1.5.3 on Windows Server 2012
Greetings All, Can anyone tell be if Firebird v 1.5.3 will install and run effectively on Windows Server 2012? It is currently being ran on a Windows 2003 Server and they are wanting to upgrade to a new server and I just wanted to see if there would be any issues. Thanks to any who can shed some light. Mike --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com
Re: [firebird-support] Firebird v1.5.3 on Windows Server 2012
I do not have time to upgrade to V2.0 or v2.5 before the new server would be in. I fully intend to when time permits. Thus, the reason I'm asking if it could be done to allow them to continue while I do the conversion. Thanks, - Original Message - From: Konstantin Khomoutov To: firebird-support@yahoogroups.com Cc: Softtech Support Sent: Friday, December 06, 2013 8:52 AM Subject: Re: [firebird-support] Firebird v1.5.3 on Windows Server 2012 On Fri, 6 Dec 2013 08:33:25 -0600 Softtech Support stwiz...@att.net wrote: Can anyone tell be if Firebird v 1.5.3 will install and run effectively on Windows Server 2012? It is currently being ran on a Windows 2003 Server and they are wanting to upgrade to a new server and I just wanted to see if there would be any issues. Thanks to any who can shed some light. Versions of Firebird below 2.5 are discontinued, so is there really any reason to ask? I mean, if it breaks those they will have to hold both pieces. So I'd they they should better look at upgrading their app to support 2.5 (extensive release notes with possible compatibility issues are available for both 2.0 and 2.5 series). --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com
[firebird-support] How do return the number of object in a Firebird database
Greetings All, v1.5.3 I need to know how many tables, indicies, functions, stored procedures, etc are in my Firebird database. Is there a quick way to determine this? Thanks, Mike [Non-text portions of this message have been removed]
Re: [firebird-support] How do return the number of object in a Firebird database
Thanks Markus and Josef, just what I needed. Much Appreciated - Original Message - From: Markus Ostenried To: firebird-support@yahoogroups.com Sent: Wednesday, July 17, 2013 7:13 AM Subject: Re: [firebird-support] How do return the number of object in a Firebird database On Wed, Jul 17, 2013 at 1:55 PM, Softtech Support stwiz...@att.net wrote: ** Greetings All, v1.5.3 I need to know how many tables, indicies, functions, stored procedures, etc are in my Firebird database. Is there a quick way to determine this? See here, this should provide you with enough information: http://www.alberton.info/firebird_sql_meta_info.html [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
Re: [firebird-support] GBak Backup Restore Problem
Thanks, Looks like it is working now. Appreciate your guidance - Original Message - From: Tupy... nambá To: firebird-support@yahoogroups.com Sent: Tuesday, June 25, 2013 2:23 PM Subject: [firebird-support] GBak Backup Restore Problem Dear Friends, We have a strange problem with GBak. We make a backup with gbak and then a restore, also with the same. We have a table with 60 rows where a not null type SmallInt column has 0 (zero) as content. When restoring, GBak returns null for this column. As this column is not null, we get an error message (Error: Validation error for column .). Between these two steps (backup and restore), no further action is done with the backup file. I ask you = What can cause this problem? Is there any solution for this? Any idea about this problem? Thanks a lot, Roberto Camargo, Rio de Janeiro / Brasil [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Does Firebird have a way to reference similar named fields? (Like MONTH_1 thru MONTH_120)
Thanks for your reply.
MONTH_* are not fields in a table, they are output parameters of my stored
procedure.
Mike
- Original Message -
From: thp_pkmi
To: firebird-support@yahoogroups.com
Sent: Tuesday, June 18, 2013 10:52 PM
Subject: [firebird-support] Re: Does Firebird have a way to reference similar
named fields? (Like MONTH_1 thru MONTH_120)
If I can assume Month_1..Month_120 as fields of your table rather than
variables, then I suppose we can do things like this:
I= 1;
while (I = NoOfCol) do
begin
s= 'update YOUR_TABLE set Month_'||I||' = Month_'||I||' + ? where
SOME_CONDITION';
execute statement (s) (nTotalMonth);
I= I+1;
end
YOUR_TABLE SOME_CONDITION are just symbolic, sorry to be wordy
This approach is only good in syntax, in real performance I think it will be
a bit slower than your long syntax, because of the run time compilation of
EXECUTE STATEMENT.
--- In firebird-support@yahoogroups.com, Softtech Support stwizard@...
wrote:
Thanks for your reply.
I should have mentioned that this stored procedure is used to generate a
Stair-Step Financial Report (My first attempt at such a report I might add) and
therefore option 1 below would not work as I do not need to store the results.
I have used EXECUTE STATEMENT in the past for SELECT statements and even
after looking again at the LangRef-Update.pdf (For v1.5) I do not see how it
could be used for this instance. Am I missing something?
A little more info:
I basically created a stored procedure that would generate a Stair-Step
Financial report that can list the number of accounts/cases turned, amount
turned, average amount turned for collections for a given period, followed by
the amount received for every month since the given period.
The report works as follows:
If Client X has been a client for two years, then it would be a 24 x 24
report. Shown below is an example of a client signed on Oct 2012 and we started
receiving accounts in Dec 2012. So the report is ran for the first year
10/01/12 thru 06/30/13 and is a 9 x 9 report (9 rows - Periods x 9 columns -
Monthly collections).
The start date for this report is 10/01/12 and every month following the
first has to be listed whether they had acounts turned to them for collections
or not. Then the Recovery would show how much was received in each month
following the turn.
Obviously, each row has one less column filled out..so when you get to the
last row, there is only Month 1 on the report.
PERIOD CASES PRINC_AMT AVG_CASE MONTH_1 MONTH_2 MONTH_3 MONTH_4 MONTH_5
MONTH_6 MONTH_7 MONTH_8 MONTH_9
Oct-12 0 0 0 0 0 0 0 0 0 0 0 0
Nov-12 0 0 0 0 0 0 0 0 0 0 0 0
Dec-12 292 162894.14 557.85 1101.64 2721.09 3510.35 2571.71 381.21 256.64
25.00 0 0
Jan-13 229 161576.07 705.57 3802.94 762.90 3995.06 1062.37 1433.10 3206.25
0 0 0
Feb-13 182 150174.56 825.13 1315.53 1502.86 1920.46 60.00 87.74 0 0 0 0
Mar-13 158 122131.82 772.98 187.00 1867.37 1805.65 4251.79 0 0 0 0 0
Apr-13 207 157906.67 762.83 1629.24 2649.20 500.83 0 0 0 0 0 0
May-13 208 166616.77 801.04 167.23 1800.69 0 0 0 0 0 0 0
Jun-13 294 236136.50 803.18 200.00 0 0 0 0 0 0 0 0
Is there somewhere I can post attachments? If so then I could post the full
stored procedure and an excel file that was generated by it.
So any other ideas how to modify this stored procedure?
Mike
- Original Message -
From: thp_pkmi
To: firebird-support@yahoogroups.com
Sent: Tuesday, June 18, 2013 5:14 AM
Subject: [firebird-support] Re: Does Firebird have a way to reference
similar named fields? (Like MONTH_1 thru MONTH_120)
I think you have 2 options:
1. normalize the table to have 2 fields Month Number Month Value,
then you can search record by Month Number for updating Month Value
2. use PSQL statement: EXECUTE STATEMENT, you can read about it in
Firebird-2.5-LangRef-Update.pdf
--- In firebird-support@yahoogroups.com, Softtech Support stwizard@
wrote:
In Delphi I have the ability to reference a field using FieldByName()
inside a For loop like this:
For I := 1 to 120 do
begin
with cdsReport do
begin
FieldByName('MONTH_' + IntToStr(I)).Visible := iNoOfCols = I;
end;
end;
Is there any way to do this in a Firebird Stored Procedure? I have 120
fields named MONTH_1 thru MONTH_120 and currently I have to reference them like
this. Just wanting to know if there is a better way..
...
I = 1;
WHILE (I = iNoOfColumns ) DO
BEGIN
IF (I = 1) THEN
MONTH_1 = MONTH_1 + nTotalMonth;
ELSE IF (I = 2) THEN
MONTH_2 = MONTH_2 + nTotalMonth;
ELSE IF (I = 3) THEN
MONTH_3 = MONTH_3 + nTotalMonth;
ELSE IF (I = 4) THEN
MONTH_4 = MONTH_4 + nTotalMonth;
ELSE IF (I = 5) THEN
MONTH_5 = MONTH_5 + nTotalMonth;
ELSE IF (I = 6
Re: [firebird-support] Re: Does Firebird have a way to reference similar named fields? (Like MONTH_1 thru MONTH_120)
Thanks for your reply.
I should have mentioned that this stored procedure is used to generate a
Stair-Step Financial Report (My first attempt at such a report I might add) and
therefore option 1 below would not work as I do not need to store the results.
I have used EXECUTE STATEMENT in the past for SELECT statements and even after
looking again at the LangRef-Update.pdf (For v1.5) I do not see how it could be
used for this instance. Am I missing something?
A little more info:
I basically created a stored procedure that would generate a Stair-Step
Financial report that can list the number of accounts/cases turned, amount
turned, average amount turned for collections for a given period, followed by
the amount received for every month since the given period.
The report works as follows:
If Client X has been a client for two years, then it would be a 24 x 24 report.
Shown below is an example of a client signed on Oct 2012 and we started
receiving accounts in Dec 2012. So the report is ran for the first year
10/01/12 thru 06/30/13 and is a 9 x 9 report (9 rows - Periods x 9 columns -
Monthly collections).
The start date for this report is 10/01/12 and every month following the first
has to be listed whether they had acounts turned to them for collections or
not. Then the Recovery would show how much was received in each month
following the turn.
Obviously, each row has one less column filled out..so when you get to the last
row, there is only Month 1 on the report.
PERIOD CASES PRINC_AMT AVG_CASE MONTH_1 MONTH_2 MONTH_3 MONTH_4 MONTH_5
MONTH_6 MONTH_7 MONTH_8 MONTH_9
Oct-12 0 0 000000
000 0
Nov-12 0 0 000000
000 0
Dec-12292 162894.14557.85 1101.64 2721.09 3510.35 2571.71 381.21
256.6425.000 0
Jan-13229 161576.07705.57 3802.94 762.90 3995.06 1062.37 1433.10
3206.2500 0
Feb-13182 150174.56825.13 1315.53 1502.86 1920.4660.0087.74
000 0
Mar-13158 122131.82772.98 187.00 1867.37 1805.65 4251.790
000 0
Apr-13207 157906.67762.83 1629.24 2649.20 500.8300
000 0
May-13208 166616.77801.04 167.23 1800.69000
000 0
Jun-13294 236136.50803.18 200.000000
000 0
Is there somewhere I can post attachments? If so then I could post the full
stored procedure and an excel file that was generated by it.
So any other ideas how to modify this stored procedure?
Mike
- Original Message -
From: thp_pkmi
To: firebird-support@yahoogroups.com
Sent: Tuesday, June 18, 2013 5:14 AM
Subject: [firebird-support] Re: Does Firebird have a way to reference similar
named fields? (Like MONTH_1 thru MONTH_120)
I think you have 2 options:
1. normalize the table to have 2 fields Month Number Month Value, then
you can search record by Month Number for updating Month Value
2. use PSQL statement: EXECUTE STATEMENT, you can read about it in
Firebird-2.5-LangRef-Update.pdf
--- In firebird-support@yahoogroups.com, Softtech Support stwizard@...
wrote:
In Delphi I have the ability to reference a field using FieldByName()
inside a For loop like this:
For I := 1 to 120 do
begin
with cdsReport do
begin
FieldByName('MONTH_' + IntToStr(I)).Visible := iNoOfCols = I;
end;
end;
Is there any way to do this in a Firebird Stored Procedure? I have 120
fields named MONTH_1 thru MONTH_120 and currently I have to reference them like
this. Just wanting to know if there is a better way..
...
I = 1;
WHILE (I = iNoOfColumns ) DO
BEGIN
IF (I = 1) THEN
MONTH_1 = MONTH_1 + nTotalMonth;
ELSE IF (I = 2) THEN
MONTH_2 = MONTH_2 + nTotalMonth;
ELSE IF (I = 3) THEN
MONTH_3 = MONTH_3 + nTotalMonth;
ELSE IF (I = 4) THEN
MONTH_4 = MONTH_4 + nTotalMonth;
ELSE IF (I = 5) THEN
MONTH_5 = MONTH_5 + nTotalMonth;
ELSE IF (I = 6) THEN
MONTH_6 = MONTH_6 + nTotalMonth;
ELSE IF (I = 7) THEN
MONTH_7 = MONTH_7 + nTotalMonth;
ELSE IF (I = 8) THEN
MONTH_8 = MONTH_8 + nTotalMonth;
ELSE IF (I = 9) THEN
MONTH_9 = MONTH_9 + nTotalMonth;
ELSE IF (I = 10) THEN
MONTH_10 = MONTH_10 + nTotalMonth;
ELSE IF (I = 11) THEN
MONTH_11 = MONTH_11 + nTotalMonth;
ELSE IF (I = 12) THEN
MONTH_12 = MONTH_12 + nTotalMonth;
ELSE IF (I = 13) THEN
MONTH_13 = MONTH_13 + nTotalMonth;
ELSE IF (I = 14) THEN
MONTH_14 = MONTH_14 + nTotalMonth;
ELSE IF (I = 15) THEN
MONTH_15 = MONTH_15 + nTotalMonth;
ELSE IF (I = 16) THEN
MONTH_16 = MONTH_16
[firebird-support] Can I retreive parameters from a stored procedure?
Greetings All, Firebird v1.53 I need to programatically (Using Delphi) retreive the input parameters from a stored procedure. Is this possible? Thanks, Mike [Non-text portions of this message have been removed]
[firebird-support] Re: Can I retreive parameters from a stored procedure?
I figured it out... SELECT P.RDB$PARAMETER_NUMBER, P.RDB$PARAMETER_NAME FROM RDB$PROCEDURE_PARAMETERS P WHERE P.RDB$PROCEDURE_NAME = 'SPS_COTM_ACTIVITY_RPT' AND P.RDB$PARAMETER_TYPE = 0 - Original Message - From: Softtech Support To: Firebird Support Sent: Friday, May 03, 2013 8:55 AM Subject: Can I retreive parameters from a stored procedure? Greetings All, Firebird v1.53 I need to programatically (Using Delphi) retreive the input parameters from a stored procedure. Is this possible? Thanks, Mike [Non-text portions of this message have been removed]
[firebird-support] Can I retreive parameters from a stored procedure? Part 2
How do I determine what type the parameter is? Is it a date, or a varchar Any help with this appreciated. Thanks, Mike I figured it out... SELECT P.RDB$PARAMETER_NUMBER, P.RDB$PARAMETER_NAME FROM RDB$PROCEDURE_PARAMETERS P WHERE P.RDB$PROCEDURE_NAME = 'SPS_COTM_ACTIVITY_RPT' AND P.RDB$PARAMETER_TYPE = 0 - Original Message - From: Softtech Support To: Firebird Support Sent: Friday, May 03, 2013 8:55 AM Subject: Can I retreive parameters from a stored procedure? Greetings All, Firebird v1.53 I need to programatically (Using Delphi) retreive the input parameters from a stored procedure. Is this possible? Thanks, Mike [Non-text portions of this message have been removed]
