[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]
Re: {Disarmed} Re: [firebird-support] Looking for a stored procedure or function that can find the second occurance of value passed in
Thanks Mark,
Guess I should have stated that I need this for Firebird 1.5
Mike
- Original Message -
From: Mark Rotteveel
To: firebird-support@yahoogroups.com
Sent: Thursday, March 14, 2013 10:05 AM
Subject: {Disarmed} Re: [firebird-support] Looking for a stored procedure or
function that can find the second occurance of value passed in
On Thu, 14 Mar 2013 10:00:17 -0500, SoftTech mi...@softtechks.com
wrote:
Greetings All,
I would like to pass in a string and a substr and the position of where
the
x occurrence is.
Example:
String: Then the big red fox jumped over the flowing stream.
SubStr: the
occurrence: 2
Would return 34 which is the starting position of the second the found
in
the string passed in.
Anything like this available anywhere?
You can use POSITION(...)
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-intfunc-position.html
but you will need to keep track of the number of occurrences yourself.
Mark
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
[Non-text portions of this message have been removed]
[firebird-support] Stored Procedure or Function to find a carraige return in a string
Firebird 1.5 Anything available to find an ASCII_CHAR(13) in a given string and return the position of it? Thanks, Mike
Re: [firebird-support] Stored Procedure or Function to find a carraige return in a string
Thanks Mark,
You are correct that works.
- Original Message -
From: Mark Rotteveel
To: firebird-support@yahoogroups.com
Sent: Thursday, March 14, 2013 10:50 AM
Subject: {Disarmed} Re: [firebird-support] Stored Procedure or Function to
find a carraige return in a string
On Thu, 14 Mar 2013 10:47:57 -0500, SoftTech mi...@softtechks.com
wrote:
Firebird 1.5
Anything available to find an ASCII_CHAR(13) in a given string and
return
the position of it?
I think the answer is the same as my answer to your previous question :)
Mark
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
[Non-text portions of this message have been removed]
[firebird-support] For loop or while loop inside of a stored procedure?
Greetings All,, Using Firebird 1.5 I have a stored procedure where I pass in a begin and end date. Is there way to process each date within a for or while loop so that I can return a record for each date regardless if I find any data to report on for that date? Any tips on how to code this would be greatly appreciated. Thanks, Mike
Re: [firebird-support] For loop or while loop inside of a stored procedure?
Perfect, thanks
- Original Message -
From: Scott Morgan
To: firebird-support@yahoogroups.com
Sent: Friday, February 08, 2013 11:20 AM
Subject: {Disarmed} Re: [firebird-support] For loop or while loop inside of a
stored procedure?
On 08/02/13 16:54, SoftTech wrote:
I have a stored procedure where I pass in a begin and end date.
Is there way to process each date within a for or while loop so that I can
return a record for each date regardless if I find any data to report on for
that date?
Any tips on how to code this would be greatly appreciated.
Something like this might help:
SET TERM ^ ;
CREATE PROCEDURE DATE_RANGE
( START_DATE DATE, END_DATE DATE )
RETURNS
( INTERVAL_DATE DATE )
AS
BEGIN
INTERVAL_DATE = START_DATE;
WHILE (INTERVAL_DATE = END_DATE) DO
BEGIN
SUSPEND;
INTERVAL_DATE = INTERVAL_DATE + 1;
END
END^
SET TERM ; ^
Which can be used with a query or modified to suit:
/* All dates from 5th Jan to 10th Jan inclusive */
SELECT INTERVAL_DATE FROM DATE_RANGE('1/5/2013', '1/10/2013');
Scott
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
[Non-text portions of this message have been removed]
Re: [firebird-support] Need a little help with EXECUTE STATEMENT
Greetings Martijn and Mark,
You're exactly right that fixed the problem.
Mark it also fixed this error
ISC ERROR CODE: 335544606
ISC ERROR MESSAGE:
expression evaluation not supported
Thanks to both of you for taking the time to respond,
Mike
- Original Message -
From: Martijn Tonies
To: firebird-support@yahoogroups.com
Sent: Wednesday, January 09, 2013 8:14 AM
Subject: {Disarmed} Re: [firebird-support] Need a little help with EXECUTE
STATEMENT
Hello Mike,
DECLARE VARIABLE tsEndDate TimeStamp;
begin
tsEndDate = :V_END_DATE || ' 23:59:59';
EXECUTE STATEMENT 'SELECT FIRST 1 D.CREATE_DATE
FROM DEBT D
WHERE D.CREATE_DATE = ' || tsEndDate
INTO :FIRST_CREATE_DATE;
both caused this error.
ISC ERROR CODE: 335544569
ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 7, char 141
23
What am I doing wrong? Any work arounds?
This ends up being (for V_END_DATE = 2013/1/09)
WHERE D.CREATE_DATE = 9/1/2013 23:59:59
Do you see the problem? It becomes un-quoted...
Try:
WHERE D.CREATE_DATE = ''' || tsEndDate ||
INTO ...
Where ''' = ' ' ' (with no spaces) and = ' ' ' ' (with no spaces)
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!
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
[Non-text portions of this message have been removed]
[firebird-support] Need a little help with EXECUTE STATEMENT
Greetings All,
Using Firebird 1.5.3
D.CREATE_DATE is defined as a TimeStamp in the database.
V_END_DATE is an input parameter defined as Date
V_END_DATETIME is an input parameter defined as TimeStamp
I have a stored procedure with the following execute statment
EXECUTE STATEMENT 'SELECT COALESCE(COUNT(D.DEBT_NO),0) AS TOTAL_COUNT,
COALESCE(SUM(D.ORIG_PRINCIPAL +
D.ADJ_PRINCIPAL),0) AS TOTAL_GROSS,
(SUM(D.LIST_DATE -
D.SERVICE_DATE)/COALESCE(COUNT(D.DEBT_NO),1)) AS AVG_AGE
FROM DEBT D
JOIN ACCT_CASE AC ON AC.ACCT_ID = D.ACCT_ID AND
AC.CASE_ID = D.CASE_ID
WHERE AC.CLT_ID = ' || V_CLT_ID ||
'AND NOT AC.CLT_SITE_ID IN (' ||
V_CLT_SITE_ID_LIST || ')
AND EXTRACT(MONTH FROM D.LIST_DATE) = ' ||
iBegMonth ||
'AND EXTRACT(YEAR FROM D.LIST_DATE) = ' ||
iBegYear ||
'AND CAST(D.CREATE_DATE AS DATE) = ' ||
V_END_DATE
INTO :R_TOTAL_COUNT, :R_TOTAL_GROSS_AMT,
:R_AVG_AGE;
When I run the stored procedure I receive the follow error:
ISC ERROR CODE: 335544606
ISC ERROR MESSAGE:
expression evaluation not supported
I tracked it down to the CAST(D.CREATE_DATE AS DATE) line.
I guess I have never tried to use a CAST() inside of a execute statement
before. Is this correct?
So, as a work around I thought I would modify it as follows:
EXECUTE STATEMENT 'SELECT COALESCE(COUNT(D.DEBT_NO),0) AS TOTAL_COUNT,
COALESCE(SUM(D.ORIG_PRINCIPAL +
D.ADJ_PRINCIPAL),0) AS TOTAL_GROSS,
(SUM(D.LIST_DATE -
D.SERVICE_DATE)/COALESCE(COUNT(D.DEBT_NO),1)) AS AVG_AGE
FROM DEBT D
JOIN ACCT_CASE AC ON AC.ACCT_ID = D.ACCT_ID AND
AC.CASE_ID = D.CASE_ID
WHERE AC.CLT_ID = ' || V_CLT_ID ||
'AND NOT AC.CLT_SITE_ID IN (' ||
V_CLT_SITE_ID_LIST || ')
AND EXTRACT(MONTH FROM D.LIST_DATE) = ' ||
iBegMonth ||
'AND EXTRACT(YEAR FROM D.LIST_DATE) = ' ||
iBegYear ||
'AND D.CREATE_DATE = ' || V_END_DATE || '
23:59:59'
INTO :R_TOTAL_COUNT, :R_TOTAL_GROSS_AMT,
:R_AVG_AGE;
ISC ERROR CODE: 335544569
ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 7, char 141
23
So then I thought I would try this:
DECLARE VARIABLE tsEndDate TimeStamp;
begin
tsEndDate = :V_END_DATE || ' 23:59:59';
EXECUTE STATEMENT 'SELECT COALESCE(COUNT(D.DEBT_NO),0) AS TOTAL_COUNT,
COALESCE(SUM(D.ORIG_PRINCIPAL +
D.ADJ_PRINCIPAL),0) AS TOTAL_GROSS,
(SUM(D.LIST_DATE -
D.SERVICE_DATE)/COALESCE(COUNT(D.DEBT_NO),1)) AS AVG_AGE
FROM DEBT D
JOIN ACCT_CASE AC ON AC.ACCT_ID = D.ACCT_ID AND
AC.CASE_ID = D.CASE_ID
WHERE AC.CLT_ID = ' || V_CLT_ID ||
'AND NOT AC.CLT_SITE_ID IN (' ||
V_CLT_SITE_ID_LIST || ')
AND EXTRACT(MONTH FROM D.LIST_DATE) = ' ||
iBegMonth ||
'AND EXTRACT(YEAR FROM D.LIST_DATE) = ' ||
iBegYear ||
'AND D.CREATE_DATE = ' || tsEndDate
INTO :R_TOTAL_COUNT, :R_TOTAL_GROSS_AMT,
:R_AVG_AGE;
Same Error:
ISC ERROR CODE: 335544569
ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 7, char 141
23
Then I thought I would just try something simple.
EXECUTE STATEMENT 'SELECT FIRST 1 D.CREATE_DATE
FROM DEBT D
WHERE D.CREATE_DATE = ' || :V_END_DATETIME
INTO :FIRST_CREATE_DATE;
or
DECLARE VARIABLE tsEndDate TimeStamp;
begin
tsEndDate = :V_END_DATE || ' 23:59:59';
EXECUTE STATEMENT 'SELECT FIRST 1 D.CREATE_DATE
FROM DEBT D
WHERE D.CREATE_DATE = ' || tsEndDate
INTO :FIRST_CREATE_DATE;
both caused this error.
ISC ERROR CODE: 335544569
ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 7, char 141
23
What am I doing wrong? Any work arounds?
Any help appreciated.
Thanks,
Mike
[firebird-support] Wrting my first UDF for Firebird v1.5
Greetings All,
I had to create a UDF using Delphi that can be imported into Firebird.
Currently using v1.5 but am studying up on how to move to the latest version
of Firebird.
Using this article as my guide I created my first UDF:
http://www.firebirdsql.org/en/writing-udfs-for-interbase/
The UDF will take a SQL statement with parameters and replace the parameters
with the actual value, and return a SQL statement that can then be used with
EXECUTE STATEMENT.
The UDF works great.
My concern and question is since I'm using v1.5 of Firebird and I did
include IB_Util in my uses clause do I need to use function
ib_util_malloc(l: integer): pointer; cdecl; external 'ib_util.dll';?
Or does anyone see anything wrong with my UDF that I should be made aware
of?
Does ib_util.dll need to be in the windows\system directory on the office
server?
Here is my UDF:
DPR Source:
library Softtech;
uses
SysUtils,
Classes,
SQLFunctions in 'SQLFunctions.pas';
{$R *.RES}
exports SQLCondParamRepl;
begin
end.
Unit Source:
{-
Project Name:
Project Dir : C:\Delphi 5\DLLs\Softtech UDF\
Created : 17-Dec-2012
Unit Name : SQLFunctions
Purpose :
Author : Michael G. Tuttle
History :
-}
unit SQLFunctions;
interface
//* IB_Util.pas must be in the library path
uses Sysutils, IB_Util;
function SQLCondParamRepl(SQLCommandText: PChar; var AcctID, CaseID, DebtNo:
Integer): PChar; cdecl; export;
implementation
{ Use this in the script editor of Database Workbench to install this
function into Firebird:
declare external function f_SQLCondParamRepl
cString(32760), Integer, Integer, Integer
returns cstring(32760) free_it
entry_point 'SQLCondParamRepl' module_name 'softtech';
Test it with this:
SELECT F_SQLCONDPARAMREPL('SELECT D.LIST_DATE FROM DEBT D WHERE D.ACCT_ID
= :Param1 AND D.CASE_ID = :Param2 AND D.DEBT_NO = :Param3', :V_ACCT_ID,
:V_CASE_ID, :V_DEBT_NO) from RDB$Database
Or from within a stored procedure:
RESULT = F_SQLCONDPARAMREPL('SELECT D.LIST_DATE FROM DEBT D WHERE
D.ACCT_ID = :Param1 AND D.CASE_ID = :Param2 AND D.DEBT_NO = :Param3',
:V_ACCT_ID, :V_CASE_ID, :V_DEBT_NO);
EXECUTE STATEMENT}
function SQLCondParamRepl(SQLCommandText: PChar; var AcctID, CaseID, DebtNo:
Integer): PChar; cdecl; export;
var
CT: String;
begin
CT := String(SQLCommandtext);
CT := StringReplace(CT, ':Param1', IntToStr(AcctID), [rfReplaceAll,
rfIgnoreCase]);
CT := StringReplace(CT, ':Param2', IntToStr(CaseID), [rfReplaceAll,
rfIgnoreCase]);
if DebtNo 0 then
CT := StringReplace(CT, ':Param3', IntToStr(DebtNo), [rfReplaceAll,
rfIgnoreCase]);
Result := PChar(CT);
end;
end.
Re: {Disarmed} Re: [firebird-support] Replacement of parameters within a stored procedure
OK, I'm sure I've seen this in the list before... Where can I find
documentation on moving from v1.5 to v2.5?
- Original Message -
From: Mark Rotteveel
To: firebird-support@yahoogroups.com
Sent: Saturday, December 15, 2012 4:23 AM
Subject: {Disarmed} Re: [firebird-support] Replacement of parameters within a
stored procedure
On 14-12-2012 21:49, Dmitry Kuzmenko wrote:
Hello, SoftTech!
Saturday, December 15, 2012, 12:36:32 AM, you wrote:
S I have used Execute Statement in the past. Using it to run SQL
S statements built on the fly. What my problem is, is taking an existing
SQL statement and
S replacing the Param1 and Param2 with actual values. Again, I cannot
build it on the fly.
well, then, I can't understand the problem.
You have statement in the varchar variable, and you can change it
to anything.
And, ES accepts parameters.
EXECUTE STATEMENT only accepts parameters on Firebird 2.5, not on
Firebird 1.5 (which is the version SoftTech is using).
Mark
--
Mark Rotteveel
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
[Non-text portions of this message have been removed]
[firebird-support] Replacement of parameters within a stored procedure
Firebird v1.5 A SQL statement is stored in a table. Inside of a stored procedure I need to retrieve this SQL statement, replace :Param1 and :Param2 with actual values then execute it. I will of course know the value, just don't know how to replace the :Param1 and 2 with the value. SELECT DISTINCT 1 FROM ACCT_CASE INNER JOIN DEBT D ON (ACCT_CASE.ACCT_ID=D.ACCT_ID) and (ACCT_CASE.CASE_ID=D.CASE_ID) INNER JOIN DEBTOR_CASE_DEBT ON (D.ACCT_ID=DEBTOR_CASE_DEBT.ACCT_ID) and (D.DEBT_NO=DEBTOR_CASE_DEBT.DEBT_NO) and (D.CASE_ID=DEBTOR_CASE_DEBT.CASE_ID) INNER JOIN PERSON PD ON (PD.PERSON_ID=DEBTOR_CASE_DEBT.PERSON_ID) LEFT OUTER JOIN PER_ADDRESS PDPA ON (PDPA.PERSON_ID=PD.PERSON_ID) LEFT OUTER JOIN ADDRESS PDA ON (PDA.ADDR_ID=PDPA.ADDR_ID) LEFT OUTER JOIN ZIP_CODE ON (PDA.ZIP_CODE_ID=ZIP_CODE.ZIP_CODE_ID) WHERE (ACCT_CASE.ACCT_ID = :Param1 AND ACCT_CASE.CASE_ID = :Param2 AND PDPA.DEFAULT_ADDRESS = 1 AND ZIP_CODE.STATE_CODE = 'AZ') Can this be done? If so, some guidance would be appreciated. Thanks, Mike
Re: {Disarmed} Re: [firebird-support] Replacement of parameters within a stored procedure
I have used Execute Statement in the past. Using it to run SQL statements
built on the fly. What my problem is, is taking an existing SQL statement and
replacing the Param1 and Param2 with actual values. Again, I cannot build it
on the fly.
- Original Message -
From: Dmitry Kuzmenko
To: firebird-support@yahoogroups.com
Sent: Friday, December 14, 2012 2:14 PM
Subject: {Disarmed} Re: [firebird-support] Replacement of parameters within a
stored procedure
Hello, SoftTech!
Friday, December 14, 2012, 10:01:51 PM, you wrote:
S I cannot do what I need to do on the client side as you suggest.
S This must be handled on the server using firebird. A trigger is used to
call a stored
S procedure and then inside this stored procedure I need to replace
parameters with actual values.
Look at Execute Statement syntax.
But what are you doing is road to hell - trigger, than SP, then
parsing a then ES with THAT query. Be sure that performance will go to zero.
--
Dmitry Kuzmenko, www.ib-aid.com
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
[Non-text portions of this message have been removed]
Re: {Disarmed} [firebird-support] Firebird metadata comparison
I use Database Comparer by www.clevercomponent.com they have a stand alone
utility, an ActiveX component as well as a VCL component so you can develop
your own interface which is what I have done.
- Original Message -
From: Steve Cookson
To: firebird-support@yahoogroups.com
Sent: Friday, December 07, 2012 6:16 AM
Subject: {Disarmed} [firebird-support] Firebird metadata comparison
Hi Guys,
Is there some way I can compare the metadata between two databases?
Really I'd like to implement configuration management for my Firebird user
base, but I don't really know how to begin.
Any ideas would be welcome.
Regards
Steve
[Non-text portions of this message have been removed]
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
[Non-text portions of this message have been removed]
Re: {Disarmed} [firebird-support] Re: UNION prevents all records from being pulled to the results
Thanks Alexandre, Aage, and Ann.
I've been working in Interbase and Firebird since 1996 and never had run across
this before because my row were always distinct. I knew it was something I was
doing wrong.
Thanks again to all who replied,
Mike
- Original Message -
From: Aage Johansen
To: firebird-support@yahoogroups.com
Sent: Monday, October 29, 2012 4:06 PM
Subject: {Disarmed} [firebird-support] Re: UNION prevents all records from
being pulled to the results
SoftTech wrote:
The SQL that follows pulls these results: (These results are missing 5
records from the second select of the union)
FEE_CODE AMT_EARNED_AGENCY SHOW_IN_PMT_DIST_PLAN FEE_CATEGORY
CCO2 76 1 0
SVC 1.17 1 0
SVC 30 1 0
... ...
From Helen's Firebird book:
If duplicate rows are formed during the creating of the union set,
the default behavior is to exclude the duplicate rows from the set.
To include the duplicates, use UNION ALL instead of UNION on its own .
--
Aage J.
[Non-text portions of this message have been removed]
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
[Non-text portions of this message have been removed]
[firebird-support] UNION prevents all records from being pulled to the results
Greetings All, The SQL that follows pulls these results: (These results are missing 5 records from the second select of the union) FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY CCO27610 SVC1.1710 SVC3010 This is the SQL statement: SELECT FC.FEE_CODE, COALESCE(PCF.CASE_FEE_AMT_EARNED_AGENCY_1 + PCF.CASE_FEE_AMT_EARNED_AGENCY_2, 0) AS AMT_EARNED_AGENCY, FC.SHOW_IN_PMT_DIST_PLAN, FC.FEE_CATEGORY FROM PMT_CASE_FEE PCF JOIN PMT_CASE PC ON PC.ACCT_ID = PCF.ACCT_ID AND PC.CASE_ID = PCF.CASE_ID AND PC.PMT_ID = PCF.PMT_ID AND PC.PMT_NO = PCF.PMT_NO JOIN CASE_FEE_HISTORY CFH ON CFH.CASE_FEE_ID = PCF.CASE_FEE_ID AND CFH.PMT_ID = PCF.PMT_ID JOIN CASE_FEE CF ON CF.CASE_FEE_ID = PCF.CASE_FEE_ID JOIN FEE_CODE FC ON FC.FEE_ID = CF.FEE_ID WHERE PCF.PMT_ID = :iPmtID AND PCF.PMT_NO = :iPmtNo AND PCF.ACCT_ID = :iAcctID AND PCF.CASE_ID = :iCaseID UNION SELECT FC.FEE_CODE, COALESCE(PDF.DEBT_FEE_AMT_EARNED_AGENCY_1 + PDF.DEBT_FEE_AMT_EARNED_AGENCY_2, 0) AS AMT_EARNED_AGENCY, FC.SHOW_IN_PMT_DIST_PLAN, FC.FEE_CATEGORY FROM PMT_DEBT_FEE PDF JOIN PMT_DEBT PD ON PD.ACCT_ID = PDF.ACCT_ID AND PD.DEBT_NO = PDF.DEBT_NO AND PD.PMT_ID = PDF.PMT_ID AND PD.PMT_NO = PDF.PMT_NO JOIN DEBT_FEE_HISTORY DFH ON DFH.DEBT_FEE_ID = PDF.DEBT_FEE_ID AND DFH.PMT_ID = PDF.PMT_ID JOIN DEBT_FEE DF ON DF.DEBT_FEE_ID = PDF.DEBT_FEE_ID JOIN FEE_CODE FC ON FC.FEE_ID = DF.FEE_ID JOIN DEBT D ON D.ACCT_ID = PDF.ACCT_ID AND D.DEBT_NO = PDF.DEBT_NO WHERE PDF.PMT_ID = :iPmtID AND PDF.PMT_NO = :iPmtNo AND D.ACCT_ID = :iAcctID AND D.CASE_ID = :iCaseID If I execute just the first select in the union it pulls these results FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY CCO27610 If I execute the second select in the union it pulls these results FEE_CODEAMT_EARNED_AGENCYSHOW_IN_PMT_DIST_PLANFEE_CATEGORY SVC3010 SVC3010 SVC3010 SVC3010 SVC3010 SVC1.1710 SVC3010 Again, executing the select statements separately results in the correct records being returned, executing both selects with the union omits 5 records. Using Firebird 1.5.3 Any ideas? Thanks, Mike
[firebird-support] How do I convert a timestamp to a string from within a stored procedure?
Greetings All, Does anyone know how I can accomplish this from within a stored procedure? I need to convert a timestamp 01/26/2012 05:38:32am to a string 01262012053832. Will actually be using CURRENT_TIMESTAMP within the stored procedure. Still using Firebird 1.5.3 (Hope to convert down the road) Thanks to all who respond. Mike
Re: {Disarmed} [firebird-support] How do I convert a timestamp to a string from within a stored procedure?
I think I have something figured out...
- Original Message -
From: SoftTech
To: firebird-support@yahoogroups.com
Sent: Thursday, January 26, 2012 5:50 AM
Subject: {Disarmed} [firebird-support] How do I convert a timestamp to a
string from within a stored procedure?
Greetings All,
Does anyone know how I can accomplish this from within a stored procedure?
I need to convert a timestamp 01/26/2012 05:38:32am to a string
01262012053832.
Will actually be using CURRENT_TIMESTAMP within the stored procedure.
Still using Firebird 1.5.3 (Hope to convert down the road)
Thanks to all who respond.
Mike
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
[Non-text portions of this message have been removed]
