Re: [firebird-support] Statement freezes firebird

As Karol writes, introducing a descending index on the ID field ought to make your query quick (Firebird indices - and keys - are unidirectional). Firebird has never been good with IN(), I was bitten around 1999, changed to using EXISTS and for the last 20 years I have never missed IN() (and

Re: [firebird-support] Plan problem en CTE

Your query is most likely incorrect. The LIST function returns a VARCHAR, so if x returns, say, two rows with 5 and 7, then list will create the string '5,7' (or '7,5', your query does not specify any order) and IN ('5,7') is very different from IN('5', '7'). Your query would only return rows

Re: [firebird-support] Is there any logical difference?

Well, Karol, I would say that logically speaking they may be thought of as different. Though on this computer, running an old 2.5 Firebird version, I'm not capable of making two such statements differ in results and a very superficial internet search indicates that EXISTS always return true or

Re: [firebird-support] Query that was very fast under the oldest 2.5 versions are very slow under 2.5.8 or 2.5.9

One of the first things Firebird taught me (well, I don't remember whether if it was Firebird 0.9.4 or InterBase 5.6), was to never use IN(). At that time, the optimizer didn't check if the subselect was correlated(*) or not, and hence executed the subselect for every possible row in the outer

Re: [firebird-support] Consecutive values from different fields

>I have a question if you can help me PLEASE. > >Using a Firebird SQL query is it possible to count the consecutive number of matching values in different fields? >I need to determine the LAST consecutive days a driver has been working. > >DPID  WEEKDATE   DRIVERNR DRIVER D1  D2 

Re: [firebird-support] Working with SQL (might be using loop)

Hi Vishal! SQL puzzles can be interesting, so I decided to have a go at your quest. with recursive tmp ( MyInput ) as ( select cast( :InputString as Varchar( 500 ) ) from rdb$database ), WordLetter ( ThisChar, RestInput, WordPos, CharPos ) as ( select substring( lower( MyInput ) from 1 for 1 ),

Re: [firebird-support] How can I correctly write this WHERE

The reason the first query failed, Bhavbhuti, was because I forgot that nested CASE also needs nested END, so adding END to the end of the query should work on Fb 2.5 (and thanks Mark, for showing me a part of Firebird 3 that I didn't know of). Still, even though it should work, it will be

[firebird-support] Puzzled by difference in execution time

I was very surprised by what I observed in Firebird (probably 2.5.4) yesterday and today. After importing 3000 records into a temporary table (on commit preserve), I wrote an (selectable, but containing update) EXECUTE BLOCK that updated a normal table (updating 3000 of 1 million records)

Re: [firebird-support] In() Question

Den 31.01.2018 10:08, skrev 'Check_Mail' check_m...@satron.de [firebird-support]: Hello @ll, I have a configuration table with stations, who can be only one order active and stations, where it isn’t so. The list contains all stations, who only one order can be active. For example, if

Re: [firebird-support] Which Firebird ADO.Net client is compatible with Firebird server v2.0.x?

As a shot in the dark (since it is not consistent, but more of a problem on a slow computer): Could it be related to some timeout issues, and that increasing ConnectionTimeout or CommandTimeout could help? But Mark is right, you should post your problem on another list with far more detailed

Re: [firebird-support] Query and problem with nulls

>LEFT JOIN DOC_TESTA DT ON D.ID = DT.DEPOSITO_ID, DOC_CORPO DC Ouch, Luigi, I don't think I've ever seen anyone using an implicit join after an explicit left join like this before! I have no clue how Firebird handles this (and I'm uncertain if I would want to know it or not). Generally, you

Re: [firebird-support] Problem with CONTAINING and COLLATE - UTF8

>After the original "Problem with CONTAINING and COLLATE" thread I switched to UTF8 but not happy with it. >The optional COLLATE after CONTAINING have no effect, the database default COLLATION used. See the below >examples. Tried with 3.0.3.32854 snapshot and Linux 64bit (Ubuntu 14.04.1).

Re: [firebird-support] Performance problem - input wanted

Just thought of a very simple test you could run, Michael. Is the plan identical for the following query on the old and new server? select * from rdb$database where not exists( select v509Index from VAREFRVSTR_DETAIL where VarePlu_ID=:PVarePlu_ID and   Afdeling_ID=:AfdNr and  

Re: [firebird-support] Sorting-Problem on recursive query (window functions)

Hi again, Josef! I like SQL puzzles, and decided to spend a bit of time this afternoon trying to solve yours. Not using Fb 3, my knowledge of windowing functions is too limited to offer any such answer, but I found something that seems to get the result you want in Fb 2.5. Your main problem is

Re: [firebird-support] How I can speed up this query

11.10.2017 19:43, Luigi Siciliano wrote: > Hallo, > > I have this query > > SELECT > ID, > DENOMINAZIONE, > SCADENZA, > PARTITA, > NUMERO_DOCUMENTO, > DATA_DOCUMENTO, > IMPORTO, > IMPORTO - PAGATO AS RESIDUO, > PAGATO, > PAGAMENTO, > SALDARE > from > ( >

Re: [firebird-support] Find duplicate multi-row entries

Sorry again, Daniel, I had to get to a place where I could test things before replying. My problem was that what I'd done before was to try to find identical sets, not trying to find unique sets, and that made me mess up the logic. I hope this query will get you what you want (and this time

Re: [firebird-support] Re: Firebird 2.5: Nested querys help

Den 31.08.2017 21:01, skrev duque.herna...@yahoo.com [firebird-support]: Set, thank you for your help. I'll like to explain my self better. If having these records: table_id date_time customer_id other_fields --- 001

Re: [firebird-support] Firebird 2.5: Nested querys help

>One customer could have several records in the same date and I have to make a SELECT only to the first table_id for a given date for >each customer_id. I have worked around the query with something like this but I haven't succeed: Hi Hernando! Admittedly I have tried to guess what you mean

Re: [firebird-support] Is it possible IF then statement in where clause?

> < snip> > from > > PN_TESTA PNT > JOIN PN_CORPO PNC on PNT.ID = PNC.PN_TESTA_ID > WHERE > PNC.CONTO_ID = :CONTO > > if :CLIENTE is not null then <-- *IS NOT ACCEPTED* > > AND IIF (PNC.CLIENTE_ID IS NOT NULL, PNC.CLIENTE_ID, > PNC.FORNITORE_ID) = :CLIENTE) >

Re: [firebird-support] Firebird 3: NOT EXIST vs NOT IN

>I have stored procedure developed under FB 2.1 and it's very, very slow (more than 10 minutes) under FB 3 because it has NOT IN condition in WHERE clause: >not SHIPPING.ORDERNO in (select orderno from invoices) >I wrote equivalent NOT EXIST statement with the same result: >NOT EXISTS(SELECT

Re: [firebird-support] FIRST 1 of each iItemID

> I have a view and the following query which returns multiple records from the view (see below) for each iItemID > > SELECT iItemID > , cID > , dDt > , bRate >FROM vwPriceListHistory >ORDER by iItemID, dDt DESC, cID DESC > > What I want is one first

Re: [firebird-support] How to get different Maximum Data for every single date in the table?

and feeling happy to learn valuable things from it. Thank You So much SET. But I would also prefer to wait for another solution. But no hurry, take your time. Whenever you are free to look into this, please have a look. On Friday, 21 July 2017 2:26 AM, "setysvar setys...@gmai

Re: [firebird-support] Simple post edited data to table raises exception after converting FB 2.1 to FB 3.0

Den 20.07.2017 16:53, skrev fredf...@hotmail.com [firebird-support]: I used Delphi's FireDAC components and FireDAC Monitor program which tracing issues with Firebird. I posted extracted data from tracing file after user hit save button to save changes in address1 line - it's very simple

Re: [firebird-support] Doubt (Problem) In Writing SQL

Den 17.07.2017 13:16, skrev Vishal Tiwari vishuals...@yahoo.co.in [firebird-support]: Hi SET, Thank You for the reply. Not an issue, I could understand. Before I execute this SQL, would like to learn something pretty interesting logic in the SQL you provided. I got the intention of using

Re: [firebird-support] Doubt (Problem) In Writing SQL

Den 05.07.2017 20:15, skrev Vishal Tiwari vishuals...@yahoo.co.in [firebird-support]: Hi SET, Just for you my Friend... Please check... CREATE TABLE BOOK_SELLING_DETAIL ( BOOK_NO Integer, BOOK_ID Integer, PUBLISHER Varchar(50), MAIN_BRACH Varchar(10), PUBLISH_DATE Date,

Re: [firebird-support] question cte separately

> Hello, > > at the moment, I’m in struggle with a CTE: > > for with recursive ok as > (select a.pos, b.kennzeichen, b.price, b.id from t_l_prkom_grp a inner join > t_l_prkom_grp_pos b on a.id = b.id_kom_grp where a.id_kom = :id_kom > union all > select c.pos, d.kennzeichen, d.price, d.id from

Re: [firebird-support] CTE Question

Hi Olaf! First, SQL doesn't like unknown columns, you need to know at least the maximum possible number of properties to support. Having said that, you could try something like: select B1.Property, B2.Property, B3.Property, B4.Property from TableA A1 join TableB B1 on A1.ID = B1.ID_TableA left

Re: [firebird-support] QUERY QUESTION

Den 02.03.2017 17:36, skrev 'Stef' s...@autotech.co.za [firebird-support]: > Good day, > > I have a question for you SQL boffins, how can I query a table to get the > count of consecutive days found ? > > A Table contains rows with dates where an item(s) has been added for every > day this item

Re: [firebird-support] Partial update

Den 13.02.2017 20:40, skrev Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]: > Hello, All. > > If I use prepared statement like this "update table set field1=?, > field2=? where > field3=?" for performance reasons, is there a way to update only some fields > and leave > other

Re: [firebird-support] Full join and indices

Den 23.01.2017 13:05, skrev Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]: Hi, I just needed to do a full outer join between two very simple tables G and N, each with a single-column PK of identical type char(12). I noticed that the plan produces was: PLAN JOIN (N

Re: [firebird-support] Case sql

Den 05.01.2017 17:49, skrev 'Stef' s...@autotech.co.za [firebird-support]: > Hi Karol > > Thank you for the feedback, I understand what you mean, but how would I go > about adding or changing to get the “group by”? Hi Stef! I'm not Karol, but being that you want them on one line, I think you

Re: [firebird-support] Invalid token.invalid request BLR at offset 1745. Input parameter mismatch for procedure

I'm just guessing, Israel, but have you checked whether your calls to eqcalcpepssp(:icodemp, :scodfilial, :icodprod, :nsaldo, :ddtmovprodax, :icodempax, :scodfilialax, :icodalmox ) and eqcustoprodsp(:icodemp, :scodfilial, :icodprod, :dtpesq,'U',:icodempax, :scodfilialax, :icodalmox, 'N' )

Re: [firebird-support] Sequence

Olaf wrote 02.12.2016 10:43: >Also I can do the following: > >Priority in steps of 1 > >ID 10 Prio 1 >ID 20 Prio 2 >ID 23 Prio 3 >ID 11 Prio 4 > >Now the user can Set the ID 23 to Prio 2, how can I realize this without a loop? > >Update table set prio = newprio(1) where id = 20;

Re: [firebird-support] New auto-increment column, existing data

Den 15.11.2016 20:58, skrev 'Some One' anotherpersonsomewh...@mail.com [firebird-support]: > I am trying to add new auto-increment primary keys for existing table. > > > This is what I have: > == > set term ~ ; > execute block > as > declare dbkey char(8); >

Re: [firebird-support] Problem in executing query using 'IN' statement.

Sorry for the error, I forgot that IN works on sets, not individual fields. So, change to: SELECT Tb2._ID, Tb1.DET_ID, COUNT(Tb3._Name) NAME_COUNT FROM TABLE_1 Tb1 JOIN TABLE_2 Tb2 ON Tb2.DET_ID = Tb1.DET_ID JOIN Table_3 Tb3 ON Tb3._ID= Tb1._ID AND CAST(Tb3.Time AS DATE)=

Re: [firebird-support] Problem in executing query using 'IN' statement.

Den 16.09.2016 15:34, skrev 'Joje' j...@codework-solutions.com [firebird-support]: I have a query in which I check whether an NAME _IDS of type varchar is in another table with NAME _ID column having data type as int. Also, ID_1 have ID’s separated by comma’s example ‘1,2,3,4,5’. Below is

Re: [firebird-support] After update trigger: the same values for new.somefiled and old.somefield

Den 28.08.2016 12:55, skrev 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support]: > I'm using Firebird 2.5.2.26539. Any chance that another trigger is changing the value? >>> I was thinking that myself, but Patrick said that it was the "old" value >>> which >> was being changed

Re: RES: [firebird-support] It works on FB 2.5 but does not work in FB 1.5

>CREATE OR ALTER VIEW ESPELHO( ... >Error Message is: >Invalid Token. Dynamic SQL Error code = -104. Invalid Command. Data Type unknown CREATE OR ALTER VIEW was new in Firebird 2.5. I don't even think ALTER VIEW existed in Firebird 1.5, but CREATE VIEW should exist. >FROM EQMOVPROD MP,

Re: [firebird-support] Sorting CTE for List()

Hi Nikolaus! I've wanted sorting lists in Firebird myself, but to no avail. On http://www.firebirdsql.org/refdocs/langrefupd21-aggrfunc-list.html, I read: "The ordering of the list values is undefined" And, surely enough, your ORDER BY doesn't actually contribute to the order of the list -

Re: [firebird-support] Re: First-In-First-Out (FIFO) stock valuation

Den 07.04.2016 08:42, skrev venussof...@gmail.com [firebird-support]: Hi all Sorry for resurrecting this very old thread but I have come across a situation (hopefully Jose would be able to answer). I have found a bug in the bQty column it was not able to reduce itself to the current stock,

Re: [firebird-support] How to add previous column value to current value?

>I need something like this > >Name Date Days betwen Previous date >-- >Name1 date10 >Name2 date2date2-date1

Re: [firebird-support] Listing Table of Database in Order of Dependency

>Hi, Hi Rajiv! >How can i list tables of a database in order of dependency ie > >TABLE_1 (no foreign key references) >TABLE_2 (foreign key references, if any, in TABLE_1 ) >TABLE_3 (foreign key references, if any, in TABLE_1 / TABLE_2 ) >TABLE_4 (foreign key references, if any, in TABLE_1

Re: [firebird-support] Composite index - issue or not existing feature?

Hi Karol! At the risk of this being confusing or even incorrect (I trust Dmitry or Ann will correct if it is incorrect). >SELECT * FROM dbo.XXX X WHERE X.A BETWEEN 2 AND 30 *AND* X.B BETWEEN 5 AND 60 My understanding of this is that Firebird (in theory) have two choices. Either (a) use

Re: [firebird-support] How write a query with a progressive sum field

Den 07.03.2016 11:16, skrev Luigi Siciliano luigi...@tiscalinet.it [firebird-support]: > Hallo, > I need to write a query with a computed field that contain a > progressive sum like this table: > > DATA DOCUMENTO_ID NUMERO SERIE CARICO SCARICO SALDO > 01/01 A

Re: [firebird-support] query to return the last entry in detail table for each entry in master table

>I have an assignment table (master table) and a review table (detail table). The review table holds one or more records for each record in the >assignment table. I need a query to return the latest review for each record in the assignment table. Could someone advise me on a query to do

Re: [firebird-support] Trusted authentication for Standard users

>Hi, > > I noticed that for standard users with firebird trusted authentication, we need to mention the role name with the connection parameters. > > Can somebody give more details about it. > > Thank you. > > Regards, > > Sabu Hariharan Hi Sabu! I'm answering since no-one seems to

Re: [firebird-support] Querying between databases

Den 30.01.2016 15:35, skrev craig_...@coxcolvin.com [firebird-support]: I have many separate database files that have all the same structure, but hold data from different customers. In each database, I have a series of tables that are used to supply valid values through foreign keys. These

Re: [firebird-support] What is more effective execute block or select?

>If the question is which is likely to perform better, then the answer is the select. >Select statements can be compiled and optimized once and reused, avoiding >validating access and metadata references. The execute block must be compiled, >optimized, and access checked each time it's

Re: [firebird-support] Firebird 3.0 RC1 - SQL optimization help

Den 24.01.2016 09:04, skrev Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support]: > Hello, > > I needed some time to understand (at least try to understand) your post. > Please, note that my SQL knowledge is very limited. I really don’t know > internals of database any system. Though,

Re: [firebird-support] How to fill detail table with record secuence into master table

Or (if he wants a number starting from 1 for each master, as opposed to a unique number for each master): execute block as declare mId integer; declare mOld integer; declare dId integer; declare dmId integer; begin m_Old = -1; dmId = 1; for select id, master_id from

Re: [firebird-support] Is it possible calculate result of aggregates?

Den 22.01.2016 11:51, skrev Luigi Siciliano luigi...@tiscalinet.it [firebird-support]: > How can I do this query: > > Select > A, > B, > SUM(A + B) as C, > C * A as D -> FB result on "C not Exists" error! > from > table > group by > A, > B > > Of

Re: [firebird-support] An SQL query perform badly after firebird database file size grow beyond 8GB

>Query: > >SELECT MAX(A.PostDate) MaxPostDate >FROM SL_CS A, SL_CSDTL B >WHERE A.DocKey=B.DocKey > AND A.Code='300-10001' > AND B.ItemCode='OCC' > AND B.UOM='UNIT' > AND A.Cancelled='F' > AND B.UnitPrice<>0 > >Good plan: >PLAN JOIN (B INDEX (SL_CSDTL_ITEMCODE), A INDEX

Re: [firebird-support] Execution speed with update query

Den 06.01.2016 14:42, skrev Sonya Blade sonyablade2...@hotmail.com [firebird-support]: > update elements E set E.END_I = (select n.node_num from nodes N > where abs(E.X_I -N.XI)<0.001 and abs(E.Y_I - N.YI)<0.001 and > abs(E.Z_I-N.ZI)<0.001 ) The above query cannot possibly use any index for the

Re: [firebird-support] select parent based on multiple child conditions

Den 19.12.2015 20:38, skrev 'Daniel Miller' dmil...@amfes.com [firebird-support]: > With correction for one or two typos - those both worked, thank you! > Had to read, and read, and read - to try to understand HOW these work. > Which was exactly what I was hoping for - to better understand usage

Re: [firebird-support] select parent based on multiple child conditions

Here's two alternatives for you, they should give the same result (though provide different options for modifications, see the comments inside the SQL), pick the one you prefer. Both differ from your SQL in that rows are also returned when there are no routes, whereas your SQL would require at

Re: [firebird-support] Creating and filling a backup table for testing purposes (FB2.4 W10 x64)

Hi Andrea! Creating a table is DDL (data definition language), inserting into it is DML (data manipulation language). I NEVER mix DDL and DML in the same transaction even though it is possible (though I don't think you can use a table before the transaction that created it is committed).

Re: [firebird-support] string difficulty

Den 07.12.2015 13:55, skrev 'checkmail' check_m...@satron.de [firebird-support]: > .. and if the ID has the length of 15 chars and only the las * is for filling out to 16, i get a wrong result too. > > Can I compact my code? > > if(char_length(str)>16) then > begin > str =

Re: [firebird-support] Deleting records and deadlocks

Den 02.12.2015 13:24, skrev Ann Harrison aharri...@ibphoenix.com [firebird-support]: >> On Dec 2, 2015, at 6:35 AM, Tim Ward t...@telensa.com [firebird-support] >> wrote: >> >> What about if two concurrent transactions are both trying to *delete* >> the *same*

Re: [firebird-support] FB 2.5.4 - new handling of IN SUBSELECT?

Den 05.11.2015 12:12, skrev Josef Kokeš j.ko...@apatykaservis.cz [firebird-support]: > Hi! > > I wonder: Was there any change in FB 2.5.4 which would slow down > processing of WHERE field IN (subselect) a lot, compared to FB 2.5.3? I > have been performing tests because a user of mine complained

Re: [firebird-support] SQL Optimation best way

>A.field1 and field2 are the intern project number, b.fielda and b.fieldb are the extern ordernumber year and number. >Sum(A.field3) where a.field1 = condition1 and a.field2 = condition2 >In the same statement I would include >Sum(b.fieldc) where b.fielda = a.field4 and b.fieldb = a.field5

Re: [firebird-support] It´s a bug or whatever?

Den 29.10.2015 19:22, skrev hamacker sirhamac...@gmail.com [firebird-support]: Hi everybody. When I try: select (2183.48/(1-(37.25/100))) as valor from rdb$database As result Firebird: 3465,8412 But other databases(MSSQL) and spreadshets(Excel and Calc) the correct result is 3479,6494023904

Re: [firebird-support] High CPU use after restore

Hei Jardar, I have never even heard of Nortek before! >We have DB of about 40GB where transaction counter exceeded max and we had to backup and restore to get the db back up an running. >However after doing this we have had trouble where the DB consumes 25% CPU (100% on one core). This

Re: [firebird-support] High CPU use after restore

Den 28.10.2015 20:56, skrev Jardar Maatje jardar.maa...@nds.nortek.no [firebird-support]: Hi again A bit more details. I found the query the slows this down and it is a query that includes blob data. If I adjust the query to cast the blob to varchar first the query executes 30 timers

Re: [firebird-support] How to replace NOT IN with JOINs

>CREATE TABLE AAA_TABLE_A >(ID D_INTEGER); > >CREATE TABLE AAA_TABLE_B >(ID D_INTEGER, AAA_TABLE_A D_INTEGER, MY_VALUE D_INTEGER, MY_VALUE2 D_INTEGER); > >DELETE FROM AAA_TABLE_A; >INSERT INTO AAA_TABLE_A (ID) VALUES (1); >INSERT INTO AAA_TABLE_A (ID) VALUES (2); >INSERT INTO

Re: [firebird-support] Re: invalid request BLR at offset 667 bad parameter number

> from orgchart p, orgchart c > inner join organisation pn on pn.orgid = p.parentid Don't mix implicit and explicit JOIN in the same statement, change to something like: for select p.parentid from orgchart p inner join orgchart c on p.childid = c.parentid and

Re: [firebird-support] How To Speed Up Update SQL

>>1. EXECUTE BLOCK and there for select and delete >I am completely unaware about execute block. Would you offer any help, so I could learn from it. If PK is your integer primary key for MY_BOOK, then you should be able to do something like this: execute block returns(Changes integer) as

Re: [firebird-support] Optimize query for date ranges

>SELECT * FROM ARBEITSEINTEILUNG AS a where a.baustelle = '12345' AND >a.DATUM >= '05.10.2015' AND a.DATUM <= '11.10.2015'; > >PLAN (A INDEX (IDX_ARBEITSEINTEILUNG1)) > >there are two indices on the field DATUM: One ascending, the other one >descending. > >Records DATUM >= '5.10.2015' = 102

Re: [firebird-support] Find grid page containing record

Den 06.10.2015 17:38, skrev Tim Ward t...@telensa.com [firebird-support]: > Given that a query needed to return data for a page of a grid is of the form > > SELECT FIRST 25 SKIP > .ID (and some other fields of human-readable data) > FROM < plus tables as needed for other fields in the > SELECT,

Re: [firebird-support] Using variables in ESQL select statement

Den 21.09.2015 19:12, skrev john_b_bis...@yahoo.com [firebird-support]: > We have tables and fields in a database which we need to create audit > logs for (any changes to fields require the audit table to be written to). > > However, we do not want to create specific trigger statements for each

Re: [firebird-support] Re: 2.5.1 upgrade dilemma

Den 22.08.2015 15:26, skrev Dmitry Yemanov dim...@users.sourceforge.net [firebird-support]: 22.08.2015 16:06, tvd...@ymail.com wrote: Rebuilding the indices is not the issue. It's just that the warning implies that rebuilding the whole database is really better than just rebuilding the

Re: [firebird-support] Join to same table twice - or joins vs subqueries

select r.id, (select a.node from nodes a where r.node_id = a.id) node, route_index, (select b.node from nodes b where r.link_id = b.id) link_node, (select c.quality_label from route_qualities c where r.quality = c.QUALITY) from routes r where exists(select *

[firebird-support] Re: FB does table scan as soon as I use left outer join in view - why?

Something like: select S.* from COLLECTION_ COLL left join SKIN S on S.ID = COLL.SKIN_ID where S.ID is not null i.e. fake the left join to get the correct join order (COLLECTION_-SKIN_-COLOUR_). I guess that means that select S.* from COLLECTION_ COLL join SKIN S on S.ID = COLL.SKIN_ID+0

Re: [firebird-support] Join to same table twice - or joins vs subqueries

Hi Daniel! select r.id, a.node node, route_index, b.node link_node, c.quality_label from routes r join nodes a on r.node_id = a.id join nodes b on r.link_id = b.id join route_qualities c on r.quality = c.QUALITY order by node, route_index; Is there an alternate way of writing

Re: [firebird-support] MAKE 'EXECUTE STATEMENT' USE INDEX

Den 18.08.2015 16:59, skrev 'Mr. John' mr_joh...@yahoo.com [firebird-support]: HI,in SP I have this query FOR EXECUTE STATEMENT 'SELECT SUM(CANT) FROM TABLE1 WHERE FIEL1='||:F1||' AND FIEL2='||:F2|| IIF(:pCondition=1,' AND FIEL3='||:F3,'') INTO .. DO .. TABLE1 PK =FIELD1,FIELD2,FIELD3

Re: [firebird-support] What is the best way to re-write this Stored Procedure that seems to be SLOW processing?

Den 15.08.2015 06:14, skrev 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]: Hello Set Why writing: PP.STATUS_CODE || '' will improve the SELECT, can you explain me? As far I know, adding '' is for use NATURAL in the PLAN, why the use of NATURAL will do a

Re: [firebird-support] Annoying: CTE CTE2 is not used in query

If I select from COMBINED, the sql runs but as below I get an error. This is pretty annoying when constructing a large SQL with many CTEs and you want to test each one and combinations of them. I can get why the error is there, but any other suggestion? with CTE1 as (select 1 as ID from

Re: [firebird-support] What is the best way to re-write this Stored Procedure that seems to be SLOW processing?

PLAN JOIN (PP INDEX (REFPERSON15,IX_PP_STATUS_CODE2,IX_PP_STATUS_CODE2),P INDEX (PK_PHONE)) Two more questions, Karol: How large percentage of the records in PER_PHONE have status_code G or V? What's the definition of REFPERSON15? Set

Re: [firebird-support] What is the best way to re-write this Stored Procedure that seems to be SLOW processing?

Two more questions, Karol: Sorry, I meant to write Mike. How large percentage of the records in PER_PHONE have status_code G or V? What's the definition of REFPERSON15? The PER_PHONE table currently contain 579,873 rows 91455 or 15.771% is ‘G’ 166075 or 28.639% is ‘V’ For a total of 44.41%

Re: [firebird-support] Need help in Firebird

Dear All, I have a question. I need to store some data in Firebird database. One of the data is a running serial number, 0 to 99. I try the Count SQL function, but sometimes after INSERT function I get same number 2-3 times. After I try to make a tabel which contain an INTEGER field, and

Re: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser

Also, if I remove the order by, IP reports a drastic reduction is execution time i.e. down to 6msec. Does the ORDER BY on the PK make such a difference? Unh, maybe. Between the list, my mail system and yours, I've lost most of the formatting and can't look at the original while typing

Re: [firebird-support] Recursive SQL-Question

I have a table in the database which represents a tree like the one below. Each tree-node is a record in the table and has a primary-key. 11 21.1Green 31.1.1 41.1.2Green 51.2 61.2.1 71.2.2Green 82 9

Re: [firebird-support] UserNames connected to my database

Hi. I have a problem. I use a Firebird 1.5.6 Server for a lot of applications, 5 databases on the same server. I need to find the number of connection for a single database, and the names of the users. For this i used IBX IBDatabaseInfo-UserNames-Count and

Re: [firebird-support] Possible to use FIRST 1 inside a group by?

Hi, I’ve got quite a tricky SQL query (well, at least for me it is, I’m hoping someone may have done this before) Here’s a simplified example of what I’m trying to do: I have a table with IDGroupID FKCode Value 1 1 ABC

Re: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Existence Predicates NOT IN Question

Existence Predicates NOT IN and ALL May Be Slow Firebird and, before that, InterBase, have produced incorrect results for the logical existence predicates ALL and NOT IN for many years. That problem has been corrected in Firebird 2.0, but the change means that indexes on the inner tables