Re: ODP: ODP: ODP: [firebird-support] SQL slower after N executions?

Sorry I can't help at all, Kjell, I'm all "old style query plan". Though I'm baffled by the new style changing when the old style remains and would love if someone could explain... Set fre. 1. mai 2020 kl. 11:56 skrev Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support] <

Re: ODP: ODP: ODP: [firebird-support] SQL slower after N executions?

Hej Kjell, I'm not used to seeing plans this way, but if I'm reading the plan correctly, then adding +0 or || '' (depending on the field type) immediately after your first union like this: select 'FöretagOmsättningsår' "Bärartyp", FtgOmsar.."ECO_ID" "Bärare", Uhant."ECO_ID" from

Re: [firebird-support] SQL slower after N executions?

I don't know why things slow down, Kjell, if the batch job updates records, I guess it could be related to there being lots of versions of some records and that garbage are only collected after your application ends (it sounds plausible if transactions are still open when you "Stopping the batch

Re: [firebird-support] Bad performance with select first(1) + order by

Just create a descending index, Set tir. 4. feb. 2020 kl. 12:36 skrev Matthias Winkler spmm...@gmail.com [firebird-support] : > > > Hello, > > I am struggling with my DB performance once more: > >SELECT FIRST(1) PRIMKEY >FROM TABLE_X >ORDER BY PRIMKEY DESC > > The statement is slow

Re: [firebird-support] Speedup big table after delete

Have you checked transaction statistics when things are slow? The typical reason for gradual slowdown is that there is one or more transactions that started quite a long time ago and hasn't finished yet. This prevents garbage collection and may make Firebird very slow. It is easily visible by

Re: [firebird-support] Help with splitting a string in Firebird SQL

Simple, replace(reverse(substring(reverse(i.location) from position(' ', reverse(i.location, ' / - ', ' AT ' ) Though splitting on the last space would be a problem with cities having spaces in their names, e.g. New York (USA) or St Albans (England). HTH, Set ons. 9. okt. 2019 kl. 00:14

Re: [firebird-support] CTE Spaces between every day

Maybe you could add something like: union all select distinct cast(m.t || '.' || m.m || '.' || m.j as timestamp), null, null, null, null from tkal_main m where m.serie in ( 3, 6 ) and w.wek_id = :wek_team It won't quite be a blank line, but a line only containing the date. HTH, Set man. 12.

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

I certainly agree with you that if EXISTS returns false, then NOT EXISTS should return true. It could make some sense if both EXISTS and NOT EXISTS returned (though it should be documented somewhere), but not that one returns false and the other . When you write this in the tracker, I think it

Re: [firebird-support] SELECT FIRST N BY GROUP

ROW_NUMBER, as Dimitry suggests, is available from Firebird 3. The following query should work regardless of Firebird version: SELECT s.* FROM SALARIES s LEFT JOIN SALARIES s2 on s.EMPID = s2.EMPID and s.EFFDATE < s2.EFFDATE LEFT JOIN SALARIES s3 on s2.EMPID = s3.EMPID and s2.EFFDATE < s3.EFFDATE

Re: [firebird-support] strange bahaviour

If you were using dialect 1, I would say the difference would be expected (since NUMERIC(18) is equal to DOUBLE PRECISION in dialect 1), but I guess you're using dialect 3? What do you get if you run: SELECT ID, IMPORTO - TRUNC(IMPORTO), PAGATO - TRUNC(PAGATO) FROM ELENCO_SCADENZE Is the

Re: [firebird-support] Help interpret plan

How many records are there in BUPD, are the other indexes in the plan selective and can you show us the query? Just wondering if it can be rewritten so that the query performs better (I assume the reason for you asking about the plan being that you have a query that is too slow). Set ons. 12.

Re: [firebird-support] Re: wrong ordering in WITH RECURSIVE query

It's your query that is wrong, it's the final result that should be ordered, not the subselect, i.e.: with recursive n (ID_PUJC, NAZEV, PORADI, FK_NADR, uroven) as ( SELECT ID_PUJC,NAZEV,PORADI,FK_NADR,0 FROM def_pujc_test WHERE fk_nadr is null UNION ALL SELECT dp.ID_PUJC,

Re: [firebird-support] Link system tables to trigger

O, I see, Olaf, you want to get the Windows user (or similar) and not the Firebird user (since all use the same Firebird user through ODBC). I have no clue whether that is possible to do in a trigger or not, but there's hopefully someone else on this list that knows the answer. Sorry, Set Den

Re: [firebird-support] Link system tables to trigger

Isn't current_user ( https://firebirdsql.org/refdocs/langrefupd21-current_user.html) sufficient? Den tir. 12. mar. 2019 kl. 09:20 skrev 'Check_Mail' check_m...@satron.de [firebird-support] : > > > Hello, > > I would like to log some changes in tables, in germany we must save this > for dsgvo

Re: ODP: [firebird-support] Question about index use

Adding +0 or || '' (depending on type of field_A) would make it more likely that the table_B.field_B index will be used. It wouldn't force the optimizer, just be a strong hint... Select * from table_A a left join table_B b on a.field_A = b.field_A+0 where b.field_B = 1 HTH, Set Den tor. 7. mar.

Re: [firebird-support] Question about index use

Sure, the results of Select * from table_A a left join table_B b on a.field_A = b.field_A where b.field_B = 1; and Select * from table_A a inner join table_B b on a.field_A = b.field_A where b.field_B = 1; are identical. However, in the first case you're telling Firebird to explicitly do a

Re: [firebird-support] Query optimization when using sub query with in operator

WHERE ID IN ( ) is very different from WHERE ID IN ( ). In your simple case the subselect is not correlated and only needs to be executed once, but correlated queries are more complex. What I mean by correlated can be seen in this example: SELECT ... FROM A WHERE A.ID IN ( SELECT B.A_ID FROM B

Re: [firebird-support] Formatting currency

This is due to your regional settings. If I tried 'select cast(1112223.444 as decimal(18,2)) from rdb$database' I would get 1 112 223,44 since I'm doing things the Norwegian way, if I'd lived in England, I'd expect to get 1,112,223.44 and if I'd lived in India I'd probably get 11,12,223.44 (at

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

Sure, just use: execute block returns ( consecutivedays integer ) as ... where drivernr = 697 Den man. 28. jan. 2019 kl. 08:54 skrev 'Autoneer' myauton...@gmail.com [firebird-support] : > > > Thank you Lester, Omacht and Set > > > > Sorry a simple oversight on my side it now works 100%. >

Re: [firebird-support] Speed difference 2.5.3 - 2.5.7

Almost seems like the PI_T_INVOICES is lacking or not active. Either that or that the part of it being used for the query in question has lousy selectivity. It is not unusual for the optimizer to think that two PLANs are almost equally good and then sometimes make a terrible choice, but I'm

Re: [firebird-support] Very slow select Index with null date

Try comparing SELECT count(*) FROM DM251 A WHEREA.DT_INDEX* IS* NULL to SELECT count(*) FROM DM251 A WHEREA.DT_INDEX* IS NOT* NULL And what are the PLANs for these two selects (including name of field(s) if not intuitive)? My guess regarding your initial timing, is that only the first

Re: [firebird-support] Using Union and Join (of two tables residing in different databases) in a Query

dBase supported tables, and databases was not a separate concept. Firebird also supports tables, but as a very different concept from databases. A Firebird database can contain about 32000 tables and joining tables within a database is easypeasy. Although possible (through EXECUTE STATEMENT... ON

Re: [firebird-support] Prevent overlaping dates in concurent environment

Why wouldn't my suggestion work, Tomasz? When Karol inserts his first row into his main table it will contain DATE_FROM 2018-09-10 and DATE_TO 2018-09-20 and the trigger would insert 11 rows (one for each date) into my suggested table. When the next row is inserted with DATE_FROM 2018-09-15 and

Re: [firebird-support] Prevent overlaping dates in concurent environment

What about a separate table for dates with one row per date and a UNIQUE constraint that you populate from a (AFTER INSERT/DELETE) trigger on your real table? HTH, Set Den ons. 12. sep. 2018 kl. 11:49 skrev liviuslivius liviusliv...@poczta.onet.pl [firebird-support] <

Re: [firebird-support] how to resolve this deadlock

Hi Hamish! What exactly do you mean by "The queries kind of don't actually conflict"? If they're trying to MERGE into the same row simultaneously, then they do actually conflict, even if they contain the same values. Strictly speaking, I think you have a 'lock conflict' and not a 'deadlock' (a

[firebird-support] x00 in char(1) field affecting number of rows returned

Admittedly, I’ve only tested on Firebird 2.5.2 and 2.5.4 and the particular column used has ISO8859_1 for both CHARACTER SET and COLLATION. SELECT distinct MyChar1Field FROM MyTable GROUP BY 1 returned three rows with what looked like a space, whereas SELECT distinct MyChar1Field FROM

Re: [firebird-support] Re: Sequential auto incremental numbering

I think the article Lester refers to may be the 'An auditable series of numbers' article which can be found on http://www.ibobjects.com/TechInfo.html. Den man. 3. sep. 2018 kl. 09:58 skrev Lester Caine les...@lsces.co.uk [firebird-support] : > On 03/09/18 07:29, 'Christian Giesen' ch...@xt.co.za

Re: [firebird-support] get number of combinations of to keys

ro, fa) > into :ProFaCount > > > enough? > > > András > > > -- > *Feladó:* firebird-support@yahoogroups.com < > firebird-support@yahoogroups.com>, meghatalmazó: Svein Erling Tysvær > setys...@gmail.com [firebird-support] > *Elkül

Re: [firebird-support] get number of combinations of to keys

Well, you need to know more about PRO and FA than we do, but if they are character fields and neither of them can include _, then maybe: select count( distinct coalesce( PRO, '' ) || '_' || coalesce( FA, '' ) ) ProFaCount from Tab where Key = :Key would work. The COALESCE is there in case the

Re: [firebird-support] Delete issue

Good to see that the issue is solved, but of curiosity: Why not simply delete from tplan_kw_pos a where not exists( select * from tauftr_ge b where a.auftragsjahr = b.jahr and a.auftragsnr = b.nr ) Set 2018-06-15 10:38 GMT+02:00 Omacht András aoma...@mve.hu

Re: [firebird-support] Error creating Foreign key

What are the names of the foreign keys? Just in case Firebird thinks two foreign keys are identical, e.g. the naming of both foreign keys have the same first 27 letters and only differ later (don't know if the limit is 27, I think it was 27 or 28 for tables earlier, have no clue about FKs). Are

Re: [firebird-support] Puzzled by difference in execution time

At first I just tried UPDATE ... SET field1 = 'K', field2 = '-' WHERE EXISTS(... and changed to EXECUTE BLOCK after that. First (for testing) I used ROWS 1 (or ROWS 5) in the FOR SELECT and it was still time consuming, although it finished within a minute or so. I've simply never thought of

Re: [firebird-support] convert a string to a number in a where clause - without errors

I tried something very similar to select * from daten where (case when sn similar to '[0-9]+' then cast (sn as integer) else null end ) > 0 and it worked in Firebird 2.5. Though I might have considered rewriting it select * from daten where cast( iif( sn similar to '[[:DIGIT:]]+', sn, null )

Re: [firebird-support] Problem with where clause

Normally, a query would contain something like: WHERE CF.DATE_COMMANDE BETWEEN '1.1.2018' AND '2.1.2018' What you are doing is forgetting the apostrophes and trying to use: WHERE CF.DATE_COMMANDE BETWEEN 1.1.2018 AND 2.1.2018 which correctly receives a syntax error. I can think of three ways

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

I would suggest replacing your query with something like: with tmp(DaData) as (select cast(:DaData as date) from rdb$database) SELECT DT.DEPOSITO_ID, SUM(iif(DT.DATA < t.DaData, DC.CARICO - DC.SCARICO, 0)) AS RIPORTO, SUM(iif(DT.DATA >= t.DaData, DC.CARICO, 0)) as Carico, SUM(iif(DT.DATA

Re: [firebird-support] Lock Conflict

Are there any INSERT triggers on either of the tables? 2017-12-15 11:44 GMT+01:00 OB1 oldbasf...@googlemail.com [firebird-support] : > > > Thanks for replying. > > Possibly. I'll recheck, but all they're doing is inserting, no selecting, > editing or deletion. >

Re: [firebird-support] Select with calculated field problem

Your actual result indicates that there are other rows in either of the tables (e.g. you could have an extra row in X01 with CODSBB 3 and CANT 1). Verify that these two queries return one row each: SELECT * FROM LSUBANS WHERE CODSUB = 'SB3' and SELECT * FROM X01 WHERE CODSBB = 'SB3' HTH, Set

Re: [firebird-support] Get the value of Query Not Available In Table

This is not possible in a direct query (well, unless you make a table that contains the numbers that you want to check), but with EXECUTE BLOCK you can do something similar: execute block (FromValue integer = :MyFromValue, ToValue integer = :MyToValue) returns (NotInTable integer) as begin

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

Try changing to Afdeling_ID||'' (or Afdeling_ID+0 if it is a number) in your trigger. That should make RDB$FOREIGN105 useless for the query. Though this kind of trigger that finds the MAX value in huge tables (although there's only 750 occurences of each VAREPLU_ID on average, I guess there are

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

Please show us the definition of RDB$FOREIGN105 and VAREFRVSTR_DETAIL_VNR. Set 2017-11-21 12:38 GMT+01:00 michael.vilhelm...@microcom.dk [firebird-support] : > > > Hi all > > Sorry for my late answer and thank you all for your input. We experienced > a large

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

Not answering your question, just curious whether Insert into VareFrvStr_Detail ( VarePlu_ID, Farve_Navn, Laengde_Navn, Stoerrelse_Navn, V509Index, MinBeholdning, Genbestilling, NormalStkAntal, Valuta_Navn, Provision, MomsSats, VejetKostPrisStk,

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

I did notice that SortOrder got truncated if it wasn't cast to a longer field, and quite frankly, I have no clue whether the sorting gets correct if you don't cast it like this or if it was 'a random coincidence' that it worked on the test data. I also tried to use cte rather than MyTable in cte2

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

What about something like: with recursive cte as (select id, id_parent, green, SortText, SortText SortColumn from MyTable where Green = 'Yes' union all select T2.id, T2.id_parent, T2.green, T2.SortText, cte.SortColumn || t2.SortText from MyTable T2 join cte

Re: [firebird-support] Creating a conditional "order by" statement

I even think Tomasz suggestion can be moved to the ORDER BY itself, at least it worked for me when I tried (well, I used iif(cast(:MyParameter as integer, Amount1, Amount2)) select ID, Amount1, Amount2, ... from Table1 union select ID, Amount1, Amount2, ... from Table2 order by iif(your

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

2 > > So it's quite possible I will have lots of duplicates for an individual > row in ROUTES when compared in ROUTE_HISTORY - what I'm trying to filter is > the pattern of rows. Only if ALL the rows of a current entries in ROUTES > exist in ROUTE_HISTORY should it be considered a duplicate con

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

)) select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY from new_routes nr join routes r on nr.NODE = r.NODE Hope I finally got it right, Set 2017-09-28 10:01 GMT+02:00 Svein Erling Tysvær <setys...@gmail.com>: > Sorry, I forgot that should probably use 'IS NOT DISTINCT' and not '=' for

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

2 >> 5557651102 >> >> So it's quite possible I will have lots of duplicates for an individual >> row in ROUTES when compared in ROUTE_HISTORY - what I'm trying to filter is >> the pattern of rows. Only if ALL the rows of a current entries in ROUTES >> exi

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

Sure it is possible to write such a query: select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY from routes r left join route_history rh1 on r.node = rh1.node and r.route_index = rh1.route_index and r.link_node = rh1.link_node

Re: [firebird-support] crosstab pivot query

Hi Olaf! Not quite crosstab, but knowing there is a maximum of 31 days in a month, you can simulate: with tmpPerson(Person) as (select distinct person from tableA where month = 9), tmpDays("day", person, sign) as (select "day", person, sign from tableA where month = 9) select p.person,

Re: [firebird-support] COUNT DISTINCT issue in Fb 2.5

> select a.num_part, a.cve_prov, a.cve_impo, a.des_merc > from ctrac_clasif a > where a.num_part = '0630039046'; > >It yields this results: > >NUM_PART CVE_PROV CVE_IMPO DES_MERC >= = = == >0630039046 3400. 19CUBIERTA >0630039046 3400. 19

Re: [firebird-support] Any 'run as' in SQL/PSQL in FB3 ?

Did you remember to also remove AS? As far as I can see from the syntax description, AS should be used before USER, but not before ROLE, so try: execute statement (lsql_update_cv_itens_sub1) (:lvl_base, :lid_cv_itens_sub1) role 'perfil_vendas'; HTH, Set 2017-08-30

Re: [firebird-support] Evaluate "empty" string

>If (coalesce(terminal, '') <> '') then No need for coalesce since is an unknown state and compared with a value it neither returns true nor false. Hence, I suspect if (trim(terminal) > '') to be equivalent to if (((CHAR_LENGTH(trim(terminal))>0) and (terminal is not null)) Set

Re: [firebird-support] Foreign key different field type

Well, I would hope that create procedure ... declare variable i2 TYPE OF COLUMN test2.id1; declare variable i TYPE OF COLUMN test.id; begin i2 = 12345678; i = 12345678; ... would complain about the assignment to i and not i2. Although I agree with you that it sounds rather useless to allow

Re: [firebird-support] increment

AR" = 2010 (using double quotes since your example contained lots of reserved words), Set 2017-06-15 15:41 GMT+02:00 Svein Erling Tysvær <setys...@gmail.com>: > It is possible to do this with a complex, but straight UPDATE statement. > However, my hunch is that the below is si

Re: [firebird-support] increment

It is possible to do this with a complex, but straight UPDATE statement. However, my hunch is that the below is simpler: execute block as declare variable i integer; declare variable PK ; i = 1; for select from table where year = 2010 order by month, into :PK do

Re: [firebird-support] SELECT with CTE and LIST: Duplicates with DISTINCT and incorrect ordering in the main select

Thanks Michał, I was certain I tried that yesterday, but now I notice that this actually works, so I must have CASTed the wrong place(s). Thanks again, Set

[firebird-support] SELECT with CTE and LIST: Duplicates with DISTINCT and incorrect ordering in the main select

Yesterday I tried to select using a CTE containing LIST(DISTINCT), and then the main (outer) select tried to group by this LIST. The result ended up with incorrect ordering as well as duplicates. Trying to make a reproducible test case, I didn’t get the duplicates, but rather the list contained

Re: [firebird-support] Firebird 2.5 - Alter table - drop default - bug or feature

> > Hi everyone, > > I encountered strange behavior droping a column definition with default > value. > > The environment is Ubuntu 16.04 LTS 64 bits, Firebird version > LI-V2.5.6.27020 (Firebird 2.5 SuperClassic) > > > I'll try to explain with an example. The statement sequence is: > > 1. Create

Re: [firebird-support] join question

Sorry, answered slightly to quickly: Select a.nrworker, b.valueworkermonth from table A left join table B on a.nrworker = b.nrworker and b.year = 2017 and b.month = 4 where a.active = 1 2017-04-11 13:23 GMT+02:00 Svein Erling Tysvær <setys...@gmail.com>: > Simple, just make sure you o

Re: [firebird-support] join question

Simple, just make sure you only refer to table b in the left join and not in the where clause: Select a.nrworker, b.valueworkermonth from table A left join table B on a.nrworker = b.nrworker and a.active = 1 and b.year = 2017 and b.month = 4 HTH, Set 2017-04-11 12:14 GMT+02:00 'Check_Mail'

Re: [firebird-support] FBTrace: What are "dyn requests"

I don't know what dyn requests are, Sean, but reading a bit I do find dyn mentioned in the Firebird book (page 1028) as well as on page 72 of a presentation Pavel did at the Firebird conference 2014. First, the Firebird book: A byte-encoded language for describing data definition statements.

Re: [firebird-support] QUERY QUESTION

WITH TMP(ItemNr, FirstDate, LastDate) AS (SELECT TTFirst.ItemNr, MAX(TTFirst.DateUsed), MAX(TTLast.DateUsed) FROM TEST_TABLE TTFirst JOIN TEST_TABLE TTLast ON TTFirst.ItemNr= TTLast.ItemNr AND TTFirst.DateUsed <= TTLast.DateUsed WHERE NOT EXISTS(SELECT * FROM TEST_TABLE TTNot

Re: [firebird-support] QUERY QUESTION

-03-06 8:04 GMT+01:00 Svein Erling Tysvær <setys...@gmail.com>: > WITH TMP(ItemNr, FirstDate, LastDate) AS > (SELECT TTFirst.ItemNr, MAX(TTFirst.DateUsed), MAX(TTLast.DateUsed) > FROM TEST_TABLE TTFirst > JOIN TEST_TABLE TTLast ON TTFirst.ItemNr= TTLast.ItemN

Re: [firebird-support] Cursor not Executing Properly

I don't use cursors myself, but are you sure "open cur_list_of_contracts;" is enough to set a value different from 0 for row_count? Maybe you need to do the first fetch earlier, e.g. execute block as declare contractno CHAR(20); declare cur_list_of_contracts cursor for (select CU.CONTRACTNO

Re: [firebird-support] Is there an easy way for input rows from a script file?

HTH = Hope this (alternatively: that) helps 2017-02-16 3:06 GMT+01:00 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support] < firebird-support@yahoogroups.com>: > > > Thank you very much Set, I will try your advice. > > Off topic: can you say me what the letters HTH

Re: [firebird-support] Dividing one column by another

If the problem is that it doesn't return any rows (and not that the rows contain null values), then I would expect the reason to be that DOUBLE PRECISION is a floating point and not fixed point datatype. Floating point datatypes never contain exact values, e.g. what you think is 15.3 may be stored

Re: [firebird-support] Basics of Statistics - Mathematics: Mean (Average), Median, Mode and Range ?

Don't know whether Firebird has internal functions for this or not and it is likely that there are more elegant solutions available, but the below queries should be ways to get what you ask (although I don't know whether you want to return all values for mode and whether or not you want the

Re: [firebird-support] Best way to delete millions of rows

Hi Thomas! When driving my car during rush hours, I may get irritated, but I still don't consider it a bug that the traffic - including my car - moves very slowly. Similarly, I do not consider this a bug in Firebird, although I of course would agree that ideally it could be better. From the user

Re: Re: [firebird-support] Optimizer request

d in (select p.partid from partners p where p.country='Spain') > > > I'm using FB 2.5.x > > Right now I don't have big real data for testing. > I'm just wondering because from what I understand from here then for every > row of orders firebird would make that constant subselect

Re: [firebird-support] Optimizer request

Never use IN (subselect). Change to select * from orders where exists( select * from partners where partners.partid = orders.partid and partners.country = ‘Spain’) 2016-09-09 14:30 GMT+02:00 'Djordje Radovanovic' softsis...@sbb.rs [firebird-support] : > > > I

Re: [firebird-support] Re: Uneven results from Round(x, 3) function

Dmitry has (of course) given you the correct answer. What you may overlook, is that DOUBLE PRECISION is a floating point number, and floating point numbers are often approximate and not exact. DOUBLE PRECISION is exact to 15 digits, after that random values may occur. NUMERIC, on the other hand

Re: [firebird-support] Re: Where's my logic flawed?

the particular problem I was trying to solve, so this was more a case of me believing Firebird behaving differently than it does. Set 2016-06-03 11:25 GMT+02:00 Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] <firebird-support@yahoogroups.com>: > 03.06.2016 11:27, Svein E

[firebird-support] Where's my logic flawed?

This query: WITH TMP( TJENESTEENHETRESHID, TJENESTEENHETLOKAL, IMPORT_DATO ) AS ( SELECT TJENESTEENHETRESHID, TJENESTEENHETLOKAL, MAX( IMPORT_DATO ) FROM NPR WHERE TJENESTEENHETRESHID > 0 AND TJENESTEENHETLOKAL > '' GROUP BY 1, 2 ), TMP2(TJENESTEENHETRESHID, TJENESTEENHETLOKAL )

Re: [firebird-support] FB 3.0, Dialect 1 and SQL Error

Have you tried (cast(:PAar as integer) * 100) + cast(:PMDR as integer)) I've never tried it the exact setting as you're using, but I've had to use cast when I've used a CTE to return a constant value (quite natural, there's no simple and general way for Firebird to guess what type your parameter

Re: [firebird-support] how to suppress dashes in query results

Using REPLACE in the WHERE clause means no index. If this is a huge table and you have no other selective WHERE criteria, I would recommend that you rather add another (indexed) field, have a trigger that is ACTIVE BEFORE INSERT OR UPDATE that sets this field using REPLACE, and then simply use

Re: [firebird-support] Foreign Key on update and after update trigger

No, it does not seem difficult, Olaf. Assuming that twarehouse_sum contains some summation of twarehouse, I'd say the reason is as simple as two separate transactions modifying twarehouse simultaneously in a way that makes the change of the other transaction invisible. Generally, I'd say the idea

Re: [firebird-support] How do find duplicates in a table?

If PERSON_ID is unique and you have an index on SOC_SEC_NO, you can find all records with duplicate SOC_SEC_NO this way (ordered by SOC_SEC_NO, so that they are kind of grouped together): SELECT * FROM PERSON P WHERE EXISTS(SELECT * FROM PERSON P2 WHERE P.PERSON_ID <> P2.PERSON_ID

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

No, I must admit limited knowledge of MERGE. For Firebird 3 is seems like a good alternative, but I think Fb 2.1 and 2.5(?) requires a WHEN NOT MATCHED THEN INSERT clause (which is typically not desirable in these cases). Set 2016-01-28 13:31 GMT+01:00 Mark Rotteveel m...@lawinegevaar.nl

Re: [firebird-support] Performance Problem after Migration fb 2.1 cs to fb 2.5 cs both X86

It is probably not relevant to your particular query, but you do have an unusual way to write your joins and I believe (though I'm only 80% certain) your 'nesting' style reduces the options for the optimizer (I think it can only choose between TBESTELLUNGEN and TBESTPOS as the first table, though

[firebird-support] Duplicates with LIST function in Fb 2.5.4?

Hi, I'm puzzled by two queries returning duplicate rows: 1) SELECT LIST('Hi') FROM RDB$DATABASE UNION SELECT LIST('Hi') FROM RDB$DATABASE 2) WITH TMP(DuplicateRows) AS (SELECT LIST('Hi') FROM RDB$DATABASE UNION SELECT LIST('Hi') FROM RDB$DATABASE) SELECT DISTINCT DuplicateRows FROM TMP I was

Re: [firebird-support] Re: Duplicates with LIST function in Fb 2.5.4?

...@users.sourceforge.net [firebird-support] <firebird-support@yahoogroups.com>: > 11.01.2016 11:49, Svein Erling Tysvær wrote: > > > Hi, I'm puzzled by two queries returning duplicate rows: > > > > 1) > > SELECT LIST('Hi') FROM RDB$DATABASE > > UNION > &g

Re: [firebird-support] Numeration without hole, Is right Before Insert Trigger?

Also, take a look at this ancient document that used to be the standard answer to people asking the same question as yours: http://ibobjects.com/docs/ti_AuditableSeries.ZIP HTH, Set 2015-12-22 20:26 GMT+01:00 Ann Harrison aharri...@ibphoenix.com [firebird-support]

Re: [firebird-support] Re: Firebird Indexing problem

Hi, you're wrong. Non-indexed reads are faster than indexed reads. However, indexed reads may reduce the data that needs to be read and with the right conditions, it can speed up things tremendously. Let's say you have a table with breast cancer patients. Using an index for gender would slow

Re: [firebird-support] How to do a running total in SQL

This will become simpler with Firebird 3, which implements windowing functions. Simple cases can also be possible with older versions and some imagination, e.g. with tmp (Reference, SumDue) as (Select Reference, Sum(Due) From Invoices Group by 1) Select Reference, SumDue, (select sum(SumDue)

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

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 idea

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 Table Order by Sort_Column Rows 1 If you

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

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),

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 111000.00

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 (3

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 datasets.

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,

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 USER

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 error when

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) (ID :=

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 enforce it

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

  1   2   3   4   5   >