RE: [firebird-support] Gbaked and restored database larger than the original?

Take a look here: http://www.firebirdfaq.org/faq361/ I'd add that restore also has a -use_all_space option. If you don't specify this, then pages are filled to approximately 80% (80% is preferable over 100% unless it is a read-only database). See http://www.firebirdsql.org/manual/gbak-cmdline.h

SV: [firebird-support] general question: calculated fileds vs performance

>Hello! Generally speaking, regarding performance, is "expensive" having >calculated fields? >Not complex ones, I mean this type of thing: > >... COMPUTED BY (cast(qty * price * tax / 100 as money 2)) >So far I don't notice any difference but I'd like to hear opinions about >whether is a good

RE: [firebird-support] Order By Not Working Using "WITH" Clause

>Hi All, > >I have below SQL, which is concatenating the ShortCode column data, but >without ordering as per the ORDER_NUMBER column in ABC table in "WITH" clause. > >Please help. > >With TBL_SHORT_CODE (SHORT_CODE, FK_KEY) As  >( >SELECT Distinct(XYZ.SHORT_CODE) As SHORT_CODE, ABC.FK_KEY  >From A

RE: [firebird-support] replace a database in production

>>i have a web app that use firebird embedded database hosted in the internet. >>and the same database in local used by a regular database 2.5 server >>I have to replace the web database with the local one (a kind of replication) >>the problem is that the database could be used at that moment so lo

Re: [firebird-support] Why is SQL slow?

>Could someone give me some pointers to improve the speed of this SQL please. >It's taking >about 10 minutes to execute on a fast PC using Firebird v1.56. > >I am counting the number of messages sent by different methods, for a >particular company. >A company has a number of contacts and they r

RE: [firebird-support] Get ID of record with minsort

>I have a table with one ID-Column and one Sort-Column. > >I need the ID from the record with the lowest sort-number. >Sortnumbers are not unique (can be doubled). > >Can I do this with one SQL-Statement? If you want one row returned: Select ID_column From Order by Sort_Column Rows 1 If you wan

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 i

Re: [firebird-support] Firebird migration to increase perfomances

>Yes I just checked my original query will return 6 rows without the distinct OK, then lets add another CTE so that the calculations aren't multiplied: with tmp1(surname, name, trxdate, source, job_number, creditnr, sourceid, serviceadvisOR) as (select distinct trim(c.name||' '||c.surname), trim

RE: [firebird-support] Firebird migration to increase perfomances

>Hi Set > >Wow your query runs like a bat out of hell J 0.16 Milliseconds !! and it >easier to maintain as you say. > >The only issue is that it somehow multiplies the totals by 6 on all results >as compared mine? > >i.e. mine will return an invoice total of say 18500.00 and yours will return >111

Re: [firebird-support] Firebird migration to increase perfomances

>> There can be lots of reasons for a database application being slow. If >> the database have huge tables, and the application is written thinking >> in terms of tables as opposed to datasets, then things will be slow > >Svein. >An interesting comment, to me anyway, regarding tables vs dat

Re: [firebird-support] Firebird migration to increase perfomances

>Select distinct trim(surname)as surname, trim(name)as name, trxdate, source, >job_number, creditnr, sourceid, serviceadvisOR, (credvat)as credvat, >(invoiceVAT) AS VAT, > (invoiceINC)AS invoicetotalINC, (invoiceINC-invoicevat+credvat)as > invoicetotal, (credinc+creditnote-(invoiceINCRedo-

RE: [firebird-support] Firebird migration to increase perfomances

At first glance, your plan seems OK. However, it would be better if we also could see the index definitions as well as get some information about index selectivity. And, this list removes attachments, so please post the text of the query as part of the message. Set Stef van der Merwe wrote: I

RE: [firebird-support] Firebird migration to increase perfomances

>Hi all, > > some years ago I developed a web application based on Interbase, Win98, PHP, > Apache. During those yeas has been lightly updated with lase >release of software, now the we application (10 users) use the follow: > >Microsoft Server 2012 (64bit) >Apache 2.0 >PHP 5.4 >Firebird SQL 1.5 (

RE: [firebird-support] Firebird 2.52 gbak fails to do a restore - error trigger (3)

Unlike Vlad, Helen and Thomas, I know next to nothing about problems like yours (I'm good at SELECTs, which is way different from corrupted backups). Hence, listen more to them than to me. >gbak:restoring privilege for user SYSDBA >gbak: ERROR:action cancelled by trigger (3) to preserve data

[firebird-support] GROUP BY optional with constants?

I did a mistake with a query I wrote (Fb 2.5.3 or 2.5.4), but to my surprise Firebird did not complain and gave me the desired result. What I found was that whilst Firebird complains about missing GROUP BY if writing something like SELECT 'This does not work', RDB$RELATION_ID, list(RDB$CHARACTER

RE: [firebird-support] Re: Inssuficient rights for operation....

>Hello. I used Firebird 1.5.6 on server on WIN7, a database with a role with >full rights, and a user defined in ROLE, USER. >I have a problem. In the network with a application, on different client >station, login with the USER, all functions work OK. >From server or another WIN7 station, with U

RE: [firebird-support] Parametrized queries and execute statement - dynamic parameter list

>3. when i change proc to use execute statement with parameters >  >SET TERM ^ ; >CREATE PROCEDURE MY_PROC_CALC_EXEC_PARAM(ID INTEGER) RETURNS(SUM_AAA INTEGER) >AS >DECLARE VARIABLE VAR_SQL VARCHAR(1000); >BEGIN > VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID'; >  EXECUTE STATEMENT (VAR_SQL)

RE: [firebird-support] How to CAST float to integer with error?

>Hello, >is it possible by using CAST or in any other way to get database error when >casting such number to integer? >SELECT CAST('13.245' AS INTEGER) FROM RDB$DATABASE >This gives 13 but instead I would like to get an error because precision is >lost. > >However, I would like to NOT get an erro

Re: [firebird-support] Why index is not used in this query?

>SELECT * FROM >TABLE_2 T2 > INNER JOIN >TABLE_1 T1 > ON > T2.TABLE_1_ID = T1.ID > >After executing this query I am getting such plan: > >PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) > >Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am >getting error when i try to en

Re: [firebird-support] Re: Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

>Except that grouping by OPS_ZIP would not accomplish the same thing. Multiple >zip codes map to the same REGION_ID and the zip codes might not be contiguous. > >Guess I'm going to have to rethink this. What about with tmp(PB_LOAD_TYPE, APPT_DATE, REGION_ID, PICKUP, DELIVERY) as (SELECT PB_LOAD_

Re: [firebird-support] Is it possible to do this with window function?

>Hi guys. Hi Bruce! >I am having such data: > >ID DATE_TIMETYPE >101.01.2015, 00:00:00.000A >201.01.2015, 00:01:00.000A >301.01.2015, 00:02:00.000A >401.01.2015, 00:10:00.000B >501.01.2015, 00:15:00.000B >601.01.2015, 00:20:00.000

SV: [firebird-support] SQL Case query takes 4 minutes 30 seconds to execute !!

[I've removed irrelevant details from your original question] >I sincerely hope I am in the right place. We have a query which takes 4 >minutes 30 seconds to execute this is simply >too long. Please help ! Definitely, Stef, most performance problems can be solved on this list, and I agree t

RE: [firebird-support] SQLAnywhere to Firebird possibility

>Hi, > >I have been a major contributor to Firebird since 2003, also my previous >company "BASX", which I was the Head developer of, >was a gold sponsor of Firebird. > >I have recently moved to another company that uses SQLAnywhere. > >They are interested in moving to Firebird. > >But Firebird do

[firebird-support] Re: Need Speed Optimization for SQL

>CRM_DOCUMENT_HEADER Table - PK_DOCUMENT_HEADER is primary key and having >seperate index on this column as well What do you mean by this, Vishal? I think it was fixed a while ago, but duplicate indexes used to confuse the optimizer, so PK_DOCUMENT_HEADER ought not to be both the primary key an

RE: [firebird-support] Need Speed Optimization for SQL

>Hi SET, > >I tried as below way by adding Group By clause: > >with TMP(PK_JOB_ITEMS) as >(select distinct PK_JOB_ITEMS >from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}') > >select >Sum(cdi.QUANTITY) AS Delivered, >cdi.FK_JOB_ITEM >from tmp t >join CRM_DOCUMENT_ITEMS cdi o

RE: [firebird-support] Need Speed Optimization for SQL

>Hi SET, > >Nice to see you here, but I am getting below error after executing your sql, i >think group by clause is needed, would you please help? Yes, sorry I forgot group by: with TMP(PK_JOB_ITEMS) as (select distinct PK_JOB_ITEMS from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7

RE: [firebird-support] Need Speed Optimization for SQL

>I have below SQL and DDL of respective columns used in this SQL. > >Below sql takes 6-7 seconds to give the result. > >Would it be possible to bring the output time to 1 seconds, coz there are some >other operartion I need to perform based on this sql output withing short >period of t ime. > >SQ

Re: [firebird-support] SQL puzzle: Order based on Prerequisite

>In a table called "Steps", I have the following fields: > >ID, B4Me, Dsc, > >-ID field contains a unique ID for each record >-B4Me contains the ID of some other record in the table that MUST appear in a >result set, BEFORE this record. B4Me may be null. This is called the "B4Me" >order. >-Record

RE: [firebird-support] V1.56 query killing my V2.54 app

>Ok, used the +0 and worked. > >On v1.56 I was used with setting up a high granularity data column (col04Int - >part of the primary key) with a True/false (0/1) type of column >(ColDetSmIntFlag) to boost the selectivity of the index >IXColDetSmIntFlag. I kept the index with that configuration for

RE: [firebird-support] Recursive CTE question

>Can the question be rephrased as you being interested in which sets are >identical, Elias? If I understand things correctly, >I would assume "double negation" to be the "simple" answer you're looking for: > >with tmp(id) as >(select distinct id from CRITICALPARAMVALS) > >select t.id, t2.id >from

Re: [firebird-support] V1.56 query killing my V2.54 app

>Actually, the problem isn't with FK_dettbl, but with the A Natural. In a >Master detail relationship with 450K+ rows in the master and 800K+ rows in the >detail, that "natural" is a killer. The problem is that Firebird thinks that PLAN SORT (SORT (JOIN (A NATURAL, B INDEX (FK_dettbl is a

Re: [firebird-support] Recursive CTE question

>Hi everyone! > >This might not be a straightforward Firebird question, but I'm hoping there's >a feature I'm unaware of that can help me beyond plain-vanilla SQL. > >I have two tables. The first is a list of names of "critical parameters," and >the second relates certain object IDs, critical pa

Re: [firebird-support] V1.56 query killing my V2.54 app

>Hi all, > >I'm trying to upgrade an app to v2.54 from v1.56 but there are some queries >that aren't planning the way they should. > >I have this query: > >Select Distinct a.col01Int, a.col02SmInt, a.col03SmInt, a.col04VarCh45 >from dettbl b > Inner Join msttbl a on (a.col01Int = i.col01Int and

RE: [firebird-support] Cast as Numeric without parenthesis

>then why here is an error? >  >SELECT CAST('123456789.23' as numeric) FROM RDB$DATABASE Logically speaking I don't understand why SELECT CAST('123456789.23' as numeric(9,0)) FROM RDB$DATABASE is failing when SELECT CAST('1.23' as numeric(1,0)) FROM RDB$DATABASE succeeds. Practically, I guess

RE: [firebird-support] Cast as Numeric without parenthesis

I would expect numbers in the range -2147483648 to +2147483647 to be accepted (the size of an integer), and then there’s a separate field for storing whether things are shifted to the left or right. I’ve no experience or theoretical knowledge confirming this, but your examples would fit such a d

RE: [firebird-support] Extract week of the year (within year)

>is possible to get week of the year "within year"? >I see that built in functions only support ISO-8601 (or i do not know some >parameter) Actually, Karol, “week within year” is not the week, Karol. 1 January could be week 53 and 31 December could be week 1. If you want to consider 1 January a

RE: [firebird-support] How To Generate Auto Increment Number in SQL Itself ?

>HI Louis, > >It's pretty cool, but i need only in SQL, that too same number for all records >for same employee and next incremental number for all records of the another >employee. > >SET any help offered ? The first thing that comes to mind, is EXECUTE BLOCK (which is basically a STORED PROCE

Re: [firebird-support] How To Display Null Values At the End For More Than One Columns Specified in Order By Clause?

>look at this sample > >CREATE TABLE XXX >( > A varchar(20), > B varchar(20), > C varchar(20), > D varchar(20), > E varchar(20) >); > >INSERT INTO XXX (A, B, C, D, E) VALUES ('AAA', 'BBB', NULL, 'CCC', 'DDD'); >INSERT INTO XXX (A, B, C, D, E) VALUES ('VVV', NULL, 'CCC', 'EEE', NULL); >INSERT I

Re: [firebird-support] How To Generate Auto Increment Number in SQL Itself ?

>>I have EmpCode column in my SQL >> >>Say for example, my sql returns total 100 records in that it returns 10 >>records for every employee. Here I would I display >>Row_Number as 1 for first 10 records i.e. for first employee, then for next >>10 records i.e. for second employee >>Row_Number as

RE: [firebird-support] Distributing/deploying Stored Procedures

>Thanks Martijn.  I had a quick look at DBWorkbench and it looks like a good >tool for getting a diff >between a dev and prod database and I will investigate it further, however it >doesn't address the >issue of distributing (or applying/executing) that diff when I don't have >access to the se

RE: [firebird-support] How To Display Null Values At the End For More Than One Columns Specified in Order By Clause?

Thanks for the SQL code, Vishal, you forgot to include b) (Parts of) the result you get c) The result you want Both of these are necessary to find out how to write the required SQL. Set

RE: [firebird-support] How To Display Null Values At the End For More Than One Columns Specified in Order By Clause?

>Table values are as you shown: > >MyTable >FieldA FieldB FieldC >1 2 3 >1 2 >1 2 >2 3 4 >2 > 2 3 Is this how you want them, or is this how they are? Please show us a) Your query (including the PLAN and definitions of the indexes used) b) (Parts of) the result you get c) The result you want

RE: [firebird-support] How To Display Null Values At the End For More Than One Columns Specified in Order By Clause?

>I am using Firebird database with 2.1 version. I have one issue in Order By >clause. >I have SQL which gives multile records for multiple employees, i.e. one >employee has multiple records as an output in SQL. >I have Four columns in Order By Clause and I want every columns Null value >should b

RE: [firebird-support] Cumulative queries

>If you want to query (management asks for) the yearly turnover of the company >from the start of the year to yesterday >and you have 100+ invoices ... It is time and resource consuming, even >with stored procedures. If I have monthly >archives (tables) then I have to query each of the table

RE: [firebird-support] Index

>Hello everybody, > >I'm looking in a table for an existing record bevore I insert it. Now I would >set some indices. There are 4 fields I compare. >It is better I create one index with all 4 fields or for every field one? Difficult question with several potential answers. Generally, one combin

RE: [firebird-support] Re: Query that never ends (Firebird 2.5.3)

>if I switch t1 and t2, it works very quick but it does not fit the needs (I >have to rewrite all to work in this way). >But I really don't understand way the original query is working that slow; if >I run t1: >select a1."BJAR",a1."TKEY",a2."OWNR",a2."TIPO",a2."DSCR" as "ADSCR" >from "TL1_1" a1

[firebird-support] Re: Query that never ends (Firebird 2.5.3)

Slow: > select t1.*, t2.* from > (select a1."BJAR",a1."TKEY",a2."OWNR",a2."TIPO",a2."DSCR" as "ADSCR" from > "TL1_1" a1 > left outer join "BJAR" a2 on a1."BJAR"=a2."BJAR") t1 > left outer join > (select b1."BJTX",b1."TKEYT",b2."OWNR" as "TOWNR",b2."BJAR",b2."DSCR" as > "TDSCR" ,b2."VOCE

RE: [firebird-support] Hang On ExecuteNonQuery Using Firebird2.5 with visual studio 2010

>    Select first(1) u.UNIT_ID from m_unit u >where (upper(trim(u.UNIT_NAME))= upper(trim(:MAIN_UNIT)) >or upper(trim(u.UNIT_SYMBOL))=upper(trim(:MAIN_UNIT))) > and coalesce(CONVERSION_UNIT_ID,0)=0 Into :Main_Unit_Id; Does this select use any index at all? How many records are t

RE: [firebird-support] Hang On ExecuteNonQuery Using Firebird2.5 with visual studio 2010

>For iLoop as Integer=0 to grid.Rows.Count-1'Grid Has 100,000 Rows >'-Sometimes Hang Here Also- >rdr = Get_Reader("USP_CHECK_AND_GET_ITEM_EXIST('" & >Mid(Trim(dicFields_Name.Item("SP_ITEM_CODES")), 1, 20) & "','" & >Mid(Trim(dicFields_Name.Item("ITEM_PART_CODES")), 1,

RE: [firebird-support] Hang On ExecuteNonQuery Using Firebird2.5 with visual studio 2010

Sukhen wrote: >>>update never fails but it fails in insert query. loop run in a single >>>connection with a new datarows in same query. I answered: >>I think it is about time to show us some code (not all). Could it be that >>another transaction not visible to your current >>transaction has ins

RE: [firebird-support] Hang On ExecuteNonQuery Using Firebird2.5 with visual studio 2010

>update never fails but it fails in insert query. loop run in a single >connection with a new datarows in same query. I think it is about time to show us some code (not all). Could it be that another transaction not visible to your current transaction has inserted a row that creates a lock conf

RE: [firebird-support] Hang On ExecuteNonQuery Using Firebird2.5 with visual studio 2010

>>please, somone guide me where i am wrong >> >> This support list is for the Firebird database. For the .NET >> providers, you need to subscribe to the firebird-net-provider list: >> List-Subscribe: >> , >> >At first gl

RE: [firebird-support] Multiple WHERE clauses

You may consider WITH TMP(My72, cFrom, cTo, dValueFrom, dValueTo) as (SELECT CAST(:My72 as integer), US1.dValue, US2. dValue FROM RDB$DATABASE LEFT JOIN dSetValue('Current Year', 'From') US1 ON (1=1) LEFT JOIN dSetValue('Current Year', 'To') US2 ON (1=1)) SELECT FROM TMP t CROSS JOIN tDevelopment

RE: [firebird-support] Charset in ISQL: How to show special characters

>>my impression was the other way arround ;-) > >Then I'm either misreading your e-mail, or you've switched the examples around? > >>When I change the charset in isql to any charset, that is definitly not >>used in the database I always get the same output from isql. I was >>expecting that the pr

Re: [firebird-support] Query too slow

>Hi; > >I am using FB 2.5.3. in a fast environment (win8 64bit - QuadCore 3.4 Ghz - 8 >GB ram). > >If I run this query it goes very very fast and returns 84 records: > >Preparing query: select distinct "PROC" from "GEST" where ("GEST"."FSAL" >between '20080801' and '20080812') order by "PROC" >Pr

RE: [firebird-support] Know nothing - please help

>I have a query like this: > >select >   artikli.a_sif, >    v1.v_txt, >    v2.v_txt, >    coalesce(sum(magkol.k_total),0) total, >    coalesce(sum(magkol.k_nabcen * magkol.k_total),0) vrednost, >    coalesce(sum(magkol.k_nabcen * magkol.k_total),0)/ >coalesce(sum(magkol.k_total),1) nc, >    coale

RE: [firebird-support] How to join records

>Thank you Ann, Thank you Set, >yes, in my real situation I have to extend the query to other fields from T2 - >therefore the second solution seems to be the better one for me. >Could "Common Table Expressions" or "Derived Tables" bring any advantages? I suppose you could use a combination of the

Re: [firebird-support] How to join records

>> On Feb 16, 2015, at 4:22 AM, josef.gschwendt...@quattro-soft.de >> [firebird-support] wrote: >> >> we have 2 tables >> For each record in Table1 there are 2 records in Table2. >> >> Table1 (T1) >> == >> 1 >> 2 >> >> >> Table2 (T2) >> >> T2T1 >> == >> 11 >> 21 >> 3

SV: [firebird-support] Re: Grouping without sorting alphabetically

>Thanks Set for starting me on the right track. Please see my finalized query >below which gets me to the expected result. Good to see that you got the result you wanted, Bhavbhuti! I do have two comments regarding your final result: 1) Why do you use ORDER BY within the CTEs? In the outer sel

RE: [firebird-support] Re: Grouping without sorting alphabetically

Dataset: 1Haemogram Report 1Routine Haemogram 1Haemogram Report 5Diff. Count (Mature Cells) 1Haemogram Report 10 Special Investigations 2Haemogram Report

RE: [firebird-support] Re: Speed issues

>I am having similar issues. Are there any resources to read up on this? What do you mean by "similar issues", Louis? Zoran first showed us his SQL and plan, so that we more or less could see that his SQL, plan and indexes seemed good (that's arguably the most common reason for speed problems).

Re: [firebird-support] nesting - weight calculation

There is at least a good alternative, Frank Ingermann showed how to do something very similar when calculating the amount of ingredients required when baking a marble cake at a Firebird conference a few years ago. He used a recursive CTE. Whether a recursive CTE is simpler than a recursive proce

Re: [firebird-support] Unlock record

>I saw the same as you, and made the following test: > >- Opened my application. >- Executed the option which locks a record (executes a SELECT * FROM TABLE1 >WHERE (FIELD1 = 'some value') WITH LOCK). >- Opened the database with IB_SQL. >- Looked in table MON$STATEMENTS. I found many records. Many

Re: [firebird-support] Common Table Expressions (“WITH ... AS ... SELECT”)

Sorry for top-posting, I'm not currently at a real computer. I guess you'Re right in that the CTE is called once for each iteration. Since you're joining on output and not input fields, that probably would not be necessary, but maybe Firebird doesn't optimizer such - somewhat unusual - cases.

Re: [firebird-support] Unlock record

>I looked in a database, and in MON$STATEMENTS tabla I have the following >fields: MON$STATEMENT_ID MON$ATTACHMENT_ID MON$TRANSACTION_ID MON$STATE MON$TIMESTAMP MON$SQL_TEXT MON$STAT_ID >How can I detect which is the record I have to delete to unlock my record? >This is the only table I have to

SV: [firebird-support] Unlock record

> May be I can modify or delete some record in any MON$ table or do some other > thing? I typically locate and delete records from MON$STATEMENTS. Mind you, that is 2.5 and typically when I have a long-running query. Don't know whether deleting from this table is possible or safe with 2.1 or s

Re: [firebird-support] working days between two dates

>Hi Tomas! I just mean week days... > >Something like DateDiff() but only counting from monday to friday. > >I have a function in Delphi for that. I could make an UDF I think. I just >wander if there is something already done. I thought it was something "very >used"... probably is not? I don't k

[firebird-support] Re: Deadlock exception occurs but it shouldn't?

>"The main purposes of explicit locks are (1) to prevent expensive handling of >update conflict errors in heavily loaded application" > >this is the exact reason I try to avoid conflicts. My threads can wait but >they should not raise exceptions. > >I always though that it is possible to avoid su

[firebird-support] Re: performance of subselect with group by

>Hello, > > thanks Set, makes my test unnecessary! > > But I'm thinking a little bit about my original problem. > > Would it make sense to add a tracker entry for optimization of > subselects without reference to outer query? > > I think that they should get evaluated and transformed to someth

[firebird-support] Re: performance of subselect with group by

>> I don't think there is any simple way to make a delete with a >> subselect as the only part of a where clause perform great on >> largish tables. That is, using EXECUTE BLOCK (which doesn't exist on >> older Firebird versions) should perform OK: >> >> execute block as >>declare variable id

[firebird-support] Re: performance of subselect with group by

>Hello, > > Thanks for the answer. > How can I optimize if I want to use a DML command in conjunction > with a subselect, e.g. > > delete from test where Id in ( > select min(t.Id) FROM test t > group by t.reference, t.key > having count(*) > 1 > ) > Hi Björn! I don't think there is any simpl

Re: [firebird-support] Performance optimation?

>Hello, > >I save values in some tables (simpler description) > >First a Table who saved the timestamp of the mensuration >Table A timestamps >ID primary key >TS timestamp > >Second a Table with the measured data (25 records/measured sensors will be >saved every 10 Minutes, one record in Table A,

Re: [firebird-support] group by date

>Any ideas? > >Hello @ll, > >I would like to group my records by day or month. The date is saved as >timestamp. If I do the following, > >SELECT EXTRACT(day FROM m.messzeit), COUNT(*) as CountMessages >FROM te_messzeiten m > >GROUP BY EXTRACT(day FROM m.messzeit) > >I get all days (1 to 31), but I

RE: [firebird-support] Firebird 1.5 - CURRENT_TIMESTAMP difference

>Hey guys, > >I'm having some issues with a time difference on a server - the software we've >built around Firebird checks that the client and server do not have a >time different of more than 30 minutes otherwise certain functionality is >disabled. > >Now the server time is fine, but when query

RE: [firebird-support] Grouping SQL counts

>FB 1.5x > >I have a SQL statement that returns the results I want--giving me a count on >the detail dataset >(ClientRegHistList is a detail list on ClientRegHis, more info below) > >  select C.RegDate, Count(Client_ID) >    from ClientRegHist C, > ClientReghistList CL >   where (CL.Clie

Re: [firebird-support] Like and database entry "-"

>"where name like 'Ab%'" returns any entry of the table, where name start with >"Ab" except those ones containing character "-". Using "starting with" or >"containing" >the same database entries are missing. Firebird simply doesn't exclude entries depending on which character follows what you'

RE: [firebird-support] Re: Firebird database seems to influence multi user ability

>I have been an interbase guy about fifteen years ago and loved to see it go >open source. I used to work with Paul Beach (Hello, if you're here,Paul) Yes, Paul Beach is still here, he’s working with other InterBase and Firebird experts at IB Phoenix. Here you can see who they are: http://www.i

RE: [firebird-support] Why this similar to is wrong?

>select  >iif('em...@testdomain.com' similar to >'([_a-zA-Z\d\-\.]+@[_a-zA-Z\d\-]+(\.[_a-zA-Z\d\-]+)+)','ok','fail') >from rdb$database > >Says 'invalid string' and 'invalid pattern', but the pattern it´s the same in >other languages for validate email address. I know nothing about regular expre

RE: [firebird-support] Left outer join with stored procedure

>Hello All, > >I have a table regioni with 164 rows (r_id), and also stored procedure which >returns some data like (r_id, value). >When I try to use > >select > >regioni _ID, >stored_procedure_value > >from regioni >left outer join stored_procedure (:DATE1,:DATE2) > >I only get number 20 rows for

RE: [firebird-support] Re: Cross database update

>Dear Thomas, >thank you for your answer. I have experienced that pssing by the trigger is >functioning well. > >The only thing wrong is that the loop has no effect on the data of the >records. And I hoped that someone could check the statement syntaxwise. Maybe >I am doing something wrong in my

RE: [firebird-support] sum data into single row per day

>Thank you Svein, that's what I tried but kept getting: >SQL error code = -104. >Invalid expression in the select list (not contained in either an aggregate >function or the GROUP BY clause). > >and yet it now works. I thought it must have been because I was accessing code >in the iif(...). I mus

[firebird-support] Re: Why does the optimizer choose NATURAL for this tiny table?

>> D1) ON p.icd10 = t.icd10 >> >> These change the plan to: >> PLAN JOIN (MERGE (SORT (JOIN (P NATURAL, G INDEX >> (I_ICD10_GRUPPE_ICD10))), SORT (JOIN (T KID INDEX (U_NPR_KRG_ID_FNRK), >> T N INDEX (I_NPR_KRG_ID), T T INDEX (I_NPR_TILSTAND_ID_NPR, I >> INDEX (PK_ICD10)) > >Interesting. The

RE: [firebird-support] sum data into single row per day

> for select > iif(n.code=1,sum(n.amount),0), > iif(n.code=2,sum(n.amount),0), > iif(n.code=3,sum(n.amount),0), > n.sell_date > from new_table n > where n.sell_date between :fromdate and :uptodate > group by n.sell_date,n.code >

[firebird-support] Re: Why does the optimizer choose NATURAL for this tiny table?

Hi Sean/Dmitry, I've mingled my answers to your suggestions. Does the plan change to INDEX if the condition would be: S2) on p.icd10 in (t.icd10, t.icd3) S3) on p.icd10 = t.icd10 or p.icd10 = t.icd3 D2) ON p.icd10 = t.icd10 OR p.icd10 = t.icd10 No, these all keep the original plan: PLAN JOIN (J

[firebird-support] Why does the optimizer choose NATURAL for this tiny table?

Fb 2.5.1 I have an EXECUTE BLOCK statement, for which a small part goes NATURAL where I'd expect it to use an index associated with a UNIQUE CONSTRAINT. I would expect JOIN icd10_purres p ON p.icd10 IN (t.icd10, substring(t.icd10 from 1 for 3)) to use an index, maybe something along the lines

RE: [firebird-support] What is wrong with transactions here?

>> Hi! >> >> I have some lines in firebird.log like this: >> === >> cxv.servers.netTue Nov 4 11:37:38 2014 >> Sweep is started by SWEEPER >> Database "SkyNetInt" >> OIT 1221095, OAT 1231335, OST 1231313, Next 139981575359934 >> >> >> cxv.servers.netTue Nov 4

RE: [firebird-support] update optimization problem

>Hi. >in firebird 2.5 > >Simple descrip tables > >table_ud >ud_id integer not null primary key >field_ud varchar(6) > >row count: 383322 > >table_tmp >ud_id  integer  >cnd    integer  > >index (cnd) > >row count: 617 > >UPDATE table_ud ud >SET ud.field_ud = '201401' >WHERE ud.ud_id in (SELECT t.u

Re: [firebird-support] How do I count the number of duplicate rows in a table?

>Greetings All, > >Firebird 1.5.3 > >Should be elementary but, I'm drawing a blank on how to accomplish this. > >I have a table (ACCT_CASE_COURT) that contains these fields (among others): > >ACCT_CASE_COURT_IDINTEGERNOT NULLPK >ACCT_IDINTEGERNOT NULL >CASE_IDSMALLINTNOT

Re: [firebird-support] VarChars sometimes begin with single or double quote

>I've not been able to find any topic that comes close to addressing my >question, and it seems so basic, I'm guessing that I'm missing something >fundamental. > >I have queries that specify for ranges in varchar fields and return those and >populate my VirtualStrings in a standard alphabetical

Re: [firebird-support] PSQL: Getting both aggregation and individual rows

u measured how much more time is required to return the entire result set if having the aggregate functions compared to a simpler query without them (but with ORDER BY since that makes it more easily comparable)? Set On 21.10.2014 21:15, Svein Erling Tysvær svein.erling.tysv...@kreftregist

RE: [firebird-support] how to have different (conditional) order by clause with same select

>create or alter procedure my_SP1 ( > index_by integer) /* the order I want */ returns ( > myfield1_out char(15), > myfield2_out char(15)) >AS >begin > for select > myfield1,myfield2 > frommytable > case > when index_by=1 > then order by myfield1,myfi

Re: [firebird-support] PSQL: Getting both aggregation and individual rows

What about FOR WITH TMP(MyDateTime, MySum) as (SELECT MIN(dateandtime), SUM(Value) FROM gen_data(...)) SELECT g.id, g.name, g.dateandtime, g.value, t.MyDateTime, tMySum FROM gen_data(...) g CROSS JOIN tmp t Of course, you may want a different join to CROSS JOIN. HTH, Set ___

RE: [firebird-support] Request new feature - better perfomance

>>Maybe I was not clear as I should be but here is an example in Firebird: >> >>select something,anotherthing,(select sum(thirdthing) from second b where >>b.something = a.something) total from a where >>(select sum(thirdthing) from second b where b.something = a.something) > 10 >  >tray sentence

RE: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?

>ACCT_CASE: Case Management table >ACCT_IDINTEGER    NOT NULL    PK >CASE_ID    SMALLINT    NOT NULL    PK >CLT_ID    INTEGER    NOT NULL    FK to CLIENT table  << Need this for the JOIN   >DEBTOR_CASE_DEBT:  Allows for multiple PERSON's to be associated with a DEBT >ACCT_IDINTEGER    NOT N

RE: [firebird-support] BLOB

>>Text, binary, it's all the same to gbak.   Try the -g suggestion - if gbak is >>cleaning out garbage, it's slow. > >But, surely, the garbage has to be cleaned out sooner or later, and doing it >in a backup run is the cheapest way to do it, >as every record is being visited anyway? (Compared to

RE: [firebird-support] BLOB

>my BLOB records are plain text phrases, somewhere between 1 and 400 char-s , >containing setup information (in Delphi I save a TStringList to file and I >backup that file in my database). > >My question was about saving these tables with gbak -v (verbose) where I can >see the time spent to back

[firebird-support] Re: Simultaneous inserts / selects

>All values for inserts are converted to string so there are no parameters. Sorry to hear that. MyStatement.SQL.Text:=’INSERT INTO MyTable(Field1, Field2) VALUES (:Param1, :Param2)’; MyStatement.Prepare; while not eof(InputFile) do begin readln(InputFile); MyStatement.Params[0].AsInteger:=;

Re: [firebird-support] DATEDIFF(HOUR...) returns 1 for a 45 minutes interval

>Hi all > >I would have attached a screen shot of the sample data but I guess that is not >possible. What I am trying to achieve right now is get the time difference in >hh:mm format to the user > >tStartTime and tEndTime field values > >26.08.2014, 08:38:00.00026.08.2014, 09:45:00.000 >26.0

Re: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2

>Obviously the logic of my application is not the best, but it needs to pass >through a special interface, that transforms classic cobol read/write >routines, in sql statements. This brings to the need to have a single >commit after a single insert command of a single record. I know this is not >g

Re: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2

>Hi Sean, thanks for the contribution. > >Some answers to your requests: > >1) Logic of application. > >It is a Microfocus Cobol legacy application, with latest (2014) x64 runtime. >The long time is obviously not relative to a simple sql command, but to the >overall execution. >We have deve

  1   2   >