[firebird-support] Speed of an query with IN select
Is there a way I can speed up the following query? Select CODE, STKCODE, DESCRIPTION from COUNT_SHEETS_DETAIL where code STARTING WITH 'TPL' and STKCODE in (Select STKCODE from (Select STKCODE, COUNT(STKCODE) as COUNT1 from COUNT_SHEETS_DETAIL where code STARTING WITH 'TPL' GROUP BY STKCODE order by STKCODE ) where COUNT1 > 1) order by STKCODE Any help would be appreciated. Regards Cornie
[firebird-support] Which value will be used after converting a field from Double Prec to Decimal
I have column with field type DOUBLE PRECISION and needs to convert it to DECIMAL (15,4). In a test with a field value of 23.99, I change it to DECIMAL (15,4) and got want I need in my Delphi (DBX) application of 24.. Then I change it back to DOUBLE PRECISION and notice that the value is again 23.99. I presume firebird (2.51 32bit) does not change the stored value when changing the type. My question is now, if I change the type to DECIMAL (15,4) will Firebird use 23.99 or 24.0 in calculations in stored procedures, etc? Any help / explanation will be much appreciate. Regards Cornie van Schoor
[firebird-support] How do you get a summary SUM() from a union of tables
Hi I have 2 (and more) tables with identical structure: Simplified structure and data: CODE VARCHAR(15) QUAN INTEGER Table A Item1, 50 Item1, 40 Item2, 70 Table B Item1, 50 Item2, 100 Item2, 30 I need to get the sum(QUAN) for all the rows in all the tables group by CODE Item1, 140 Item2, 200 Is there a way I can do it with a UNION? Or is there a better way? Regards Cornie van Schoor InfoStar Software
[firebird-support] How do you 0/null results to a GROUP BY query
Hi All I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 015000 2012 026000 2012 030 (or null will be fine) 2012 047000 etc. Any help will be appreciated! Cornie van Schoor InfoStar Software
[firebird-support] Advice needed on speed between triggers and application inserts/updates
I am a developer of accounting software and mostly learned my "trade" by trail and error and from this forum. (Thanks for all the info and tips I receive daily on this forum.) Setup: Delphi XE2 + DBX + Firebird 2.5x Firebird mostly running on dual core pentiums, Windows XP/7 Pro. Workstations = Celerons +, running the application exclusively To date I have used mostly "On Insert / On Update" triggers to insert or update tables that must be update when a certain record is posted. For example: On the sale of an item, I must post a record to the Salesperson Commission table, update the quantities on hand in the Stock Table etc. But due to the complexity of for example the commission structures I am thinking of moving it to the application. The question is: How much slower/faster will 5 SQL statements issue from the application be compared to 1 SQL statement with 4 inserts/updates inside a trigger? Or I am missing some bigger issue here? Thanks for advice Cornie van Schoor InfoStar Software South Africa
[firebird-support] How do I get the differences between 2 timestamps as a timestamp.
I have the following table (Staff_Events) ... Start_Date TIMESTAMP, End_Date TIMESTAMP, Period TIMESTAMP, and need to set Period to the time difference between End_Date and Start_Date. End_Date will always be later than Start_Date. (Enforce by on Insert/Update triggers.) I know I can the get difference with DATEDIFF, but I need the result as a TimeStamp. I would appreciate any help. Regards Cornie van Schoor
[firebird-support] Select * where FieldString = 3 allowed on some Firebird servers
Background Testing Server Windows 7 Professional Firebird 2.5.2 Superserver (Default settings with just TCP-IP changes) Production Server Windows 7 Home Firebird 2.5.2 Superserver (Default settings with just TCP-IP changes) In my application developed in Delphi XE2, dbExpress I made the error of writing scripts like: Select Fields from Table where VARSTR = 3 instead of Select Fields from Table where VARSTR = '3' Where VARSTR is defined as VARCHAR[15] I know in die earlier versions of Firebird is was allowed not to used the single quotes, but that it change. My problem is this: On the testing server the wrong script works fine with a binary copy of the data and it work fine on the production server until yesterday afternoon when it started to give the "can not convert "" to" in the application. I will fix my code, but want I know why: a) The wrong code work on the TEST server b) Worked and then stopped working on the production server Any pointers would be appreciated. Thanks for the all posts here, I learn everyday. Regards Cornie
[firebird-support] How to "fix" SYSDBA/masterkey login
Hi I want to fix my software usage of SYSDBA/masterkey and would appreciate pointers to fastest/best way to do so. I have +- 100 installations running on WinXp/Win7 and Firebird 2.5.1 or 2.5.2. The software is develop with Delphi XE2/DBX. For a start I just want to "copy" the rights of SYSDBA to a new user, if possible directly from my application or a batch file. From there I will implement all the tips etc I learned from this group. Thanks for all the info shared in this group. Regards Cornie van Schoor
[firebird-support] Transactions
Please help my understand what will happen in the following cases involving transactions. System: Delphi XE2, Firebird 2,5x, Using DBX components. Isolation level: ReadCommitted. The actual statements are much more complex and involves various statements in the transaction and/or triggers that updates/insert into up to 8 tables. Case 1 App 1 starts a transaction (T1) and in an After Insert trigger for MasterTable do something like: Update TableSub set X = X + MasterTable.X where Y=Y.MasterTable Before App1 commits, App2 starts a transtaction (T2) and does the same statement. Then App1 commits(T1) and then App2 commits(T2). Will this result in a deadlock or will T1 "run" and then T2 or will X.old in both transaction have the same value? Case 2 Same case as above but the trigger use X = GEN_ID(Generator1,1). Will X be, for example 10, in T1 and 11 in T2, or will it result in a deadlock, or will X be 10 in both transactions? Any information will be appreciated and even more so hints as on how to handle this. I would like T1 to start and run to completion and then T2 taking into account what happen in T1. Kind Regards Cornie van Schoor InfoStar Software South Africa
