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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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] <
firebird-support@yahoogroups.com>:

>
>
> You can try also pseudo inner join (left join with where clause)
>
>
>
> Instead of
>
>
>
> T1 INNER JOIN T2 ON T1.ID1=T2.ID2
>
>
>
> change it to
>
>
>
> T1 LEFT JOIN T2 ON T1.ID1=T2.ID2
>
> WHERE
>
> T2. ID2 IS NOT NULL
>
>
>
> regards,
>
> Karol Bieniaszewski
>
>
> 

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 "Företag" F
 inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
 inner join "FöretagOmsättningsår" FtgOmsar on FtgOmsar."Företag" =
F."ECO_ID" +0
 inner join "År" Ar on Ar."ECO_ID" = FtgOmsar."Omsättningsår" +0
 inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" =
FtgOmsar."Uppgiftshanterare"

ought to speed up your query. The point being that your slow plan only is
an option if indices can be used for  FtgOmsar."Omsättningsår" and
F."ECO_ID" and that this kind of addition prevents those indices from being
used.

HTH,
Set

tor. 30. apr. 2020 kl. 17:43 skrev Kjell Rilbe
kjell.ri...@marknadsinformation.se [firebird-support] <
firebird-support@yahoogroups.com>:

> Thanks Karol! I will consider explicit plan if my current rewrite of the
> query doesn't pan out.
>
> The table "TmpFKExportId" will always have the same number of records,
> but a different set (each chunk of the batch will load the same number
> of id:s, but of course different id values). Statistics should be constant.
>
> The other tables will vary slightly over time, but the database contains
> 2+ million companies and related data. During the batch, the only
> updates to this data is from our TM staff who phone companies and enter
> data manually, one company at a time. So overall, the change rate is
> minute. And in particular, there's no big change exactly 318 chunks into
> the batch job, every time.
>
> Yes, the query is hardcoded as a string literal into the app's source
> code. It can't get more "same" than that. :-)
>
> Mvh,
> Kjell
>
> Den 2020-04-30 kl. 17:06, skrev Karol Bieniaszewski
> liviusliv...@poczta.onet.pl [firebird-support]:
> >
> > I suppose you have two different queries – one with where clause and
> > one without on one of the tables involved in the query.
> > Are you sure that the queries are the same?
> >
> > But also it is quite usual that after new data changes the plan is
> > about to change.
> > Is this true for your case that some table got more records after fill
> > then previously second table?
> > Especially this one?:
> >
> > You can try also modify your query to have static plan (some +0) or
> > update index statistics after some count of operations.
> >
> > Regards,
> > Karol Bieniaszewski
> >
> > [Non-text portions of this message have been removed]
> >
> >
>
>
> [Non-text portions of this message have been removed]
>
>
>
> 
> Posted by: Kjell Rilbe 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 and
restarting it, with the app still open". Maybe you should check if there's
a large gap between oldest and next transaction when you stop the batch
(similar to Karols first recommendation)? Another thing that would not harm
is to change from

  where Ar."Årtal" = ( select max( Ar2."Årtal" )
  from "FöretagOmsättningsår" FtgOmsar2
  inner join "År" Ar2 on Ar2.."ECO_ID" =
FtgOmsar2."Omsättningsår"
  where FtgOmsar2."Företag" = F."ECO_ID" )

to
 where not exists ( select 1
from "FöretagOmsättningsår" FtgOmsar2
inner join "År" Ar2 on Ar2.."ECO_ID" =
FtgOmsar2."Omsättningsår"
where FtgOmsar2."Företag" = F."ECO_ID"
  and Ar."Årtal" < Ar2."Årtal" )

in the second part of your query. This will probably not help with the
slowdown, but it should not be slower than your max( and it may be slightly
quicker (although the table names "FöretagOmsättningsår" and "År" does
indicate these are tiny tables).

Set

ons. 29. apr. 2020 kl. 13:44 skrev Kjell Rilbe
kjell.ri...@marknadsinformation.se [firebird-support] <
firebird-support@yahoogroups.com>:

> Hi,
>
> I have a batch job that among other things executes a certain SQL query
> to load some data.
>
> This query takes about 10-12 seconds the first ~320 times, but then
> suddenly takes ~45-50 seconds every time.
>
> Any idea what could be causing this?
>
> The query is rather long, see below. Firebird 3.0.4 on Windows Server
> 2016 64 bit. App is ASP Framework using FirebirdSql.Data.FirebirdClient
> 7.1.1.0.
>
> I can't see any other operations slowing down at the same point in time.
> I can't see any particular change in the app's memory usage (it varies
> up and down both before and after the time of slowdown). I did try a
> change of Firebird's cache settings (more page buffers), but it didn't
> seem to have any effect (yes I did restart FB service to ensure changes
> took effect). Windows file system cache doesn't seem to hit the roof at
> this particular time, so I see no connection there.
>
> Stopping the batch and restarting it, with the app still open, doesn't
> help, but restarting the entire app leads to the shorter execution time
> again.
>
> Each execution of this SQL query is done in a new connection (not
> pooled). Before this SQL, the same connection is also used to empty the
> table "TmpFKExportId" and then fill it with some new data, a simple list
> of 250 bigints).
>
> The app does do other DB work that spans multiple executions of this SQL
> query, using a separate connection. Those operations do not exhibit any
> noticable slowdown.
>
> I'm at a loss... Any suggestions of things to try are most welcome!
>
> SQL:
>
> select distinct Upg."ECO_ID", Upg."ECO_TYPE"
> from (
>  select 'Företag' "Bärartyp", F."ECO_ID" "Bärare", Uhant."ECO_ID"
>  from "Företag" F
>  inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
>  inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" =
> F."Uppgiftshanterare"
>  union
>  select 'FöretagOmsättningsår' "Bärartyp", FtgOmsar."ECO_ID"
> "Bärare", Uhant."ECO_ID"
>  from "Företag" F
>  inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
>  inner join "FöretagOmsättningsår" FtgOmsar on FtgOmsar."Företag" =
> F."ECO_ID"
>  inner join "År" Ar on Ar."ECO_ID" = FtgOmsar."Omsättningsår"
>  inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" =
> FtgOmsar."Uppgiftshanterare"
>  where Ar."Årtal" = (
>  select max(Ar2."Årtal")
>  from "FöretagOmsättningsår" FtgOmsar2
>  inner join "År" Ar2 on Ar2.."ECO_ID" = FtgOmsar2."Omsättningsår"
>  where FtgOmsar2."Företag" = F."ECO_ID"
>)
>  union
>  select 'ITmiljö' "Bärartyp", ITmilj."ECO_ID" "Bärare", Uhant."ECO_ID"
>  from "Företag" F
>  inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
>  inner join "ITmiljö" ITmilj on ITmilj."ECO_ID" = F."ITmiljö"
>  inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" =
> ITmilj."Uppgiftshanterare"
>  union
>  select 'Befattningsinnehav' "Bärartyp", BI."ECO_ID" "Bärare",
> Uhant."ECO_ID"
>  from "Företag" F
>  inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
>  inner join "Befattningsinnehav" BI on BI."Företag" = F."ECO_ID"
>  inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" =
> BI."Uppgiftshanterare"
>  union
>  select 'Person' "Bärartyp", P."ECO_ID" "Bärare", Uhant."ECO_ID"
>  from "Företag" F
>  inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
>  inner join "Befattningsinnehav" BI on BI."Företag" 

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 once more because of the sorting.
> I just want to find the entry with the highest primary key.
> The PRIMKEY is ASC and unique.
>
> Is the another way to achieve the same?
>
> Thanks
>
> Matthias
>
>
>
> 

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
subtracting the value reported for the oldest (active) transaction from the
next transaction. If that gap is too large, you probably have one or more
programs that doesn't commit their transactions frequently enough,
something that can lead to there being many versions of each record being
around and a lot of work for the Firebird engine (in Firebird, each record
can exist in multiple versions although each transaction only sees one
version of the record). Deleting records will not help in such
circumstances, restarting Firebird will quickly see a speed increase, but
often followed by a new slowdown. The real way to fix this problem is to
rewrite the programs accessing Firebird so that they handle transactions
properly.

If you delete lots of records, some cleanup has to be done. Typically, this
is done the next time something accesses the table. You can prevent users
from seeing this kind of slowdown by e.g. doing a SELECT COUNT(*) from the
table after the deletions (but deletions will not fix situations like the
one mentioned in the first paragraph).

Another thing that sometimes give the perception of a slow database are
queries written in ways that Firebird dislikes (e.g. SELECT ... FROM ...
WHERE  NOT IN ) or indices being not appropriate for a
particular query.

1.000.000 records is not too much, there are Firebird databases containing
many times as many records that have no speed issues. The biggest tables I
normally work with probably has 20 or 30 million records and some would
even consider that a fairly small Firebird table.

HTH,
Set

tor. 30. jan. 2020 kl. 12:48 skrev Matthias Winkler spmm...@gmail.com
[firebird-support] :

>
>
> Hello,
>
> I have a table with 1´000´000 entries.
> As a cleanup I now delete all except for the most recent ones.
>
> Do I need to do anything else to improve performance fo the table?
>
> Rebuilding indices is not possible if the primary key is a autogenernarted
> int, right?
>
> Any other suggestions?
>
> BR
>
> MAtthias
>
>
> 

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 skrev Ron Pillar ron.pil...@in-synch.com
[firebird-support] :

>
>
> Hello All,
>
>
>
> I could use some help/direction with splitting (or extracting data between
> certain characters in) an existing string.  This is generally a simple task
> in code using IndexOf, LastIndexOf, etc. but I can’t seem to figure out the
> syntax in Firebird SQL (and it has to be done in SQL unfortunately).  The
> string is stored in one field called “Location” and the results are being
> split into two columns (which are basically “ADDRESS” and “CITY”).
>
>
>
> Here is an example of the data:  202 CLARENCE RAY DR CITYNAME
>
>
>
> So far I’ve written this SQL (might be a better way to do this):
>
> *reverse(substring(reverse(i.location) from position(' ',
> reverse(i.location*
>
>
>
> Which returns*:  *202 CLARENCE RAY DR
>
>
>
> And this SQL:
>
> *right(i.incident_location, position(' ', reverse(i.incident_location)))*
>
>
>
> Which returns*:  *CITYNAME
>
>
>
> I thought that I had solved the problem…  Upon looking through the data
> for the “Location” field, I noticed that there appears to be intersections
> as well address information.  The intersections format is where I’m
> encountering the most trouble.  Here is an example of the intersection
> format:
>
>
>
> *- OLD HWY 11 / - OAK GROVE RD CITYNAME*
>
>
>
> I need to convert this data into a format like:   *OLD HWY 11 AT OAK
> GROVE RD *in one column and* CITYNAME *in a second column.  My 
> *right(i.incident_location,
> position(' ', reverse(i.incident_location))) *should be sufficient for*
> the CITYNAME, *but I have no idea how to write the syntax to convert the
> first column.
>
>
>
> Any help would be greatly appreciated!
>
>
>
>
> 

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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. aug. 2019 kl. 14:23 skrev 'Check_Mail' check_m...@satron.de
[firebird-support] :

>
>
> Hello Karol,
>
>
>
> I had the same idea, but it is a little bit complex. First, I must save
> the last date, if the new one ist different, I must save all return
> variables, set it to null, suspend the record and restore the saved return
> values and suspend this. So I have the old Date, the blank record and the
> new one. I thought, there is a simplier way.
>
>
>
> Thank you.
>
>
>
> *Von:* firebird-support@yahoogroups.com 
>
> *Gesendet:* Montag, 12. August 2019 12:17
> *An:* firebird-support@yahoogroups.com; 'Check_Mail' check_m...@satron.de
> [firebird-support] 
> *Betreff:* Re: [firebird-support] CTE Spaces between every day
>
>
>
>
>
> Hi,
>
>
>
> simply add loop inside
>
> do
>
>   begin
>
>  suspend;
>
> --put here some loop
>
> while something do
>
>   begin
>
>   --modify output variables here
>
>   suspend;
>
>   end
>
>   end;
>
>
>
> regards,
>
> Karol Bieniaszewski
>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 would be good if you included a very simple reproducible test
case.

Set

man. 12. aug. 2019 kl. 10:01 skrev liviuslivius liviusliv...@poczta.onet.pl
[firebird-support] :

>
>
>
> >>Thank you Set for the help. I must do one more test on monday. I must
> modify Case to comparision
> >>CASE WHEN EXISTS() IS NULL THEN -1 ...
> >>And we will see if exists can return something else then TRUE or FALSE
>
> i have tested it now and i suppose result is wrong
>
> 
>
> SELECT
> EXISTS() return False
> FROM
> WHERE
> ...
>
> 
>
> but
> SELECT
> NOT EXISTS() return NULL!!! instead of True
> FROM
> WHERE
>
> 
>
> i suppose it is an error and should be reported to the tracker?
>
>
> Regards,
> Karol Bieniaszewski
>
>
> 

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
WHERE s3.EMPID IS NULL

If there are any rows without EMPID or EFFDATE, they are ignored by this
query. Note that the query doesn't return the latest two rows, but all rows
for the latest two EFFDATEs for the employee. If you have such duplicates
and want the last two rows only, then you could e.g. do:

SELECT s.*
FROM SALARIES s
LEFT JOIN SALARIES s2 on s.EMPID = s2.EMPID and ( s.EFFDATE < s2.EFFDATE or
( s.EFFDATE = s2.EFFDATE and s.ID < s2.ID ) )
LEFT JOIN SALARIES s3 on s2.EMPID = s3.EMPID and ( s2.EFFDATE < s3.EFFDATE
or ( s2.EFFDATE = s3.EFFDATE and s2.ID < s3.ID ) )
WHERE s3.EMPID IS NULL

HTH,
Set

ons. 7. aug. 2019 kl. 15:53 skrev Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support] :

> 07.08.2019 14:26, pg...@mccolls.co.uk [firebird-support] wrote:
> > Is this possible in a single SELECT query
>
>I would try WHERE ROW_NUMBER() OVER (PARTITION BY empid, ORDER BY
> effdate) <= 2.
>
>
> --
>WBR, SD.
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

Re: [firebird-support] strange bahaviour

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 problem the same after

UPDATE
  ELENCO_SCADENZE
SET
  IMPORTO = TRUNC(IMPORTO, 2)

HTH,
Set

ons. 3. jul. 2019 kl. 17:27 skrev Luigi Siciliano luigi...@tiscalinet.it
[firebird-support] :

>
>
> Hallo,
> Il 03/07/2019 16:35, liviuslivius liviusliv...@poczta.onet.pl
> [firebird-support] ha scritto:
>
>
> What are the exact types of both fields?
>
> NUMERIC(18,4) via domain:
>
> CREATE DOMAIN CURRENCY_D
>  AS Numeric(18,4)
>  DEFAULT 0.00
>  NOT NULL
> ;
> Thanks.
> --
>
> Luigi Siciliano
> --
>
>
>
> 

Re: [firebird-support] Help interpret plan

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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. jun. 2019 kl. 12:38 skrev Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support] :

> 12.06.2019 12:37, Kjell Rilbe kjell.ri...@marknadsinformation.se
> [firebird-support] wrote:
> > Would this mean that the table F would be subject to a full natural scan
> > for each record of the preceding joins?
>
>Yes.
>
>
> --
>WBR, SD.
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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, dp.NAZEV, dp.PORADI, dp.FK_NADR, n.uroven + 1 FROM
def_pujc_test dp, n WHERE dp.fk_nadr = n.id_pujc
)
SELECT * FROM n
order by poradi

Set

tir. 16. apr. 2019 kl. 02:42 skrev respekt...@post.cz [firebird-support] <
firebird-support@yahoogroups.com>:

>
>
> Hi all,
>
> it is ordered according to order of inserts
>
> I have tried to delete one row and reinsert it - and the row is now last
> :-(
>
> So is there some logical error in my query?
> Or is it the error in fb engine?
>
> Thank you a lot.
> Tom
>
>
> 

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 tir. 12. mar. 2019 kl. 10:48 skrev 'Check_Mail' check_m...@satron.de
[firebird-support] :

>
>
> Hello Set,
>
>
>
> all users connecting over odbc with the same user, so I cannot separate
> the user, which changed the record. It is possible to get it over the
> transactions-table?
>
>
>
> *Von:* firebird-support@yahoogroups.com 
>
> *Gesendet:* Dienstag, 12. März 2019 09:52
> *An:* firebird-support@yahoogroups.com
> *Betreff:* 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 (customer data).
>
>
>
> Now, I can do this with trigger, but can I link this changing with the
> system tables, that I could get the user? The mon$attachment shows me all
> active attachments with the user and the ip of the client. Can I link this
> with the changing?
>
>
>
> Thank you.
>
>
>
> Best regards
>
>
>
> Olaf
>
>
>
>
>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 (customer data).
>
>
>
> Now, I can do this with trigger, but can I link this changing with the
> system tables, that I could get the user? The mon$attachment shows me all
> active attachments with the user and the ip of the client. Can I link this
> with the changing?
>
>
>
> Thank you.
>
>
>
> Best regards
>
>
>
> Olaf
>
>
>
>
> 

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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. 2019 kl. 12:32 skrev Carsten Schäfer ca_schae...@gmx.de
[firebird-support] :

>
>
> Yes I'm sure.
>
> With the left join FB does a full scan on Table_A and uses the Foreign Key
> index on Table_B. The index on table_B.field_B is not used at all.
>
> So you get millions of reads if the table contains millions of data
>
> With the inner join the index on  table_B.field_B is used and you only
> get 1 read (if It's only 1 row that matches the condition)
>
> Is it possible to force the optimizer tu use the index on table_B.field_B?
>
>
> Regards
> Carsten
>
>
> Am 06.03.2019 um 21:49 schrieb Karol Bieniaszewski
> liviusliv...@poczta.onet.pl [firebird-support]:
>
>
>
> Are you sure that this query looks like this?
>
>
>
> For me such query use indexes in both cases left and inner.
>
> Show full example with plans
>
>
>
> Regards,
>
> Karol Bieniaszewski
>
>
>
> 

Re: [firebird-support] Question about index use

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 LEFT JOIN, something which makes Firebird choose to have
table_A before table_B in the plan (it does expect LEFT to be a deliberate
choice you've made and not that you originally wrote left and then changed
your mind in the WHERE clause. Using LEFT rather than INNER join is a
possible way (although unusual) to optimize slow queries). Basically, with
left join, the three plans available to Firebird (besides NATURAL, NATURAL)
are (forgive me for not getting the surrounding PLAN JOIN, PLAN ORDER,
INDEX, parenthesis and commas correct) are:

(table_A NATURAL, table_B (table_B_field_B_index, table_B_field_A_index)),
(table_A NATURAL, table_B (table_B_field_B_index)) or (table_A NATURAL,
table_B (table_B_field_A_index))

(notice that it has to go natural on table_A in all three cases since
there's no fixed value available) whereas the inner join also makes it
possible for the optimizer to choose which table to put first in the plan,
something which also makes it possible to choose

(table_B (table_B_field_B_index), table_A(table_A_field_A_index))

Normally (not always), this latter index would be the one to prefer.

HTH,
Set

Den ons. 6. mar. 2019 kl. 19:41 skrev Carsten Schäfer ca_schae...@gmx.de
[firebird-support] :

>
>
> Hi,
>
> I'm using FB 3.0.4 on Windows and i'm asking why the index (on field_B)is
> not used in the follwing query?
>
> Select * from table_A a left join table_B b on a.field_A = b.field_A where
> b.field_B = 1;
>
> field_A is a standard foreign key and field_B is an indexed field but the
> index is not used and so the query is slow (for big tabels).
>
> When i use an inner join the index on field_B is used, but i don't get why
> FB has to make a difference in this case.
>
> In this case the result set should always be the same, regardless of inner
> join or left join, or not?
> Regards
> Carsten
>
> 

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 WHERE B.AField = A.AField )

In the above query, the subselect has to be executed once for every
possible row in A and hence, no index can be useful for A. Sure, it would
be good if the optimizer to a greater extent had understood the difference
between correlated and not correlated subqueries, but it doesn't.

Normally, it is easy to fix IN (  ) queries by changing to
constants or EXISTS (sometimes even a simple JOIN), though I do admit that
I sometimes find it a bit annoying when I find the following query to be
time consuming (when A contains many millions of rows and B only a handful)

UPDATE A SET A.Field = ( SELECT B.Field FROM B WHERE A.ID = B.A_ID )
WHERE EXISTS( SELECT * FROM B WHERE A.ID = B.A_ID )

and have to rewrite it to

EXECUTE BLOCK AS
DECLARE VARIABLE ID Integer;
DECLARE VARIABLE MyField Varchar( 200 );
BEGIN
  FOR SELECT A_ID, FIELD
  FROM B
  INTO :ID, :MyField DO
  BEGIN
UPDATE A
SET FIELD = :MyField
WHERE ID = :ID;
  END
END

Note that I'm still on Firebird 2.5 and don't know if 3.0 is better with
such queries or not.

HTH,
Set

Den fre. 1. mar. 2019 kl. 01:11 skrev Ivan C Cruz i...@supplymidia.com.br
[firebird-support] :

> The following problem applies to Firebird 3.0. Cant test right now on
> older versions.
>
> Suppose we have a table called AnyTable with column ID as primary key.
>
> The query
>
> select * from AnyTable where ID = '123'
>
> produce the following plan...
>
> PLAN (AnyTable INDEX (PK_AnyTable))
>
> Now a second query, semantically identical to the first...
>
> select * from AnyTable where ID in ( '123' )
>
> will produce the same plan:
>
> PLAN (AnyTable INDEX (PK_AnyTable))
>
> However that third query, also semantically identical to the first...
>
> select * from AnyTable where ID in ( select '123' from RDB$DATABASE )
>
> will produce a really bad plan:
>
> PLAN (RDB$DATABASE NATURAL)
> PLAN (AnyTable NATURAL)
>
> If I try to force the primary key index, an error occurs:
>
> SQL Message : -284
> index  cannot be used in the specified plan
>
> Are there any way to force index usage on a situation like that?
>
> Ivan Cruz.
>
>
> 
> Posted by: Ivan C Cruz 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

Re: [firebird-support] Formatting currency

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
least that what it looks like when I try to choose English (India) as the
region format on my computer). I assume you live in Germany, I don't think
you have to go any further than to Austria to find that there's a space and
no dot for every three digits and in Switzerland I expect you would have
gotten 1'112'223.44 (that surprised me).

Generally, store numbers as numbers and text as varchar and then think
about the presentation when you are displaying things, not when storing
them.

HTH,
Set

Den ons. 20. feb. 2019 kl. 12:02 skrev Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support] :

> 20.02.2019 11:57, 'Check_Mail' check_m...@satron.de [firebird-support]
> wrote:
> > if I select this and store it in a varchar-variable, I get 1112223.44,
> with the point, not
> > comma.
>
>Storing numbers in varchar fields is a big mistake.
>
>
> --
>WBR, SD.
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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%.
>
>
>
> Can this be run without having the end user input the variable i.e.
> :drivernr?
>
>
>
> I want to programmatically provide the driver nr as I only need the result
> to populate a field in another table ?
>
>
>
> Regards
>
>
>
> Stef
>
>
>
> *From:* firebird-support@yahoogroups.com [mailto:
> firebird-support@yahoogroups.com]
> *Sent:* 27 January 2019 11:50
> *To:* firebird-support@yahoogroups.com
> *Subject:* Re: [firebird-support] Consecutive values from different fields
>
>
>
>
>
> On 27/01/2019 08:03, 'Autoneer' myauton...@gmail.com [firebird-support]
> wrote:
> > Thank you much appreciated, I would agree with Lester’s approach however
> > this an inherited DB.
>
> You need a ';' after the wd2 ... I think
>
> Before we have CTE functions it was common to create temporary tables to
> reformat the data and create triggers to copy data from the 'legacy'
> tables to ones that make reporting easier. I'm still using some of the
> same legacy stuff 20 years on ;) Creating the SQL for a CTE query is
> often easier if one 'designs' the table one actually needs and then work
> out how to build it much like the execute block is doing ...
>
> --
> Lester Caine - G8HFL
> -
> Contact - https://lsces.co.uk/wiki/?page=contact
> L.S.Caine Electronic Services - https://lsces.co.uk
> EnquirySolve - https://enquirysolve.com/
> Model Engineers Digital Workshop - https://medw.co.uk
> Rainbow Digital Media - https://rainbowdigitalmedia.co.uk
>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
baffled by your good and bad plan being very similar excepting that one of
them uses a query and the other doesn't. Is the query itself a secret or
can you share it here?

Set

Den man. 12. nov. 2018 kl. 14:45 skrev André Knappstein
knappst...@beta-eigenheim.de [firebird-support] <
firebird-support@yahoogroups.com>:

> Livius,
> thank you.
>
> And:  cool!  You obviously have hit *something*.
> I learned before on different groups and seminars, that when comparing
> different  execution times you better start by looking at the plans...
> only... I usually don't :-)
>
> So,  the  question needs to be changed from:
> "Was there such a change between subreleases"
>
> to:
> "what have I done or omitted - unknowingly - to get different plans?"
>
> I  don't  manipulate  query  plans, in fact I never touch them, or any
> settings   related   to   them  because  in my case surely the risk of
> breaking more than I would be fixing is substantial :-)
>
> But  now  when  I look at them, they are different on the fast vs. the
> slow machines!!!
>
> Here is what I get for the query:
>
> fast execution:
> PLAN  JOIN  (JOIN (I INDEX (PI_T_INVOICES), R2 INDEX (I_T_RESULTS)), K
> INDEX (I_T_KEYS)
> PLAN (R1 NATURAL)
>
> slow execution:
> PLAN  MERGE  (SORT  (JOIN  (I  NATURAL, K INDEX (I_T_KEYS))), SORT (R2
> INDEX (I_T_RESULTS)))
> PLAN (R1 NATURAL)
>
> Query   and   database   are  identical,  both  created  from the same
> backup.
>
> But  I  have an idea which I will check later this afternoon. It's all
> probably just something on 2 or 3 of my systems.
>
> I'll report back!
>
> thanks,
> André
>
> >
>
>
> > Probably difference in query plans.
> > Show query plan for both server versions
>
>
>
>
> > Regards,Karol Bieniaszewski
> >  Oryginalna wiadomość 
> > Od: "André Knappstein knappst...@beta-eigenheim.de
> > [firebird-support]" 
> > Data: 12.11.2018  12:01  (GMT+01:00)
> > Do: firebird-support@yahoogroups.com
> > Temat: [firebird-support] Speed difference 2.5.3 - 2.5.7
>
> >   Hello all,
> >
> > for  a  local  conference,  Iampreparing   a   session   for a
> > Firebird  beginners'  group,  to  demonstrate  the  basics  of  Stored
> > Procedures and their usage from different clients.
> >
> > One scenario is about transforming a *slightly* complex sql query into
> > a selectable stored proc which will yield the same result.
> >
> > I stumbled over something strange:
> > Query  and  SP  are  running with similar same speed on 2.5.7, which I
> > have   in  production  systems. But query is *MUCH* slower than stored
> > proc  on  different(!)  2.5.3  systems  (more  rarely used machines, I
> > failed to upgrade so far).
> >
> > 2.5.7. systems: ~ 3 seconds for Query and SP
> > 2.5.3. systems: ~ 3 seconds for SP, 15 minutes(!) for Query
> > always  tested  from  a  fresh restored database with starting results
> > normalized to 0,00.
> >
> > It  does  not look like a difference in the machines' hardware or even
> > the used antivirus or OS can cause this big a difference.
> >
> > Just  out   of   curiosity  - because all production systems should be
> > updated  to  at  least  2.5.7  anyway:   has   there   been   such  an
> > improvement between sub releases?
> >
> > I checked the change logs:
> >
> https://www.firebirdsql.org/file/documentation/release_notes/Firebird-2.5...8-ReleaseNotes.pdf
> > and  checked  all  between  2.5.3  and  2.5.8,  but found nothing that
> > matches this experience.
> >
> > Query  is  pretty  much straigthforward; one nested subquery to update
> > outer stream, no UDF or other built-in functions
> >
> > best regards,
> > André
> >
> >
>
>
> >
>
>
>
> 
> Posted by: =?utf-8?Q?Andr=C3=A9_Knappstein?=  >
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 few rows are returned in the fast query (and possibly that there's no
active index for the DT_INDEX field).

Set

Den tir. 9. okt. 2018 kl. 16:46 skrev Hugo Eyng hugoe...@msn.com
[firebird-support] :

>
>
> Hello.
>
> I created an index on a table where de column is datetime type. The
> problem seems to be related to the NULL value.
>
> This SQL is very *SLOW* (about 7 seconds for a table containing 360.000
> records). Returns around 400 records
>
> SELECT
> A.cd_xxx, A.dt_1, A.dt_2, A.dt_3, A.dt_4, A.dt_5, A.dt_index​
> FROM DM251 A​
> WHEREA.DT_INDEX* IS* NULL
>
> This SQL is very *FAST* (less than 0.5 seconds for a table containing
> 360.000 records). Returns around 359.600 records
>
> SELECT
> A.cd_xxx, A.dt_1, A.dt_2, A.dt_3, A.dt_4, A.dt_5, A.dt_index​
> FROM DM251 A​
> WHEREA.DT_INDEX* IS NOT* NULL
>
>
>
> Atenciosamente,
>
> + + Hugo Eyng + +
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
EXTERNAL DATA SOURCE...), it does make things a lot more difficult if you
have to do things across databases.

If you've been asked to transfer 967 tables to Firebird, the normal and
simple solution would be to have one database containing all 967 tables,
not a lot of different databases that should be joined to each other. If
you can separate things that doesn't talk to each other, then you can split
into several databases, but probably a few databases, not 967. If there are
a few tables that are common lookup tables, you could consider having one
master database with these and then copy/replicate the tables to other
databases (I would assume zipcodes and municipalities to be rather static,
so these are good candidates for having a master in a separate database,
but then copy/replicate these tables to all databases that uses them).

You may of course have good reasons for importing these .dbx tables into
databases of their own (e.g. if there are some kind of strange legal
requirements), but I'd say that is a decision that you should only take
after carefully considering alternatives.

HTH,
Set

Den ons. 12. sep. 2018 kl. 22:05 skrev 'Stellarancia.com'
ni...@stellarancia.com [firebird-support] :

> I'm transferring many tables (967) from single BDE .DBF files (old
> driver DbIV) to Firebird 2.5.
>
> I utilize Delphi 7 as environment and ZEOS components to Firebird
> connections: the work is enough easy.
>
> A large number of old queries contain UNIONS and JOINS of tables (->
> single files .DBF), but now the old .DBF tables are transferred in
> different Firebird Databases. For example, with the two tables
> Municipalities.Dbf and ZipCodes.Dbf, I can write with BDE a query
> "SELECT * FROM MUNICIPALITIES.DBF a INNER JOIN ZIPCODES.DBF b ON ()...
> WHERE..."; now the first table is in WorkCom.fdb database, and the
> second table is in the TabCom.fbd database: how to join to day the  tho
> tables? My program utilizes this queries and I would want  to find
> solutions that give a fast result as BDE.
>
> I know that is not possible for Firebird to join tables that "resides"
> in different databases (.fdb), but I must find a solution as elegant as
> possible to join the same FB tables previously joined with .DBF files.
>
> Do you have any suggestion as to how to solve this problem?
>
> Thanks in advance.
>
>
> ---
> Questa e-mail è stata controllata per individuare virus con Avast
> antivirus.
> https://www.avast.com/antivirus
>
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 DATE_TO 2018-09-22, the trigger would fail inserting 8 rows due to the
unique constraint.

The one place where I know my suggestion was incorrect, is that the trigger
needs to be AFTER UPDATE as well (I only said AFTER INSERT/DELETE), with a
change of dates, both DELETE and INSERT must be done. Other than that I
would expect it to work (although it does make things a bit slower than not
having this additional table).

Set

Den ons. 12. sep. 2018 kl. 14:49 skrev Tomasz Tyrakowski
t.tyrakow...@sol-system.pl [firebird-support] <
firebird-support@yahoogroups.com>:

> On 12.09.2018 at 13:29, Omacht András aoma...@mve.hu [firebird-support]
> wrote:
> > Create a (reserved_dates) table with date field, and make that field
> unique.. When a user inserts a date into the reservation table a trigger
> immadiate inserts this date to the reserved_dates table too. Then the
> unique key will stop secound insert instead of the first transaction is not
> commited.
>
> This won't work. All dates may be different and the intervals may still
> overlap.
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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] <
firebird-support@yahoogroups.com>:

>
>
> Hi,
>
> is there a good way to prevent overlaping dates to be inserted to the
> table?
>
> You know DATE_FROM, DATE_TO and you can have
> 2018-09-10 to 2018-09-20
> and
> 2018-09-15 to 2018-09-22
> they ovelap on 15,16,17,18,19 and 20
>
>
> No my real sample but simple to understand is:
>
> customer and meeting planning.
> Currently we lock customer record and only one user can do
> update/insert/delete operation at a time.
> But what if we need multiple users insert/update data in the same time?
> How to prevent inserting overlaping dates then?
>
> I know that we can write trigger and check overlap there but what if two
> users do insert and still not commited transaction?
> User1 do insert, trigger is fired and check that no overlaping dates
> exists - and this transaction is not commited yet
> User2 do insert, trigger is fired and check that no overlaping dates
> exists because user 1 still not commit transaction and user2 transaction do
> not see recrods from user1.
> And at this point, if user1 commit and user2 commit, then overlaping dates
> can be inserted into database.
>
> regards,
> Karol Bieniaszewski
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 proper deadlock
would be more if transaction A has updated record a and waits to update
record b, when transaction B has updated record b and waits to update
record a).

One way to solve lock conflicts can sometimes be to insert into a separate
table (never update) and then have a separate batch process that goes
through this table and updates the real tables monthly, daily or every
hour. But I've no idea whether this is of any help in your situation.

Set

Den man. 10. sep. 2018 kl. 16:33 skrev Hugo Eyng hugoe...@msn.com
[firebird-support] :

>
>
> Hi Hamish.
>
> I am not sure if I understood clearly your problem. If I did, you probably
> should use "WITH LOCK" clause.
>
>
> Atenciosamente,
>
> + + Hugo Eyng + +
> --
> *De:* firebird-support@yahoogroups.com 
> em nome de 'Hamish Moffatt' ham...@risingsoftware.com [firebird-support] <
> firebird-support@yahoogroups.com>
> *Enviado:* segunda-feira, 10 de setembro de 2018 08:46
> *Para:* firebird-support@yahoogroups.com
> *Assunto:* [firebird-support] how to resolve this deadlock
>
>
>
> I have a bunch of users logging in to my system at the same time, which
> causes a simple query like this to run:
>
> UPDATE OR INSERT INTO G_CLASS (CLASS_NUM, CLASS_NAME, USER_NUM,
> SYLLABUS_NUM)
> VALUES (1, 'Foo', 1001, 43)
>
> The field values are identical for each - it's exactly the same query.
> There's multiple threads running.
>
> I'm getting a deadlock. The transaction mode is read committed with
> wait; when one transaction finishes the next one unblocks, but fails
> with a deadlock error.
>
>
> The real query during login is actually a MERGE INTO which achieves the
> same thing except it doesn't update all the fields. This might be
> another level of complexity again.
>
>
> Where do I start on resolving this? The queries kind of don't actually
> conflict... Is there a different isolation mode that makes it work, or
> do I need a different approach, or to retry?
>
>
> Hamish
>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 MyTable



only returned one such row. A bit closer look at the data revealed that one
of the entries actually contained hexadecimal 00 and not space. Changing
this one value corrected the error, but I’m still puzzled about this
difference. Moreover, similar queries behave similarly different:



SELECT distinct MyChar1Field || ‘’

FROM MyTable

GROUP BY 1

returns one row



SELECT MyChar1Field || ‘’

FROM MyTable

GROUP BY 1

returns three rows



SELECT MyChar1Field || ‘ ’

FROM MyTable

GROUP BY 1

returns three rows



SELECT MyChar1Field || ‘ ?’

FROM MyTable

GROUP BY 1

returns one row



It is understandable that x00 is treated differently, but it doesn't make
sense to me that it makes other records randomly get into one out of two
groups, nor do I understand why DISTINCT/GROUP BY should behave differently..


Is this a known issue, or even fixed in newer Firebird versions? I didn’t
see anything in the bug tracker, but I didn’t do any thorough search.

Set

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 [firebird-support]
> wrote:
> >NextInvNo = (SELECT MAX(INVOICE_NO) FROM INVOICE) + 1;
>
> Christian ... there was many years ago a very nice article on this very
> problem. The whole reason that will not work is when two or more people
> are adding invoices at the same time! My own system generates ticket
> numbers which ideally should not have gaps but if two counters press
> 'add ticket' at the same time they both see the same 'NextInvNo' ...
> I've added a couple of solutions which almost get it right, but just
> occasionally ... like once every few months ... we get two tickets with
> the same number created within 0.0001 second of one another -  what are
> the chances of that - obviously not zero :(
>
> Now a unique index should prevent that but in my case the ticket numbers
> start at 1 every day across multiple sites so the index has to combine
> several things and I never did work out who to handle the clash event.
> In your case you can make the index unique, but need to handle if two
> invoices are created at the same time ...
>
> --
> Lester Caine - G8HFL
> -
> Contact - https://lsces.co.uk/wiki/?page=contact
> L.S.Caine Electronic Services - https://lsces.co.uk
> EnquirySolve - https://enquirysolve.com/
> Model Engineers Digital Workshop - https://medw.co.uk
> Rainbow Digital Media - https://rainbowdigitalmedia.co.uk
>
>
> 
> Posted by: Lester Caine 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

Sure, I'd expect that to work as well (well, DISTINCT has to be added to
the inner select). However, I do think my suggestion normally would be
simpler if you know the data - you delete the parts that aren't needed and
end up with e.g.

select count( distinct PRO || '_' || FA ) ProFaCount
from Tab
where Key = :Key

Den man. 16. jul. 2018 kl. 09:27 skrev Omacht András aoma...@mve.hu
[firebird-support] :

>
>
> Hi Set,
>
>
> isn't
>
>
> select count(1)
>   from (select pro, fa
>   from tab
>   where Key = :Key
>   group by pro, 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üldve:* 2018. július 16. 9:11
> *Címzett:* firebird-support@yahoogroups.com
> *Tárgy:* 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 fields could be NULL. If PRO
> and FA are non-null numbers and FA always is between 0 and 99, then the
> query would be
>
> select count( distinct ( 100 * PRO ) + FA ) ProFaCount
> from Tab
> where Key = :Key
>
> HTH,
> Set
>
> Den man. 16. jul. 2018 kl. 08:58 skrev josef.gschwendt...@quattro-soft.de
> [firebird-support] :
>
>>
>>
>> Hi,
>>
>>
>> Is there a way to get this ProFaCount in one statement (without for
>> select)
>>
>>
>> ProFaCount = 0;
>> for
>>   select distinct PRO, FA
>> from Tab
>>where Key = :Key
>> into :PRO, :FA
>> do
>>   ProFaCount = ProFaCount + 1;
>>
>> Thank you for your help.
>>
>>
>> Regards,
>>
>> Josef
>>
>>
>>
>
> __ Information from ESET Mail Security, version of virus signature
> database 17721 (20180716) __
>
> The message was checked by ESET Mail Security.
> http://www.eset.com
>
>
> __ Information from ESET Mail Security, version of virus signature
> database 17721 (20180716) __
>
> The message was checked by ESET Mail Security.
> http://www.eset.com
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 fields could be NULL. If PRO
and FA are non-null numbers and FA always is between 0 and 99, then the
query would be

select count( distinct ( 100 * PRO ) + FA ) ProFaCount
from Tab
where Key = :Key

HTH,
Set

Den man. 16. jul. 2018 kl. 08:58 skrev josef.gschwendt...@quattro-soft.de
[firebird-support] :

>
>
> Hi,
>
>
> Is there a way to get this ProFaCount in one statement (without for
> select)
>
>
> ProFaCount = 0;
> for
>   select distinct PRO, FA
> from Tab
>where Key = :Key
> into :PRO, :FA
> do
>   ProFaCount = ProFaCount + 1;
>
> Thank you for your help.
>
>
> Regards,
>
> Josef
>
>
> 
>

Re: [firebird-support] Delete issue

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 [firebird-support] <
firebird-support@yahoogroups.com>:

> Hi Anonymous!
>
> I think you sould check and run if correct this statement:
>
> delete from tplan_kw_pos x
>   where not exists (select 1
>   from tplan_kw_pos a
> inner join tauftr_ge b on a.auftragsjahr = b.jahr
> and a.auftragsnr = b.nr
>   where a.id = x.id)
>
> András
>
> From: firebird-support@yahoogroups.com [mailto:firebird-support@
> yahoogroups..com]
> Sent: Friday, June 15, 2018 10:19 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Delete issue
>
>
> Hello,
>
> I would like to delete all records, where no order number already exists
> in the table tauftr_ge. Here an example of the statement:
>
> delete from tplan_kw_pos x where x.id =  (select a.id from tplan_kw_pos a
> left join tauftr_ge b on a.auftragsjahr = b.jahr and a.auftragsnr = b..nr
> where b.nr is null and a.id = x.id)
>
>
> I get an error, The cursor identified in the UPDATE or DELETE statement is
> not positioned on a row.
> no current record for fetch operation.
>
> It looks simple, delete some records, but what can be wrong?
>
> Thank you.
>
>
>
>
>
> __ Information from ESET Mail Security, version of virus signature
> database 17555 (20180615) __
>
> The message was checked by ESET Mail Security.
> http://www.eset.com
>
>
>
> __ Information from ESET Mail Security, version of virus signature
> database 17555 (20180615) __
>
> The message was checked by ESET Mail Security.
> http://www.eset.com
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at http://www.ibphoenix.com/
> resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

Re: [firebird-support] Error creating Foreign key

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
you allowed to create the foreign key if you use a completely different
name for it?

And what is the exact definition of the primary, foreign and unique keys
for the tables in question?

Set

2018-06-12 10:59 GMT+02:00 Mourad Hedfi mou...@bilog.fr [firebird-support] <
firebird-support@yahoogroups.com>:

>
>
> Hello All,
>
> I return to you after many tests and finally I've found that when I've two
> or more successive FK to create, only the first is created and I've the
> error "Object in use" for the others.
>
> I've tried :
> 1. Add a commit command after every FK create command
> 2. Disconnect  after every FK create command
>
> ==> always the same error.
>
> Doe's anyone have an idea to fix this problem ?
>
> Bien cordialement.
>
> *Mourad **Hedfi*
> Directeur de projets
> Tél. : 01 83 62 36 85
>
> 2018-05-31 9:22 GMT+01:00 Mourad Hedfi :
>
>> Hello,
>>
>> Thanks for your reply.
>>
>> 1. Why do you remove all FK?
>> --> Data update covers all data's tables. I must remove all FK to be able
>> to insert, delete records.
>>
>>
>> 2. Why do you still use such old Firebird version?
>> --> You are wright. It's a big client database and I mist have it's
>> agreement to use a recent version.
>>
>>
>> Many things was fixed since FB1.5 and i suppose that only real fix to
>> your problem is using recent FB.
>>
>> You can try disconnect after every FK.
>>
>> --> I'll try this and tell you if it resolve the problem. Note that on
>> 477 deleted FK, 326 are created.
>>
>>
>> You can also try do select count(*) from updated tables but you say that
>> you do backup and restore...
>>
>>
>>
>> Bien cordialement.
>>
>> *Mourad **Hedfi*
>> Directeur de projets
>> Tél. : 01 83 62 36 85
>>
>> 2018-05-31 8:42 GMT+01:00 liviuslivius liviusliv...@poczta.onet.pl
>> [firebird-support] :
>>
>>>
>>>
>>> Hi,
>>>
>>> Two questions
>>> 1. Why do you remove all FK?
>>> 2. Why do you still use such old Firebird version?
>>>
>>> Many things was fixed since FB1.5 and i suppose that only real fix to
>>> your problem is using recent FB.
>>>
>>> You can try disconnect after every FK.
>>> You can also try do select count(*) from updated tables but you say that
>>> you do backup and restore...
>>>
>>>
>>>
>>> Regards,
>>> Karol Bieniaszewski
>>>
>>>
>>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 using MERGE when just updating and not
inserting (and been too poor at using it otherwise), I'll see if it is
possible to change it like this, though I don't have high hopes that it
will be any quicker.

The columns returned is the PK of the table that is updated (an integer)
and the number of rows updated so far (also integer). Each update should
only update one row, so this is there just to be certain that the correct
number of rows are updated.



2018-03-05 9:53 GMT+01:00 Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] :

> On 3-3-2018 17:43, setysvar setys...@gmail.com [firebird-support] wrote:
> > The execute blocks had similar similar content to:
> >
> > for select mt.PK
> > from  tt
> > join  mt on 
> > where mt.integerfield = 1 into :PK do
> > begin
> > suspend;
> > update 
> > set field1 = 'K', field2 = '-'
> > where pk = :pk;
> > MyCount = MyCount + rows_affected;
> > end
> >
> > The join criteria were
> >
> > (slow)
> > tt.VarChar40 = mt.VarChar40
> >
> > (quick)
> > tt.int1 = mt.int1 and tt.int2 = mt.int2
> >
> > Running the select part without the update is quick in both cases.
> >
> > Can anyone enlighten me regarding what the cause of the performance
> > difference may be? I'm simply puzzled and clueless...
>
> What happens if you use MERGE instead of doing the updates 'manually',
> or if you remove the SUSPEND statement? Which columns is the EXECUTE
> BLOCK returning?
>
> Mark
> --
> Mark Rotteveel
>
>
> 
> Posted by: Mark Rotteveel 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at http://www.ibphoenix.com/
> resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
) as integer) > 0

Set

Re: [firebird-support] Problem with where clause

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 to fix this:

Either

V1=:V1 || ' WHERE CF.DATE_COMMANDE'||' BETWEEN '''||:FROM_DATE||''' AND
'''||:TO_DATE||;

or (named parameters):

V1=:V1 || ' WHERE CF.DATE_COMMANDE BETWEEN :MY_FROM_DATE AND :MY_TO_DATE';

FOR EXECUTE STATEMENT (V1) (MY_FROM_DATE := FROM_DATE, MY_TO_DATE :=
TO_DATE)
INTO ...

or (positional parameters):

V1=:V1 || ' WHERE CF.DATE_COMMANDE BETWEEN ? AND ?';

FOR EXECUTE STATEMENT (V1) (FROM_DATE, TO_DATE)
INTO ...

HTH,
Set

2018-01-05 12:58 GMT+01:00 Köditz, Martin martin.koed...@it-syn.de
[firebird-support] :

>
>
> Hi,
>
>
>
> I think there is a quote missing at the end of „:TO_DATE“;
>
>
>
> Regards
>
> Martin
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 >= t.DaData, DC.SCARICO, 0)) as Scarico,
  SUM(iif(DT.DATA >= t.DaData, DC.CARICO - DC.SCARICO, 0)) as ESISTENZA,
  SUM(iif(DT.DATA >= t.DaData, DC.IMPEGNATI, 0)) as Impegnati,
  SUM(iif(DT.DATA >= t.DaData, DC.ORDINATI, 0)) as Ordinati
FROM DOC_CORPO DC
JOIN DOC_TESTA DT ON DC.DOC_TESTA_ID = DT.ID
CROSS JOIN tmp T
WHERE DC.ARTICOLO_ID = :ID
  AND DT.DATA <= :AData
GROUP BY
  DT.DEPOSITO_ID

The reason for the CTE is because parameters are not variables and I have
experienced cases when I've tried to refer to the same parameter twice only
to discover that IBO treated it as two separate parameters (I don't know
what components you use, but the CTE eliminates any ambiguity).

In addition to this, I updated your query from implicit to explicit JOIN
and simplified a bit (well, at least in my opinion).

The way I would expect my query to differ from yours, is that mine will
return rows with values in RIPORTO, but only 0's for the other summed
fields. I've no clue whether or not this is what you want, please tell us
more specifically what you're looking for if it is something different.

HTH,
Set

2018-01-04 13:32 GMT+01:00 Mark Rotteveel m...@lawinegevaar.nl
[firebird-support] :

> Without relevant DDL and sample data we can't really help you.
>
> Mark
>
> On 4-1-2018 12:36, Luigi Siciliano luigi...@tiscalinet.it
> [firebird-support] wrote:
> > Hallo,
> >
> > The following query works fine but returns *all nulls* if no rows
> > between :DaData and :AData
> >
> > SELECT
> >
> > DEPOSITO_ID,
> > RIPORTO,
> > CARICO,
> > SCARICO,
> > RIPORTO + ESISTENZA AS ESISTENZA,
> > IMPEGNATI,
> > ORDINATI
> > FROM
> > (
> >SELECT
> >  DT.DEPOSITO_ID,
> >  (SELECT
> > SUM(DC2.CARICO - DC2.SCARICO)
> >   FROM
> > DOC_CORPO DC2,
> > DOC_TESTA DT2
> >   WHERE
> > DC2.DOC_TESTA_ID = DT2.ID
> > AND DC2.ARTICOLO_ID = :ID
> > AND DT2.DATA < :DaData
> >  ) AS RIPORTO,
> >  SUM(DC.CARICO) as Carico,
> >  SUM(DC.SCARICO) as Scarico,
> >  SUM(DC.CARICO - DC.SCARICO) as ESISTENZA,
> >  SUM(DC.IMPEGNATI) as Impegnati,
> >  SUM(DC.ORDINATI) as Ordinati
> >FROM
> >  DOC_CORPO DC, DOC_TESTA DT
> >WHERE
> >  DC.DOC_TESTA_ID = DT.ID
> >  AND DC.ARTICOLO_ID = :ID
> >  AND DT.DATA >= :DaData
> >  AND DT.DATA <= :AData
> >GROUP BY
> >  DT.DEPOSITO_ID
> > )
> >
> > How I can modify it?
> >
> > Thanks.
> >
>
>
> --
> Mark Rotteveel
>
>
> 
> Posted by: Mark Rotteveel 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at http://www.ibphoenix.com/
> resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

Re: [firebird-support] Lock Conflict

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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.
>
> We're currently testing v3.02.  v1.56 has served us extremely well.
>
>
>
> On Fri, Dec 15, 2017 at 10:04 AM, Рустам Муса-Ахунов rusta...@ukr.net
> [firebird-support]  wrote:
>
>>
>>
>>
>> How can I receive this error when inserting a new row and prior to
>> committing?
>>
>> May be, 2-nd query starts another transaction (in an unobvious way),
>> while 1-st transaction still active.
>>
>> Why do you not upgrade your Firebird and DB to 2.5 version?
>>
>>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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

2017-11-28 15:38 GMT+01:00 Laurentiu Anisie alauren...@gmail.com
[firebird-support] :

>
>
> I don't have null's.
>
> On Tue, Nov 28, 2017 at 4:27 PM, LtColRDSChauhan rdsc1...@gmail.com
> [firebird-support]  wrote:
>
>>
>>
>> Please check if you have null values.
>> Regards,
>> Rajiv
>>
>> On 28 Nov 2017 7:05 pm, "alauren...@gmail.com [firebird-support]" <
>> firebird-support@yahoogroups.com> wrote:
>>
>>>
>>>
>>> Hi,
>>>
>>>
>>> I have 2 tables and I want to select some data from them based on a
>>> common records. In the query, one of the column must be calculated as
>>> product of one field from table 1 and one field from table 2.
>>>
>>> When I run the query, i get one row with the data without calculation,
>>> and one with calculation
>>>
>>>
>>> The query is this:
>>>
>>>
>>> SELECT A.CODSUB, A.CODOB, A.CODSBB, A.CANT*B.CANT FROM LSUBANS A,X01 B
>>>
>>> WHERE A.CODSUB=B.CODSBB
>>>
>>>
>>> LSUBANS
>>>
>>> CODSUB  CODOB  CODSBB  CANT
>>>
>>> SB3   EL8 3
>>>
>>>
>>> X01
>>>
>>> CODSUB  CODOB  CODSBB  CANT
>>>
>>> SB4SB3 3
>>>
>>>
>>> the result of query had to be:
>>>
>>> CODSUB  CODOB  CODSBB  CANT
>>>
>>> SB3   EL8 9
>>>
>>>
>>> instead is:
>>>
>>> CODSUB  CODOB  CODSBB  CANT
>>>
>>> SB3   EL8 3
>>>
>>> SB3   EL8 9
>>>
>>>
>>> What I do wrong?
>>>
>>>
>>> Thanks
>>>
>>>
>>>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
  NotInTable = FromValue;
  while (NotInTable <= ToValue) do
  begin
if (not exists (select * from temptable
where id = :NotInTable )) then
  suspend;
NotInTable = NotInTable + 1;
  end
end

HTH,
Set

2017-11-28 11:56 GMT+01:00 antoedinchar...@gmail.com [firebird-support] <
firebird-support@yahoogroups.com>:

>
>
> Hi,
>
> I am looking for a solution to get values in query and those not available
> in table data.
>
>
> CREATE TABLE TEMPTABLE(
>
> ID SMALLINT);
>
>
> INSERT INTO TEMPTABLE(ID) VALUES(1);
>
>
> SELECT * FROM TEMPTABLE
>
> WHERE ID IN (1,2)
>
>
> in the Above case I want to get the value 2 as output of a query.
>
>
> Kindly Help.
>
> Thanks In Advance.
>
>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 some values that are used a lot more) is a source for inserts being
slow as well as potentially error prone (in case of concurrent inserts, the
same values would be returned for both rows, which may or may not be what
you want).

HTH,
Set

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 performance drop friday and it lasted until monday around 17.
> The place where we host our servers, had an update of some antivirus
> software which gave huge problems.
> At first we suspected Firebird and the new server, so late sunday night I
> moved all back to the old server. Performance still very low.
>
> That said - when we finally folund the reason, i got back to try to locate
> perfomance bottleneck on this one routine.
> I located a BEFORE INSERT trigger, which causes this problem.
>
> This one:
>
>
> *CREATE TRIGGER WEB_UDSALG_SPR FOR VAREFRVSTR_DETAIL INACTIVE BEFORE
> INSERT POSITION 11 AS*
> *begin *
> *  Select *
> *MAX(WEB_Udsalg_Spr), *
> *MAX(WEB_Udsalg_Stk), *
> *MAX(WEB_Udsalg_Type) *
> *from VareFrvStr_Detail Where *
> *  VarePlu_ID=NEW.VarePlu_ID and *
> *  Afdeling_ID=NEW.Afdeling_ID *
> *Into *
> *NEW.WEB_Udsalg_Spr, *
> *NEW.WEB_Udsalg_Stk, *
> *NEW.WEB_Udsalg_Type;*
> *end*
>
> Disabling this one and performance for this one routine increased back to
> normal.
>
>
> If I try runing this query in DBW and look at the plan, its like this:
>
>
> PLAN (VAREFRVSTR_DETAIL INDEX (VAREFRVSTR_DETAIL_VNR, RDB$FOREIGN105))
>
> VAREFRVSTR_DETAIL_VNR: 0,1301287
> (Foreign key to field PLU_NR in table VARER with 76.863 rows)
>
> RDB$FOREIGN105: 0,01724137925
> This is the primary key of the table VAREFRVSTR_DETAIL.
> Primary key is:
>
>  PRIMARY KEY (VAREPLU_ID, FARVE_NAVN, LAENGDE_NAVN, STOERRELSE_NAVN,
> AFDELING_ID)
>
> All fields in the primary key is VARCHAR(30).
> I would never do it this way, but this is before my time, and was setup in
> 1999.
>
> I then did a SET STAT for the above 2 indeices.
> Afterwards its like:
>
> RDB$FOREIGN105: 0,01724137925
> VAREFRVSTR_DETAIL_VNR: 0,1300965
>
> This was the same problem.
>
>
>
> Then I tested the PLAN on an older copy.
>
> It then said:
>
> PLAN (VAREFRVSTR_DETAIL INDEX (VAREFRVSTR_DETAIL_VNR))
>
> So on this server, where it works fast, the plan does not use the primary
> key (RDB$FOREIGN105).
>
> This stat of the two indices on this old server is:
>
> RDB$FOREIGN105:  0,01724137925 (but not used).
> VAREFRVSTR_DETAIL_VNR: 0,1332285
>
> So they are more or less alike.
> On this server (my testserver) im running FB 2.5.3 SS.
>
>
> This means - this must be the reason for the performance drop.
> Right?
>
> And can I by any means changed this trigger to not use primary key.
> Logically it only need VAREFRVSTR_DETAIL_VNR.
>
>
>
>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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, SalgsPrisStk, EngrosPris, EnhedsNogle, OprettelsesDato,
LeverID, VareGrpID, Afdeling_ID)
  SELECT :PVarePlu_ID, :PFarve_Navn, :PLaengde_Navn,
:PStoerrelse_Navn, :PV509Index, :PMinBeholdning, :PGenbestilling,
:PNormalStkAntal, spe.STDVALUTA,
 :PProvision, spe.STDMOMS, :PVejetKostPrisStk,
:PSalgsPrisStk, :PEngrosPris, :PEnhedsNogle, :POprettelsesDato, :PLeverID,
:PVareGrpID, A.AFDELINGSNUMMER
  FROM AFDELING A
  INNER JOIN STAMDATA_PRG_EXT spe ON spe.AFDELING_ID =
A.AFDELINGSNUMMER
  WHERE A.AFDELINGSNUMMER <> :PAFDELING_ID
and not exists (select v509Index from VAREFRVSTR_DETAIL vd
where VarePlu_ID = :PVarePlu_ID and
  Afdeling_ID = A.AFDELINGSNUMMER and
  Farve_Navn = :PFarve_Navn and
  Stoerrelse_Navn = :PStoerrelse_Navn and
  Laengde_Navn = :PLaengde_Navn)

is identical and takes the same time. You may have to cast the parameters
to appropriate type.

The select part of this insert may also help in discovering whether or not
the same plan is used on the old and new server.

Set

2017-11-17 13:27 GMT+01:00 Рустам Муса-Ахунов rusta...@ukr.net
[firebird-support] :

> Try to determine slowest query:
>
> - "SELECT ...  FROM  AFDELING A  INNER JOIN STAMDATA_PRG_EXT ..."
> or
> - "select v509Index from VAREFRVSTR_DETAIL ..."
> or
> - "Insert into VareFrvStr_Detail ..."
>
>
> Problem can be with indexes - check whether all indexes exists in new DB
> (after restore).
> Also check your INSERT triggers on VAREFRVSTR_DETAIL (if any).
>
>
> 
> Posted by: =?UTF-8?b?0KDRg9GB0YLQsNC8INCc0YPRgdCwLdCQ0YXRg9C90L7Qsg==?=
> 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at http://www.ibphoenix.com/
> resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 (since I thought that MyTable could be huge, whereas only a fraction
could be green), but discovered that a recursive cte based on a recursive
cte didn't work and hence, I didn't make them dependent on each other.

I think your query now seems good, and I hope that my fears that it will be
too slow will not be a problem in your case.

Set

2017-11-08 17:32 GMT+01:00 josef.gschwendt...@quattro-soft.de
[firebird-support] :

>
>
> Hi Set,
>
> this is a very interesting solution.
> I changed it a bit to create a "SortOrder" that should always work.
> What do you think?
>
> I will test it on a real (big) table and see how the performance is.
>
> Maybe " cast(... as varchar(200))" is not necessary if I don't select
> "cte2.SortOrder".
>
>
> with recursive cte as
> (select id, id_parent, green, SortText
>  from MyTable
>  where Green = 'Yes'
>  union all
>  select T2.id, T2.id_parent, T2.green, T2.SortText
>  from MyTable T2
>  join cte on T2.ID = cte.id_parent),
>
> cte2 as
> (select t3.id, cast(rpad(t3.SortText,10) as varchar(200)) SortOrder
>  from MyTable t3
>  where t3.id_parent is null
>  union all
>  select T2.id, cte2.SortOrder || rpad(T2.SortText,10)
>  from MyTable T2
>  join cte2 on cte2.ID = t2.id_parent)
>
> select distinct cte.id, cte.id_parent, cte.green, cte.SortText,
> cte2.SortOrder
> from cte
> join cte2 on cte.id = cte2.id
> order by cte2.SortOrder
>
> Regards,
> Josef
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 on T2.ID  = cte.id_parent)



select id, id_parent, green, SortText

  from cte

  group by 1, 2, 3, 4

order by min(SortColumn)

Don't know whether or not it works, I use recursive CTEs very rarely and
haven't tried much sorting of them.

HTH,

Set

2017-11-06 12:32 GMT+01:00 josef.gschwendt...@quattro-soft.de
[firebird-support] :

>
>
> Hello,
>
>
> I have a table which data is representing a tree:
>
>
> CREATE TABLE MyTABLE (
> ID INTEGER NOT NULL,
> ID_PARENT INTEGER,
> GREEN VARCHAR(3),
> SortText VARCHAR(5),
> CONSTRAINT PK_MYTABLE PRIMARY KEY (ID));
>
> This is the data in this table:
>
> ID
>
> PARENT_ID
>
> GREEN
>
> SortText
>
> 1
>
>
>
> No
>
> A3
>
> 2
>
> 1
>
> Yes
>
> B1
>
> 3
>
> 2
>
> No
>
> C6
>
> 4
>
> 2
>
> Yes
>
> C5
>
> 5
>
> 2
>
> Yes
>
> C4
>
> 6
>
> 1
>
> No
>
> B2
>
> 7
>
> 6
>
> No
>
> C4
>
> 8
>
> 6
>
> Yes
>
> C3
>
> 9
>
>
>
> No
>
> A2
>
> 10
>
> 9
>
> No
>
> B3
>
> 11
>
> 10
>
> No
>
> C2
>
> 12
>
>
>
> No
>
> A1
>
> 13
>
> 12
>
> Yes
>
> B4
>
> 14
>
> 13
>
> No
>
> C1
>
>
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (1, NULL,
> 'No', 'A3');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (2, 1, 'Yes',
> 'B1');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (3, 2, 'No',
> 'C7');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (4, 2, 'Yes',
> 'C6');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (5, 2, 'Yes',
> 'C5');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (6, 1, 'No',
> 'B2');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (7, 6, 'No',
> 'C4');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (8, 6, 'Yes',
> 'C3');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (9, NULL,
> 'No', 'A2');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (10, 9, 'No',
> 'B3');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (11, 10, 'No',
> 'C2');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (12, NULL,
> 'No', 'A1');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (13, 12,
> 'Yes', 'B4');
>
> INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (14, 13, 'No',
> 'C1');
>
>
>
> Table-Data shown as Tree
>
> A3
>
>
>
>
>
>
>
> B1
>
>
>
>
>
>
>
> C7
>
>
>
>
>
> C6
>
>
>
>
>
> C5
>
>
>
> B2
>
>
>
>
>
>
>
> C4
>
>
>
>
>
> C3
>
> A2
>
>
>
>
>
>
>
> B3
>
>
>
>
>
>
>
> C2
>
> A1
>
>
>
>
>
>
>
> B4
>
>
>
>
>
>
>
> C1
>
> Now I need a dataset which suspends the green tree-nodes with the
> complete tree-path for each green cell. This dataset should be ordered
> alphabetically (A1 before A3 and C5 before C6)
>
> A1
>
>
>
>
>
>
>
> B4
>
>
>
> A3
>
>
>
>
>
>
>
> B1
>
>
>
>
>
>
>
> C5
>
>
>
>
>
> C6
>
>
>
> B2
>
>
>
>
>
>
>
> C3
>
>
>
>
> *I (almost) get the result I want with this statement: *
> with recursive cte as
>
>   (select id, id_parent, green, SortText
>
>  from MyTable
>
> where Green = 'Yes'
>
> union all
>
>select T2.id, T2.id_parent, T2.green, T2.SortText
>
>  from MyTable T2
>
>   join cte on T2.ID = cte.id_parent)
>
>
>
> select distinct id, id_parent, green, SortText
>
>   from cte
>
>
>
>
>
>
>
> The dataset is ok, but the ordering is not (because there is no “Depth
> First by” – Clause)
>
>
>
> With FB3 I tried to use windows functions (something like below):
>
> Rank() over(Partition by id_parent order by SortText)
>
>
>
> But I could not solve my problem!  - For instance, I got the message:
> Recursive member of CTE cannot use aggregate or window function.
>
>
> Could anybody give me a hint how to solve this.
>
>
> Thank you,
>
> Josef
>
>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 condition, Amount1, Amount2)

Set

2017-10-24 1:34 GMT+02:00 j...@acousticdesign.co.uk [firebird-support] <
firebird-support@yahoogroups.com>:

>
>
> Thanks for your reply. I haven't tried it as I came across Tomasz's
> suggestion first, and that works a treat!
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

OK, that's completely different, but I've done something similar once
before. It is not possible to directly compare sets for equality, but it is
possible to check that set A doesn't contain anything that isn't in set B
and that set B doesn't contain anything that isn't in set A. I assume NODE
is the one common denominator:

with tmp (NODE) as
(select distinct NODE from routes),
new_routes(NODE) as
(select t.NODE
 from tmp t
 where not exists(select *
  from routes r
  left join route_history rh on r.NODE = rh.NODE
and r.ROUTE_INDEX =
rh.ROUTE_INDEX
and r.LINK_NODE = rh.LINK_NODE
and r.QUALITY = rh.QUALITY
  where t.NODE = r.NODE
and rh.NODE is null)
  and not exists(select *
  from route_history rh
  left join routes r on r.NODE = rh.NODE
and r.ROUTE_INDEX =
rh.ROUTE_INDEX
and r.LINK_NODE = rh.LINK_NODE
and r.QUALITY = rh.QUALITY
  where t.NODE = rh.NODE
and r.NODE is null))
select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
from new_routes nr
join routes r on nr.NODE = r.NODE

Does this get you the result you want?
Set

2017-09-28 9:19 GMT+02:00 'Daniel Miller' dmil...@amfes.com
[firebird-support] <firebird-support@yahoogroups.com>:

>
>
> Thanks - but that still doesn't work.  I get a huge return set with a ton
> of duplicates.  I think something's being lost in translation.  A simple
> match of one row isn't sufficient - I need to match the "set".
>
> With the following six records:
> 5557111160
> 5557211150
> 5557351042
> 5557450732
> 5557552222
> 5557651102
>
> That is the list of routes a given node has at a specific point in time.
> So it's perfectly possible at another time to have fewer, or more, or
> different routes.  And I need to consider ALL the routes in play for a
> given timestamp to be a single "set".  So if today I have the above list of
> routes, and yesterday route index 4 was looking at a different node - that
> would constitute a different and unique set even though the other 5 records
> match.
>
> 5557111160
> 5557211150
> 5557351042
> 555742
> 5557552222
> 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
> exist in ROUTE_HISTORY should it be considered a duplicate condition.
>
> --
> Daniel
>
> On 9/27/2017 11:53:48 PM, "Svein Erling Tysvær setys...@gmail.com
> [firebird-support]" <firebird-support@yahoogroups.com> wrote:
>
> 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
>   and r.quality is not distinct from rh1.quality
> left join route_history rh2 on rh1.node = rh2.node
>   and rh1.route_index = rh2.route_index
>   and rh1.route_updated < rh2.route_updated
> where rh1.node is null
>   and rh2.node is null
>
> I use two LEFT JOINS because I assume you want things to be returned if
> things are changed back, e.g. if you have
>
> 5557111160
> 5557111160
>
> then you only want one row in route_history, but if you have
>
> 5557111160
> 5557111161
> 5557111160
>
> then you don't want the last record to be considered a duplicate, but know
> when it changed back. If ROUTE_INDEX is just a running number, you consider
>
> 5557111160
> 5557211160
>
> to be duplicates, and there are no gaps between the ROUTE_INDEX for each
> NODE, then the query can be simplified:
>
> 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+1 = rh1.route_index
>   and r.link_node = rh1.link_node
>   and r.quality is not distinct from rh1.quality
> where rh1.node is null
>
> Note that I use IS NOT DISTINCT so that NULLs are consid

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

Sorry again, Daniel, fourth query required (I forgot that things may vary
depending on time, this is something I've never done before):

with tmp(NODE, ROUTE_UPDATED) as
(select distinct rh.NODE, rh.ROUTE_UPDATED
 from route_history rh
 join routes r on rh.NODE = r.NODE), //*This join is only for performance
reasons in case ROUTE_HISTORY contains a lot more records than ROUTES and
may be removed*/
new_routes(NODE) as
(select distinct t.NODE
 from tmp t
 where not exists(select *
  from routes r
  left join route_history rh on r.NODE = rh.NODE
and r.ROUTE_INDEX =
rh.ROUTE_INDEX
and r.LINK_NODE = rh.LINK_NODE
and r.QUALITY is not distinct
from rh.QUALITY
and t.ROUTE_UPDATED =
rh.ROUTE_UPDATED
  where t.NODE = r.NODE
and rh.NODE is null)
  and not exists(select *
  from route_history rh
  left join routes r on r.NODE = rh.NODE
and r.ROUTE_INDEX =
rh.ROUTE_INDEX
and r.LINK_NODE = rh.LINK_NODE
and r.QUALITY is not distinct
from rh.QUALITY
  where t.NODE = rh.NODE
and t.ROUTE_UPDATED = rh.ROUTE_UPDATED
and r.NODE is null))
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
> QUALITY.
>
> Note that this checks only checks if there has ever been anything
> identical in ROUTE_HISTORY, it doesn't restrain itself to the latest entry
> only (i.e. the query I've written is slightly similar to the second query I
> wrote, it has to be extended if you want something slightly similar to the
> first query). Also, my third query doesn't consider duplicate rows (since
> the fields seem to be the primary key in ROUTE, it isn't theoretically
> possible in your example).
>
> Set
>
> 2017-09-28 9:52 GMT+02:00 Svein Erling Tysvær <setys...@gmail.com>:
>
>> OK, that's completely different, but I've done something similar once
>> before. It is not possible to directly compare sets for equality, but it is
>> possible to check that set A doesn't contain anything that isn't in set B
>> and that set B doesn't contain anything that isn't in set A. I assume NODE
>> is the one common denominator:
>>
>> with tmp (NODE) as
>> (select distinct NODE from routes),
>> new_routes(NODE) as
>> (select t.NODE
>>  from tmp t
>>  where not exists(select *
>>   from routes r
>>   left join route_history rh on r.NODE = rh.NODE
>> and r.ROUTE_INDEX =
>> rh.ROUTE_INDEX
>> and r.LINK_NODE =
>> rh.LINK_NODE
>> and r.QUALITY = rh.QUALITY
>>   where t.NODE = r.NODE
>> and rh.NODE is null)
>>   and not exists(select *
>>   from route_history rh
>>   left join routes r on r.NODE = rh.NODE
>> and r.ROUTE_INDEX =
>> rh.ROUTE_INDEX
>> and r.LINK_NODE =
>> rh.LINK_NODE
>> and r.QUALITY = rh.QUALITY
>>   where t.NODE = rh.NODE
>> and r.NODE is null))
>> select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
>> from new_routes nr
>> join routes r on nr.NODE = r.NODE
>>
>> Does this get you the result you want?
>> Set
>>
>> 2017-09-28 9:19 GMT+02:00 'Daniel Miller' dmil...@amfes.com
>> [firebird-support] <firebird-support@yahoogroups.com>:
>>
>>>
>>>
>>> Thanks - but that still doesn't work.  I get a huge return set with a
>>> ton of duplicates.  I think something's being lost in translation.  A
>>> simple match of one row isn't sufficient - I need to match the "set".
>>>
>>> With the following six records:
>>> 5557111160
>>> 5557211150
>>> 5557351042
>>> 5557450732
>>> 5557552222
>>> 5557651102
>>>
>>> That is the list of routes a given node has at a specific point in
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

Sorry, I forgot that should probably use 'IS NOT DISTINCT' and not '=' for
QUALITY.

Note that this checks only checks if there has ever been anything identical
in ROUTE_HISTORY, it doesn't restrain itself to the latest entry only (i.e.
the query I've written is slightly similar to the second query I wrote, it
has to be extended if you want something slightly similar to the first
query). Also, my third query doesn't consider duplicate rows (since the
fields seem to be the primary key in ROUTE, it isn't theoretically possible
in your example).

Set

2017-09-28 9:52 GMT+02:00 Svein Erling Tysvær <setys...@gmail.com>:

> OK, that's completely different, but I've done something similar once
> before. It is not possible to directly compare sets for equality, but it is
> possible to check that set A doesn't contain anything that isn't in set B
> and that set B doesn't contain anything that isn't in set A. I assume NODE
> is the one common denominator:
>
> with tmp (NODE) as
> (select distinct NODE from routes),
> new_routes(NODE) as
> (select t.NODE
>  from tmp t
>  where not exists(select *
>   from routes r
>   left join route_history rh on r.NODE = rh.NODE
> and r.ROUTE_INDEX =
> rh.ROUTE_INDEX
> and r.LINK_NODE = rh.LINK_NODE
> and r.QUALITY = rh.QUALITY
>   where t.NODE = r.NODE
> and rh.NODE is null)
>   and not exists(select *
>   from route_history rh
>   left join routes r on r.NODE = rh.NODE
> and r.ROUTE_INDEX =
> rh.ROUTE_INDEX
> and r.LINK_NODE = rh.LINK_NODE
> and r.QUALITY = rh.QUALITY
>   where t.NODE = rh.NODE
> and r.NODE is null))
> select r.NODE, r.ROUTE_INDEX, r.LINK_NODE, r.QUALITY
> from new_routes nr
> join routes r on nr.NODE = r.NODE
>
> Does this get you the result you want?
> Set
>
> 2017-09-28 9:19 GMT+02:00 'Daniel Miller' dmil...@amfes.com
> [firebird-support] <firebird-support@yahoogroups.com>:
>
>>
>>
>> Thanks - but that still doesn't work.  I get a huge return set with a ton
>> of duplicates.  I think something's being lost in translation.  A simple
>> match of one row isn't sufficient - I need to match the "set".
>>
>> With the following six records:
>> 5557111160
>> 5557211150
>> 5557351042
>> 5557450732
>> 5557552222
>> 5557651102
>>
>> That is the list of routes a given node has at a specific point in time.
>> So it's perfectly possible at another time to have fewer, or more, or
>> different routes.  And I need to consider ALL the routes in play for a
>> given timestamp to be a single "set".  So if today I have the above list of
>> routes, and yesterday route index 4 was looking at a different node - that
>> would constitute a different and unique set even though the other 5 records
>> match.
>>
>> 5557111160
>> 5557211150
>> 5557351042
>> 555742
>> 5557552222
>> 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
>> exist in ROUTE_HISTORY should it be considered a duplicate condition.
>>
>> --
>> Daniel
>>
>> On 9/27/2017 11:53:48 PM, "Svein Erling Tysvær setys...@gmail.com
>> [firebird-support]" <firebird-support@yahoogroups.com> wrote:
>>
>> 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
>>   and r.quality is not distinct from rh1.quality
>> left join route_history rh2 on rh1.node = rh2.node
>>   and rh1.route_index = rh2.route_index
>>   and rh1.route_updated < rh2.route_updated
>> where rh1.node is null
>>   and rh2.node is null
>>
>> I use two LEFT JOINS because I assume you want things to be returned if
>> things are c

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
  and r.quality is not distinct from rh1.quality
left join route_history rh2 on rh1.node = rh2.node
  and rh1.route_index = rh2.route_index
  and rh1.route_updated < rh2.route_updated
where rh1.node is null
  and rh2.node is null

I use two LEFT JOINS because I assume you want things to be returned if
things are changed back, e.g. if you have

5557111160
5557111160

then you only want one row in route_history, but if you have

5557111160
5557111161
5557111160

then you don't want the last record to be considered a duplicate, but know
when it changed back. If ROUTE_INDEX is just a running number, you consider

5557111160
5557211160

to be duplicates, and there are no gaps between the ROUTE_INDEX for each
NODE, then the query can be simplified:

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+1 = rh1.route_index
  and r.link_node = rh1.link_node
  and r.quality is not distinct from rh1.quality
where rh1.node is null

Note that I use IS NOT DISTINCT so that NULLs are considered equal. If you
prefer, the left joins can be replaced by (nested) NOT EXISTS.

HTH,
Set

2017-09-28 7:22 GMT+02:00 'Daniel Miller' dmil...@amfes.com
[firebird-support] :

>
>
> On 9/27/2017 9:20:54 PM, "liviuslivius liviusliv...@poczta.onet.pl
> [firebird-support]"  wrote:
>
>
> group by + having
> is your friend.
> E.g.
> Select field1, field2, count(*)
> From tablex
> Group by field1, field2
> Having count(*)>1
>
> But in your scenario i do not know if this is the solution. Question is if
> record was changed and in next update "restored" and once again changed is
> this duplicate or no?
> If yes above query is ok if no then procedure is your friend.
>
> And for the future change your audit trigger and check before if there was
> any change
>
> Thank you, but I this isn't quite what I need.  I need to match against
> all the records of a set (up to 8 rows per set) - not just individual rows.
>
> --
> Daniel
>
>
> 
>

Re: [firebird-support] crosstab pivot query

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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, list(d1.sign) day1, list(d2.sign) day2, list(d3.sign)
day3, ... list(d30.sign) day30, list(d31.sign) day31
from tmpPerson p
left join tmpDays d1 on p.person = d1.person and d1."day" = 1
left join tmpDays d2 on p.person = d1.person and d1."day" = 2
left join tmpDays d3 on p.person = d1.person and d1."day" = 3
...
left join tmpDays d30 on p.person = d1.person and d1."day" = 30
left join tmpDays d31 on p.person = d1.person and d1."day" = 31 /*Not
applicable for September, but I assume you want other months as well and it
doesn't harm...*/
group by p.person

If no person has two records any given day, then you don't need 'list' and
'group by'.

Since SQL isn't very suitable for crosstab (*), I normally prefer to select
- possibly with grouping - in Firebird and "crosstabbing" in Excel.

HTH,
Set

(*) My experience is more or less limited to Fb 2.5, but I cannot imagine
how to prepare a statement with an unknown number of columns, in theory
that could also mean an unknown number of parameters

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

> 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. 19CUBIERTA
>
>As you can see,  the two resulting rows are identical.

For the human eye, yes, for a computer, maybe.

>Now I run the following aggregate query:
>select a.num_part,a.cve_prov,a.cve_impo,COUNT(DISTINCT a.des_merc)
>from ctrac_clasif a
>where a.num_part = '0630039046'
>group by a.num_part,a.cve_prov,a.cve_impo
>
>NUM_PART   CVE_PROV  CVE_IMPO  COUNT
>=  = = ==
>0630039046 3400. 192

So they are different for your computer.

Is DES_MERC defined as BLOB or something similar? If so, change to
something like:

select a.num_part,a.cve_prov,a.cve_impo,COUNT(DISTINCT cast(a.des_merc as
varchar(32)))
from ctrac_clasif a
where a.num_part = '0630039046'
group by a.num_part,a.cve_prov,a.cve_impo

If not, do as Karol asks and show us how to replicate your case. At least,
I only get one row if I do

SELECT 'CUBIERTA' FROM RDB$DATABASE
UNION
SELECT 'CUBIERTA ' FROM RDB$DATABASE

whereas two rows are returned if I do

SELECT CAST('CUBIERTA' AS BLOB CHARACTER SET ISO8859_1) FROM RDB$DATABASE
UNION
SELECT CAST('CUBIERTA' AS BLOB CHARACTER SET ISO8859_1) FROM RDB$DATABASE

HTH,
Set

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 23:08 GMT+02:00 hamacker sirhamac...@gmail.com
[firebird-support] :

>
>
> Something wrong in my test because 'sintax error' when I try role name
> without username/password.
> Can you help me?
>
>
> EXECUTE BLOCK
> returns (result_value Integer)
> AS
> declare variable lid_cv bigint=3;
> declare variable lcoditem varchar(30)='(C120P2AC)';
> declare variable lvl_base NUMERIC(18,2)=100.00;
> declare variable lid_cv_item bigint;
> declare variable lid_cv_itens_sub1 bigint;
> declare variable lid_cv_itens_sub2 bigint;
> declare variable lcvi_coditem varchar(30);
> declare variable lsub1_coditem varchar(30);
> declare variable lsub2_coditem varchar(30);
> declare variable lsql_update_cv_itens varchar(4096);
> declare variable lsql_update_cv_itens_sub1 varchar(4096);
> declare variable lsql_update_cv_itens_sub2 varchar(4096);
> begin
>   lsql_update_cv_itens='update cv_itens set vl_base=? where
> (id_cv_item=?);';
>   lsql_update_cv_itens_sub1='update cv_itens_sub1 set vl_base=? where
> (id_cv_itens_sub1=?);';
>   lsql_update_cv_itens_sub2='update cv_itens_sub2 set vl_base=? where
> (id_cv_itens_sub2=?);';
>   result_value=0;
>   for select id_cv_item, coditem from cv_itens
>   where id_cv=:lid_cv
>   into :lid_cv_item, :lcvi_coditem do
>   begin
> if (:lcvi_coditem=:lcoditem) then
> begin
>   execute statement (lsql_update_cv_itens)
> (:lvl_base, :lid_cv_item)
> as user 'SYSDBA' password 'masterkey' role 'perfil_vendas'; --
> sintaxe error when remove user and password
>   result_value=(result_value+1);
> end
> for select id_cv_itens_sub1, coditem from cv_itens_sub1
> where id_cv_item=:lid_cv_item
> into :lid_cv_itens_sub1, :lsub1_coditem  do
> begin
>   if (:lsub1_coditem=:lcoditem) then
>   begin
> execute statement (lsql_update_cv_itens_sub1)
>   (:lvl_base, :lid_cv_itens_sub1)
>   as user 'SYSDBA' password 'masterkey' role 'perfil_vendas'; --
> sintaxe error when remove user and password
> result_value=(result_value+1);
>   end
>   for select id_cv_itens_sub2, coditem from cv_itens_sub2
>   where id_cv_itens_sub1=:lid_cv_itens_sub1
>   into :lid_cv_itens_sub2, :lsub2_coditem do
>   begin
> if (:lsub2_coditem=:lcoditem) then
> begin
>   execute statement (lsql_update_cv_itens_sub2)
> (:lvl_base, :lid_cv_itens_sub2)
> as user 'SYSDBA' password 'masterkey' role 'perfil_vendas'; --
> sintaxe error when remove user and password
>   result_value=(result_value+1);
> end
>   end
> end
>   end
>   suspend;
> end
>
>
> 2017-08-30 16:33 GMT-03:00 Dimitry Sibiryakov s...@ibphoenix.com
> [firebird-support] :
>
>> 30.08.2017 21:15, hamacker sirhamac...@gmail.com [firebird-support]
>> wrote:
>> > Here in fb3, to put role name in execute, Its mandatory put username
>> and password too.
>>
>>I was wrong a little, but still you should read the documentation:
>>
>> > - if ON EXTERNAL DATA SOURCE clause is omitted then
>> >   a) statement will be executed against current (local) database
>> >   b) if AS USER clause is omitted or  equal to
>> CURRENT_USER
>> >   and if ROLE clause is omitted or  equal to CURRENT_ROLE
>> >   then the statement is executed in current connection context
>> >   c) if  is not equal to CURRENT_USER or  not
>> equal to CURRENT_ROLE
>> >   then the statement is executed in separate connection established
>> inside the same
>> >   engine instance (i.e. created new internal connection without
>> Y-Valve and remote layers).
>>
>>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

>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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 foreign
keys to be of a type that is a superset, it also sounds rather harmless (if
properly implemented). Does the SQL standard say anything about this?

Set

Re: [firebird-support] increment

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

Or do you want duplicates, as in:

UPDATE "TABLE" t1
   set "POS" = 1 + (SELECT COUNT(DISTINCT "MONTH")
  FROM "TABLE" t2
  WHERE t2."YEAR" = 2010 and t2."MONTH" < t1."MONTH")
WHERE t1."YEAR" = 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 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
>   begin
>   update table
>  set "POS" = i
>   where  = :PK;
>   i = i + 1;
>   end
> end
>
> HTH,
> Set
>
> 2017-06-15 15:33 GMT+02:00 'Check_Mail' check_m...@satron.de
> [firebird-support] <firebird-support@yahoogroups.com>:
>
>> Hello,
>>
>> How can I increment consisting records like this:
>>
>> Year  month   order pos
>> 2010  2 1null
>> 2010  2 1null
>> 2010  2 1null
>> 2010  3 2   null
>>
>> Pos should be auto increment for all 2010 2 records, null to 1, the second
>> record null to 2 til 4
>>
>> Update table set pos = coalesce(pos,0)+1 where year = 2010 and month = 2
>> works not, all records have the pos=1 value
>>
>> Thank you
>>
>> Best regards
>>
>> Olaf
>>
>>
>> 
>> Posted by: "Check_Mail" <check_m...@satron.de>
>> 
>>
>> ++
>>
>> Visit http://www.firebirdsql.org and click the Documentation item
>> on the main (top) menu.  Try FAQ and other links from the left-side menu
>> there.
>>
>> Also search the knowledgebases at http://www.ibphoenix.com/resou
>> rces/documents/
>>
>> ++
>> 
>>
>> Yahoo Groups Links
>>
>>
>>
>>
>

Re: [firebird-support] increment

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
  begin
  update table
 set "POS" = i
  where  = :PK;
  i = i + 1;
  end
end

HTH,
Set

2017-06-15 15:33 GMT+02:00 'Check_Mail' check_m...@satron.de
[firebird-support] :

> Hello,
>
> How can I increment consisting records like this:
>
> Year  month   order pos
> 2010  2 1null
> 2010  2 1null
> 2010  2 1null
> 2010  3 2   null
>
> Pos should be auto increment for all 2010 2 records, null to 1, the second
> record null to 2 til 4
>
> Update table set pos = coalesce(pos,0)+1 where year = 2010 and month = 2
> works not, all records have the pos=1 value
>
> Thank you
>
> Best regards
>
> Olaf
>
>
> 
> Posted by: "Check_Mail" 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at http://www.ibphoenix.com/
> resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 duplicate values. I did a search to see if others have observed
the same thing, but didn’t find anything (though I’m far better with
Firebirds SELECT statement than internet searching, so this could well be
just me).



Here’s the query (“reproducible” test case):



with tmp(id, greeting) as

(select list(distinct 'I should go last'), list(distinct substring('Hi'
from 1 for 5))

from rdb$database

left join rdb$database r2 on (1=1)

union

select list(distinct 'I shouldn''t duplicate'), list(distinct
substring('Hello' from 1 for 5))

from rdb$database

left join rdb$database r2 on (1=1)

union

select list(distinct 'I shouldn''t duplicate'), list(distinct
substring('Hello' from 1 for 5))

from rdb$database

left join rdb$database r2 on (1=1))

select greeting, list(distinct id), count(distinct id)

from tmp

group by 1

order by Greeting



Expected result:

Hello I shouldn’t duplicate  1

HiI should go last 1



Actual result (on Firebird 2.5.4, probably with ISO8859_1 as the character
set and may actual query may have had NO_NO as the collation):

HiI should go last 1

Hello I shouldn’t duplicate,I shouldn’t duplicate  2



How the wrong result would have been if I had been able to completely make
a copy of my original error (random sorting and lots of duplicates):

HiI should go last 1

Hello I shouldn’t duplicate  1

Hello I shouldn’t duplicate  1



I wrote the query to answer one particular question, so I could get a
similar result to what I wanted by first running only the content of the
CTE and then using PivotTable in Excel, but I was very surprised by bumping
into what I consider being two separate errors inside one query (duplicates
and ordering), or maybe even 2,5 errors since my original query returned
duplicate rows rather than duplicates within LIST(DISTINCT…).



Does anyone have similar experiences or an explanation?
Set

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

>
> 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 the table
> CREATE TABLE "SCHEMA_AUTHOR" ("ID" integer NOT NULL PRIMARY KEY, "NAME"
> varchar(255) NOT NULL, "HEIGHT" integer CHECK ("HEIGHT" >= 0))
>
> 2. Popultate with some data
> INSERT INTO SCHEMA_AUTHOR (ID, NAME, HEIGHT) VALUES ('1', 'Anonimus1',
> NULL);
>
> 3. Alter the table
> ALTER TABLE "SCHEMA_AUTHOR" ADD "SURNAME" varchar(15) DEFAULT 'surname
> default' NOT NULL
>
> After step 3, I have a record with a new column with 'surname default'
> value into SURNAME field.
>
> 4. Drop default definition
> ALTER TABLE "SCHEMA_AUTHOR" ALTER COLUMN "SURNAME" DROP DEFAULT
>
> After step 4, the SURNAME field value is setting to empty string ('')
>
> Is that the correct behavior ?
>

I think you're wrong in assuming that the record after step 3 contains
'surname default'. Please try

select iif( "SURNAME" is null, 'I'm empty', 'I contain a value')
from SCHEMA_AUTHOR

after step 3. I don't use Firebird 3 myself, but I'd be greatly surprised
if they'd changed so that existing records got default values when defining
the default values. Also, take a look at this old document:
https://firebirdsql.org/manual/nullguide-alter-pop-tables.html.

Basically, what you should do is to add another step after step 3:

UPDATE SCHEMA_AUTHOR
   SET "SURNAME" = 'surname default';

HTH,
Set

Re: [firebird-support] join question

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 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' check_m...@satron.de
> [firebird-support] <firebird-support@yahoogroups.com>:
>
>>
>>
>> Hello @ll,
>>
>>
>>
>> I have a small question.
>>
>>
>>
>> Table A
>>
>>
>>
>> NrWorker ineger
>>
>> Name
>>
>> Active 0 or 1
>>
>> …
>>
>>
>>
>> Table B
>>
>>
>>
>> NrWorker integer
>>
>> Year
>>
>> Month
>>
>> ValueWorkerMonth
>>
>>
>>
>> Now I would like to get all NrWorker from Table A with Active 1 and the
>> ValueWorkerMonth from the Table B (from the current month)
>>
>>
>>
>> This is like
>>
>>
>>
>> Select a.nrworker, b.valueworkermonth from table A left join table B on
>> a.nrworker = b.nrworker where a.active = 1 and b.year = 2017 and b.month = 4
>>
>>
>>
>> Fine, but I would like also all Worker (NrWorker from Table A) where are
>> not present in Table B in this month.
>>
>>
>>
>> Table A
>>
>> NrWorker 12
>>
>> NrWorker 13
>>
>> NrWorker 14
>>
>>
>>
>> Table B
>>
>> NrWorker 12
>>
>> Year  2017
>>
>> Month 4
>>
>>
>>
>> I would like to get not only Worker 12,  rather 13 and 14 too.
>>
>>
>>
>> How can I realize it in Firebird?
>>
>>
>>
>> Thank you
>>
>>
>> 
>>
>
>

Re: [firebird-support] join question

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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' check_m...@satron.de
[firebird-support] :

>
>
> Hello @ll,
>
>
>
> I have a small question.
>
>
>
> Table A
>
>
>
> NrWorker ineger
>
> Name
>
> Active 0 or 1
>
> …
>
>
>
> Table B
>
>
>
> NrWorker integer
>
> Year
>
> Month
>
> ValueWorkerMonth
>
>
>
> Now I would like to get all NrWorker from Table A with Active 1 and the
> ValueWorkerMonth from the Table B (from the current month)
>
>
>
> This is like
>
>
>
> Select a.nrworker, b.valueworkermonth from table A left join table B on
> a.nrworker = b.nrworker where a.active = 1 and b.year = 2017 and b.month = 4
>
>
>
> Fine, but I would like also all Worker (NrWorker from Table A) where are
> not present in Table B in this month.
>
>
>
> Table A
>
> NrWorker 12
>
> NrWorker 13
>
> NrWorker 14
>
>
>
> Table B
>
> NrWorker 12
>
> Year  2017
>
> Month 4
>
>
>
> I would like to get not only Worker 12,  rather 13 and 14 too.
>
>
>
> How can I realize it in Firebird?
>
>
>
> Thank you
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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.
Firebird's DSQL subsystem parses DDL statements and passes them to a
component that outputs DYN for interpretation by the Y valve, another
subsystem that is responsible for updating the system tables.

Then Pavel:
BLR & DYN
You will never need those unless you're Firebird core developer or GPRE
user.
Note: BLR & DYN records could be quite long, so set max_blr_length &
max_dyn_length trace options accordingly.

Hence, I figured that DYN is something that I - as a normal user -
hopefully never need to know much about. Does this answer your question, or
are you interested in a more technical answer from one of the developers?

Set

2017-03-27 19:59 GMT+02:00 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support] :

>
>
>
> FBTrace config supports the logging of “dyn requests”,
>
> # Put dyn requests execute records
> log_dyn_requests false
>
> # Print dyn requests or not
> print_dyn false
>
> There is, however, no explanation of what these types of requests are –I
> don’t recall “dyn requests” getting much mention in my over 20 years of
> using FB/IB.
>
> What are “dyn” requests?
>
>
> Sean
>
>
>
>
> 
>

Re: [firebird-support] QUERY QUESTION

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
  WHERE TTFirst.ItemNr   = TTNot.ItemNr
AND TTFirst.DateUsed - 1 = TTNot.DateUsed)
   AND TTLast.DateUsed <= :MyEarlierDate
 GROUP BY 1)
SELECT ItemNr, LastDate, LastDate - FirstDate + 1 as ConsecutiveDays
FROM TMP

2017-03-06 7:44 GMT+01:00 'Stef' s...@autotech.co.za [firebird-support] <
firebird-support@yahoogroups.com>:

> Hi Set,
>
> Your solution returns exactly what I am looking for. Much appreciated.
>
> However when trying to query a snapshot for an earlier date, other than the
> most recent day, seems problematic.
>
> Stef van der Merwe
>
>
> -Original Message-
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]
> Sent: 03 March 2017 10:27 PM
> To: firebird-support@yahoogroups.com
> Subject: 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 was used.
> >
> > i.e.
> >
> > itemnr  dateused
> >
> > abc 2017/02/01
> > abc 2017/02/02
> > abc 2017/02/25
> > abc 2017/02/25
> > abc 2017/02/25
> > abc 2017/02/26
> > abc 2017/02/27
> > abc 2017/02/28
> > abc 2017/03/01
> > abc 2017/03/02
> >
> > The result from above sample should be 6 as there is a break between
> > 2017/02/02 and 2017/02/25?
> Hi Stef, thanks for teaching me a new word, I've never heard 'boffin'
> before!
>
> I think your task roughly can be reformulated as:
>
> I want to find the number of days from the most recent day of a record
> without any record for the preceeding day to the most recent day for a
> particular item, and add 1 to the result.
>
> If so, this query will get you what you're asking for, although it may take
> a while to return any result if the table is large:
>
> 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
>   WHERE NOT EXISTS(SELECT * FROM TEST_TABLE TTNot
>WHERE TTFirst.ItemNr   = TTNot.ItemNr
>  AND TTFirst.DateUsed - 1 = TTNot.DateUsed)
>   GROUP BY 1)
> SELECT ItemNr, LastDate, LastDate - FirstDate + 1 as ConsecutiveDays FROM
> TMP
>
> So, what originally seems simple but grows complex once thinking about how
> to solve it, happens to have a rather simple solution.
>
> HTH,
> Set
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item on the
> main (top) menu.  Try FAQ and other links from the left-side menu there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>
>
> 
> Posted by: "Stef" 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at http://www.ibphoenix.com/
> resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

Re: [firebird-support] QUERY QUESTION

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

Oops, forgot to include the last three lines:

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
  WHERE TTFirst.ItemNr   = TTNot.ItemNr
AND TTFirst.DateUsed - 1 = TTNot.DateUsed)
   AND TTLast.DateUsed <= :MyEarlierDate
 GROUP BY 1)
SELECT ItemNr, LastDate, LastDate - FirstDate + 1 as ConsecutiveDays
FROM TMP

2017-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.ItemNr AND
> TTFirst.DateUsed <= TTLast.DateUsed
>  WHERE NOT EXISTS(SELECT * FROM TEST_TABLE TTNot
>   WHERE TTFirst.ItemNr   = TTNot.ItemNr
> AND TTFirst.DateUsed - 1 = TTNot.DateUsed)
>AND TTLast.DateUsed <= :MyEarlierDate
>  GROUP BY 1)
> SELECT ItemNr, LastDate, LastDate - FirstDate + 1 as ConsecutiveDays
> FROM TMP
>
> 2017-03-06 7:44 GMT+01:00 'Stef' s...@autotech.co.za [firebird-support] <
> firebird-support@yahoogroups.com>:
>
>> Hi Set,
>>
>> Your solution returns exactly what I am looking for. Much appreciated.
>>
>> However when trying to query a snapshot for an earlier date, other than
>> the
>> most recent day, seems problematic.
>>
>> Stef van der Merwe
>>
>>
>> -Original Message-
>> From: firebird-support@yahoogroups.com
>> [mailto:firebird-support@yahoogroups.com]
>> Sent: 03 March 2017 10:27 PM
>> To: firebird-support@yahoogroups.com
>> Subject: 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 was used.
>> >
>> > i.e.
>> >
>> > itemnr  dateused
>> >
>> > abc 2017/02/01
>> > abc 2017/02/02
>> > abc 2017/02/25
>> > abc 2017/02/25
>> > abc 2017/02/25
>> > abc 2017/02/26
>> > abc 2017/02/27
>> > abc 2017/02/28
>> > abc 2017/03/01
>> > abc 2017/03/02
>> >
>> > The result from above sample should be 6 as there is a break between
>> > 2017/02/02 and 2017/02/25?
>> Hi Stef, thanks for teaching me a new word, I've never heard 'boffin'
>> before!
>>
>> I think your task roughly can be reformulated as:
>>
>> I want to find the number of days from the most recent day of a record
>> without any record for the preceeding day to the most recent day for a
>> particular item, and add 1 to the result.
>>
>> If so, this query will get you what you're asking for, although it may
>> take
>> a while to return any result if the table is large:
>>
>> 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
>>   WHERE NOT EXISTS(SELECT * FROM TEST_TABLE TTNot
>>WHERE TTFirst.ItemNr   = TTNot.ItemNr
>>  AND TTFirst.DateUsed - 1 = TTNot.DateUsed)
>>   GROUP BY 1)
>> SELECT ItemNr, LastDate, LastDate - FirstDate + 1 as ConsecutiveDays FROM
>> TMP
>>
>> So, what originally seems simple but grows complex once thinking about how
>> to solve it, happens to have a rather simple solution.
>>
>> HTH,
>> Set
>>
>>
>> 
>>
>> 
>>
>> ++
>>
>> Visit http://www.firebirdsql.org and click the Documentation item on the
>> main (top) menu.  Try FAQ and other links from the left-side menu there.
>>
>> Also search the

Re: [firebird-support] Cursor not Executing Properly

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
from CLASSIC_UPGRADE CU
join CONTRACT C on C.CONTRACTNO
= CU.CONTRACTNO
where C.CONTRACTSTATUS = 'A'
  and CU.INSERTDATE >
'09/01/2016'
  and CU.INSERTDATE <
'09/10/2016'
  and (CU.STATUS in
('authorized', 'Authorized')
  and C.CONVERT2YRDATE IS NULL
);
begin
  open cur_list_of_contracts;
  fetch cur_list_of_contracts into contractno;
  while (ROW_COUNT > 0) do
  begin
UPDATE CONTRACT C
SET CONVERT2YRDATE = (SELECT CU.INSERTDATE From CLASSIC_UPGRADE CU
where CU.CONTRACTNO = :contractno)
where C.CONTRACTNO = :contractno;
if (ROW_COUNT = 0) then leave;
fetch cur_list_of_contracts into contractno;
  end
  close cur_list_of_contracts;
end;

HTH,
Set

2017-02-17 10:04 GMT+01:00 Vishal Tiwari vishuals...@yahoo.co.in
[firebird-support] :

>
>
> Hi All,
>
> I have created the below cursor on Firbird DB (version 2.5). The update
> statement in below cursor is not updating value.
>
> *execute block*
> *as   *
> *declare contractno CHAR(20);*
> *declare cur_list_of_contracts cursor for (Select CU.CONTRACTNO From
> CLASSIC_UPGRADE CU, CONTRACT C where C.CONTRACTNO = CU.CONTRACTNO AND
> C.CONTRACTSTATUS = 'A' AND CU.INSERTDATE > '09/01/2016' AND CU.INSERTDATE <
> '09/10/2016' AND (CU.status = 'authorized' or CU.status = 'Authorized') AND
> C.CONVERT2YRDATE IS NULL  );*
>
> *begin*
> *open cur_list_of_contracts;*
> *  while (ROW_COUNT > 0) do*
> *  begin*
> *fetch cur_list_of_contracts into contractno;*
> *UPDATE CONTRACT C*
> *SET CONVERT2YRDATE = (SELECT CU.INSERTDATE From CLASSIC_UPGRADE CU
> where CU.CONTRACTNO = :contractno)*
> *where C.CONTRACTNO = :contractno;*
>
> *if (ROW_COUNT = 0) then leave;*
> *suspend;*
> *  end*
> *  close cur_list_of_contracts;*
> *end;*
>
>
> The block is getting executed without any errors.
>
> But, the output is not as expected. The records in the table are not
> getting updated.
>
>
> With Best Regards.
>
> Vishal
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 means?
>
> Greetings.
>
> Walter.
>
>
> On Wed, Feb 15, 2017 at 6:31 PM, setysvar setys...@gmail.com
> [firebird-support]  wrote:
>
>>
>>
>> Den 15.02.2017 17:07, skrev 'Walter R. Ojeda Valiente'
>> sistemas2000profesio...@gmail.com [firebird-support]:
>>
>> Hello everybody
>>
>> If I connect to a database "Database1" using ISQL, I can write something
>> like:
>>
>> OUTPUT MyFile.DAT;
>> SELECT * FROM MyTable;
>> OUTPUT;
>>
>> And all the rows of "MyTable" will go to the text file "MyFile.DAT".
>> That's ok and works fine.
>> But now, I want to connect to "Database2", which also have a table called
>> "MyTable" and with the same structure.
>> After that, I want to insert into "MyTable" (of "Database2") the rows
>> contained in "MyFile.DAT"
>> How can I do such thing without a lot of effort writing an INSERT command
>> in each line of "MyFile.DAT"?
>>
>> Greetings.
>> Walter.
>>
>>
>> Hi Walter. I'm not directly answering your question (mainly due to not
>> using isql myself, so I don't know the answer), but I can think of two
>> alternative ways.
>>
>> (1) (the more standard answer) Make sure your output file is in a fixed
>> length format, then create an external table for this file, transfer the
>> data using INSERT INTO "MyTable" SELECT * FROM
>>  and then finally drop the external table.
>>
>> (2) Use qli rather than isql to transfer data directly using something
>> similar to this recipe:
>> https://www.ibphoenix.com/resources/documents/how_to/doc_42
>> qli is an ancient part of InterBase, probably used before SQL was
>> invented, you can find it in the same directory as isql.
>>
>> I've only used qli once many years ago (probably on Fb 0.9.4 or 1.5), but
>> it worked nicely when I needed it. Though I don't think qli has been
>> updated for the last few Firebird versions, so maybe it doesn't work
>> anymore. And you may have a hard time finding information about it beyond
>> the document referred to above. Though I suppose:
>>
>> ready "Database1" as src;
>> ready "Database2" as trg;
>> trg."MyTable" = src."MyTable";
>>
>> doesn't need much explanation and should be simple for you to try on a
>> test database. I expect it to replace the target rather than add to it
>> (though I don't know), but the one time I used qli, I think I was
>> transferring to an empty table.
>>
>> HTH,
>> Set
>>
>>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 as 15.299532 in TableA and 15.3000135 in
TableB. Since these two numbers aren't identical, they don't match your
JOIN criteria.

Either change the type to a fixed point datatype like NUMERIC or DECIMAL
(I'm uncertain whether or not that will work on Dialect 1 databases) or
join on RANGE, e.g.
JOIN TABLE2 B ON B.URN BETWEEN A.LINKURN - 0.001 AND A.LINKURN +
0.001

If you choose the latter, a side effect is that it forces the optimizer to
have A before B in the plan. I think you can avoid this by also doing it
the other way around, i.e.

JOIN TABLE2 B ON B.URN BETWEEN A.LINKURN - 0.001 AND A.LINKURN +
0.001 AND A.LINKURN BETWEEN B.URN - 0.001 AND B.URN + 0.001

HTH,
Set

2016-12-12 10:11 GMT+01:00 russ...@smxi.com [firebird-support] <
firebird-support@yahoogroups.com>:

>
>
> There are no NULL values in either of the columns (that was my first
> thought). The table structure is
>
> TABLEA
> URN: VARCHAR(12)
> LINKURN: VARCHAR(12) [Links to TABLEB.ID]
> AMOUNT: DOUBLE PRECISION
>
> TABLEB
> URN: VARCHAR(12)
> VALUE: DOUBLE PRECISION
>
> so the query is
> SELECT A.AMOUNT, B.VALUE, A.AMOUNT/B.VALUE
> FROM TABLE1 A
> JOIN TABLE2 B ON A.LINKURN = B.URN
>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
average in case of two median values).

execute block returns (mode integer) as
declare variable occurences integer;
declare variable dummy integer;
begin
  select , count(*)
  from 
  group by 1
  order by 2 desc
  rows 1
  into :dummy, :occurences;
  for select 
  from 
  group by 1
  having count(*) = :occurences
  into :mode do
suspend;
end

execute block returns (median decimal(9, 1)) as
declare variable occurences integer;
declare variable RowsFrom integer;
declare variable RowsTo   integer;
begin
  select count(*)
  from 
  into :occurences;
  RowsFrom = ((:occurences-1)/2)+1;
  RowsTo   = (:Occurences/2)+1;
  with tmp(tmp) as
  (select cast( as decimal(9, 1))
   from 
   rows :RowsFrom to :RowsTo)
  select sum(tmp)/(:RowsTo-:RowsFrom+1)
  from tmp
  into :Median;
  suspend;
end

select max()-min() as Range
from 

HTH,
Set

2016-12-01 4:41 GMT+01:00 Roberto Carlos rc.1...@bol.com.br
[firebird-support] :

> Basics of Statistics - Mathematics: Mean (Average), Median, Mode and Range
>
> I want to calculate Mean (Average), Median, Mode and Range in Firebird 2.5
> and 3.0.
>
> I know that Firebird internal function for Mean (Average) is AVG(), but
> what are the internal functions for Median, Mode and Range in Firebird 2.5
> and 3.0?
>
> If there are not such functions, how can I calculate them using Firebird?
>
> Thanks for all and any help.
>
>
> [Non-text portions of this message have been removed]
>
>
>
> 
> Posted by: Roberto Carlos 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at http://www.ibphoenix.com/
> resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

 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
perspective, it is possibly a bug in your system though, you should try to
avoid congestion. As Karol says, one way to do this, is to delete a few
rows frequently rather than many rows simultaneously, e.g. delete records
older than 183 days every hour (if the field is indexed, but the plan
reveals that it is not used, you could try to delete records that are
between 183 and 184 days old) and not once a month (that could require
having a timestamp and not just date field).

HTH,
Set

Re: Re: [firebird-support] Optimizer request

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

Sometimes I use (a mixture of selectable and executable) EXECUTE BLOCK (you
don't need changed_records, I just tend to prefer knowing whether I changed
100 or 2 million rows):

execute block returns (changed_records integer) as
declare variable OID integer;
begin
  changed_records = 0;
  for select distinct o.id
  from orders o
  join partners p on o.partid = p.partid
  where o.delivery_date is null
and p.country='Spain'
  into :oid do
  begin
update orders
set something = 'something other'
where id = :oid;
changed_records = changed_records + row_count;
  end
  suspend;
end

Of course, you need to commit afterwards...

HTH,
Set

2016-10-19 10:46 GMT+02:00 liviuslivius liviusliv...@poczta.onet.pl
[firebird-support] <firebird-support@yahoogroups.com>:

>
>
> hi,
>
> maybe MERGE is your answer
>
> regards,
> Karol Bieniaszewski
>
>
>  Oryginalna wiadomość 
> Od: "ehmmm.fireb...@seznam.cz [firebird-support]" <
> firebird-support@yahoogroups.com>
> Data: 19.10.2016 09:34 (GMT+01:00)
> Do: firebird-support@yahoogroups.com
> Temat: Re: [firebird-support] Optimizer request
>
>
>
> I'm continuing in old conversation because I have similar question.
>
>
> How to avoid using IN(subselect) in UPDATE?
>
>
> Theoretical example:
>
>
> update orders o
> set o.something = 'something other'
> where o.delivery_date is null
>   and o.partid 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 again and again.
> Am I right?
>
> Would using EXISTS() help?
>
>
> E.
>
>
> -- Původní zpráva --
> Od: Svein Erling Tysvær setys...@gmail.com [firebird-support] <
> firebird-support@yahoogroups.com>
> Komu: firebird-support@yahoogroups.com
> Datum: 9. 9. 2016 17:10:21
> Předmět: 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] <firebird-support@yahoogroups.com>:
>
>
>
> I tried query with subquery in where clause and found big issue for this
> type of subquery.
>
> for example:
>
> select * from orders where orders.partid in (select partners.partid from
> partners where partners.country = ‘Spain’)
>
> Perfomance Analysis returns me this
>
> partners 687660 non index reads
> orders  28657 index reads
>
> If you analyze this result you’ll find that there is 687659 unnecessary
> non index reads. If developer of optimizer accept that all queries on the
> left side of where clouse has priority (and there is no way to be opposite)
> than we have big improvement in optimization.
>
> Best regards,
>
> Djordje Radovanovic
>
>
>
>
>
> =
>
>
>
> 
>

Re: [firebird-support] Optimizer request

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 tried query with subquery in where clause and found big issue for this
> type of subquery.
>
> for example:
>
> select * from orders where orders.partid in (select partners.partid from
> partners where partners.country = ‘Spain’)
>
> Perfomance Analysis returns me this
>
> partners 687660 non index reads
> orders  28657 index reads
>
> If you analyze this result you’ll find that there is 687659 unnecessary
> non index reads. If developer of optimizer accept that all queries on the
> left side of where clouse has priority (and there is no way to be opposite)
> than we have big improvement in optimization.
>
> Best regards,
>
> Djordje Radovanovic
>
>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 is exact. Generally speaking, people are advised
to use DOUBLE PRECISION for things you measure (e.g. meters) and NUMERIC
for things you count. Don't compare for equality between floating point
numbers, but ranges (e.g. COALESCE(TABLE1.QTY, 0.285) BETWEEN
0.2849 and 0.2851, the simpler COALESCE(TABLE1.QTY, 0.285)
= 0.285 can return false )

>In Firebird's documentation about ROUND function, there is a an example
with
>"ROUND(123.654, 1) returns 123.700", showing that 5 is rounded to the
upper digit as standard rounding works not as Banker's rounding.

No, bankers rounding only comes into effect when things are exactly 5, not
when they're 54.

HTH,
Set

2016-06-08 11:01 GMT+02:00 Dmitry Yemanov dim...@users.sourceforge.net
[firebird-support] :

> 08.06.2016 11:36, m.djo...@yahoo.com wrote:
> >
> > First  I thought it's because of the results' type after the different
> > operations, but even in cases when the result before ROUND is a DOUBLE
> > PRECISION (according IBExpert) the round gives different results if the
> > NULL in the COALESCE function comes from a table's field or if I write
> > it manually.
>
> COALESCE derives the resulting datatype based on input arguments:
>
>- COALESCE(NULL, 0.285) returns numeric
>- COALESCE(TABLE1.QTY, 0.285) returns double precision
>
> In the second case, 0.285 gets implicitly converted to double precision
> and may cause insignificant digits appearing in intermediate calculations:
>
> coalesce(TABLE1.QTY, 0.285) - coalesce(null, 0.285)
>
> SUBTRACT
> ===
>0.000
>
> coalesce(col, 0.285) * 12.5 - coalesce(null, 0.285) * 12.5
>
> SUBTRACT
> ===
>   -4.440892098500626e-16
>
> This difference obviously affects the ROUND results.
>
>
> Dmitry
>
>
>
>
> 
> Posted by: Dmitry Yemanov 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

Thanks Dmitry!

I simplified the query to

WITH TMP( TJENESTEENHETRESHID, TJENESTEENHETLOKAL,  IMPORT_DATO ) AS
( SELECT  TJENESTEENHETRESHID, TJENESTEENHETLOKAL, MAX( IMPORT_DATO )
 FROM NPR
 WHERE TJENESTEENHETRESHID > 0
   AND TJENESTEENHETLOKAL  > ''
 GROUP BY 1, 2 )
select t1.tjenesteenhetreshid, t1.tjenesteenhetlokal
from tmp t1
left join tmp t2
   on t1.tjenesteenhetreshid = t2.tjenesteenhetreshid
  and t1.tjenesteenhetlokal <> t2.tjenesteenhetlokal
  and t1.IMPORT_DATO <= t2.IMPORT_DATO
where t2.tjenesteenhetreshid is null

But still, I had to terminate this one also after two hours.

None of the fields in this particular query are indexed, they're rarely
used in the WHERE clause and have poor selectivity (2K different values in
20M records, it wouldn't surprise me if the most common value may cover 1%
of the 20M).

So my question was more: "Why not execute the CTE (against the large table)
once, and do the further processing against the (very small) result of the
CTE, rather than expand the CTE twice (and thus increase the execution time
from 16 minutes to about four weeks)?"

I think I've found a completely different way to solve 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 Erling Tysvær wrote:
> > 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 ) AS
> > ( SELECT TJENESTEENHETRESHID, TJENESTEENHETLOKAL
> >FROM TMP T
> >WHERE NOT EXISTS( SELECT * FROM TMP T2
> >  WHERE T.TJENESTEENHETRESHID = T2.TJENESTEENHETRESHID
> >AND T.IMPORT_DATO < T2.IMPORT_DATO ) )
> > SELECT TJENESTEENHETRESHID, LIST( TJENESTEENHETLOKAL ) TJENESTEENHETLOKAL
> > FROM TMP2
> > GROUP BY 1
> > HAVING COUNT( DISTINCT TJENESTEENHETLOKAL ) = 1
> >
> > generates this plan:
> > PLAN SORT ((TMP2 T2 NPR NATURAL))
> > PLAN SORT ((TMP2 T NPR NATURAL))
> >
> > NPR is a medium sized table (I'm guessing 20 million rows), but the
> > result of the first CTE is only 2200 rows. None of the fields in this
> > particular query are indexed. I tried to run the query, but decided to
> > terminate it after about 2 hours 40 minutes.
> > If I run the first CTE separately
> >
> > ( SELECT  TJENESTEENHETRESHID, TJENESTEENHETLOKAL, MAX( IMPORT_DATO )
> >   FROM NPR
> >   WHERE TJENESTEENHETRESHID > 0
> > AND TJENESTEENHETLOKAL  > ''
> >   GROUP BY 1, 2 )
> >
> > it takes just over 16 minutes. Putting the result of this CTE in a
> > temporary table and running the rest of the query against this temporary
> > table only takes a couple of seconds (reducing the 2200 rows to 1600).
> > The way I normally read plans seems to fail me this time - I don't quite
> > understand why NPR is mentioned twice in the plan (I assume it has to do
> > with my "two-level" GROUP BYs). Can anyone explain why? Or better: Does
> > anyone understand why the query takes so long to execute?
>
> It's slow because T2 is accessed naturally (i.e. a fullscan) for the
> every record in T. NOT EXISTS is kinda nested loop join (anti-join,
> actually), it's good only if the subquery is indexed. Otherwise, it
> takes 16 minutes + (2200 * 16 minutes) to execute.
>
> The question is why T2 does not use an index inside CTE. Condition
> T.IMPORT_DATO < T2.IMPORT_DATO cannot use an index because it's
> evaluated against an aggregate: IMPORT_DATO is actually
> MAX(NPR.IMPORT_DATO). However, condition T.TJENESTEENHETRESHID =
> T2.TJENESTEENHETRESHID should use an index on NPR.TJENESTEENHETRESHID,
> if available. But I doubt it exists, otherwise the local condition
> TJENESTEENHETRESHID > 0 would also be indexed in this case.
>
> So the only way to run this query fast is to ensure
> NPR.TJENESTEENHETRESHID is indexed (and it's the first segment, if the
> index is compound).
>
>
> Dmitry
>
>
>
>
> 
> Posted by: Dmitry Yemanov <dim...@users.sourceforge.net>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 ) AS
( SELECT TJENESTEENHETRESHID, TJENESTEENHETLOKAL
  FROM TMP T
  WHERE NOT EXISTS( SELECT * FROM TMP T2
WHERE T.TJENESTEENHETRESHID = T2.TJENESTEENHETRESHID
  AND T.IMPORT_DATO < T2.IMPORT_DATO ) )
SELECT TJENESTEENHETRESHID, LIST( TJENESTEENHETLOKAL ) TJENESTEENHETLOKAL
FROM TMP2
GROUP BY 1
HAVING COUNT( DISTINCT TJENESTEENHETLOKAL ) = 1

generates this plan:
PLAN SORT ((TMP2 T2 NPR NATURAL))
PLAN SORT ((TMP2 T NPR NATURAL))

NPR is a medium sized table (I'm guessing 20 million rows), but the result
of the first CTE is only 2200 rows. None of the fields in this particular
query are indexed. I tried to run the query, but decided to terminate it
after about 2 hours 40 minutes.

If I run the first CTE separately

( SELECT  TJENESTEENHETRESHID, TJENESTEENHETLOKAL, MAX( IMPORT_DATO )
 FROM NPR
 WHERE TJENESTEENHETRESHID > 0
   AND TJENESTEENHETLOKAL  > ''
 GROUP BY 1, 2 )

it takes just over 16 minutes. Putting the result of this CTE in a
temporary table and running the rest of the query against this temporary
table only takes a couple of seconds (reducing the 2200 rows to 1600). The
way I normally read plans seems to fail me this time - I don't quite
understand why NPR is mentioned twice in the plan (I assume it has to do
with my "two-level" GROUP BYs). Can anyone explain why? Or better: Does
anyone understand why the query takes so long to execute?

This is on Firebird 2.5 [2.5.4.26856]

Thanks in advance for any hints,
Set

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 is).

2016-05-20 12:59 GMT+02:00 michael.vilhelm...@microcom.dk
[firebird-support] :

>
>
> Hi
>
> I have installed Firebird 3.0. I'm testing our program up against this
> one.
> Our databases are in SQL dialect 1.
>
> I have a table like this:
>
> CREATE TABLE MYTABLE
> (
>   ID  INTEGER NOT NULL,
>   NAME_   VARCHAR(30),
>   VALUEFLOAT  NUMERIC( 18, 2) DEFAULT 0,
>   VALUEINTINTEGER DEFAULT 0,
>   AAR INTEGER DEFAULT 0,
>   MDR INTEGER DEFAULT 0,
>  CONSTRAINT PK_MYTABLE PRIMARY KEY (ID)
> );
>
>
> If I do this SQL, I get an error:
>
> SELECT
>   MYTABLE.NAME_,
>   Sum(MYTABLE.VALUEFLOAT) AS SumFloat,
>   Sum(MYTABLE.VALUEINT) AS SumInteger
> FROM
>   MYTABLE
> WHERE
>   ((MYTABLE.AAR * 100) + MYTABLE.MDR) >= ((:PAar * 100) + :PMDR)
> GROUP BY
>   NAME_
>
>
> ERROR:
> Dynamic SQL Error expression evaluation not supported Invalid data type
> for multiplication in dialect 1
>
> It works if I replace the parameters with numbers like this:
>
>
> SELECT
>   MYTABLE.NAME_,
>   Sum(MYTABLE.VALUEFLOAT) AS SumFloat,
>   Sum(MYTABLE.VALUEINT) AS SumInteger
> FROM
>   MYTABLE
> WHERE
>   ((MYTABLE.AAR * 100) + MYTABLE.MDR) >= ((2016 * 100) + 5)
> GROUP BY
>   NAME_
>
>
>
>
> So my question is, if there is a simple workaround to avoid this?
>
> I have tried googling something on this, but havn't found anything so far.
>
> I have read the release notes, but either cant find anything on this or
> I'm not looking good enough.
>
>
> Michael
>
>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
this new proxy column in your WHERE clause. If it is a tiny table or if you
always have other selective and indexed criteria, then doing things only in
the WHERE clause is fine.

Set

2016-02-12 2:57 GMT+01:00 ho...@agencybusys.com [firebird-support] <
firebird-support@yahoogroups.com>:

>
>
> Users can enter values with dashes (-) in random locations. I don't know
> where they might put them. I need to select a record using a where clause
> that doesn't have those dashes. For example, the user might record an
> insurance policy number as 12-345678-01 or 123-456-789-01. All I will know
> are the number, and not where they put the dashes. Is there a way to
> suppress the dashes in the Select Clause, or the Where Clause? This is
> being done in a Delphi 2007 program using IBDAC.
>
>
> It would be nice if I could do something like ... SELECT * WHERE
> HIDE('-',UsersField) = 'MyValue'
>
> Or the equivalent in the Select statement.
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 of deleting and inserting into a summation
table in a trigger on a detail table is something that works well only with
maximum one concurrent user per row of the summation table. It simply
contradicts concurrency!

What I'd rather recommend, is that the trigger always inserts into the
summation table, but never deletes from it. If you insert a new record into
twarehouse, the trigger should insert +1 into twarehouse_sum, if you delete
a record from twqarehouse, the trigger should insert -1 into twarehouse_sum
(change +1/-1 appropriately if the summation table contains sums rather
than counts). Then you should have a separate process that at set times
(e.g. once every hour, day or week), does something like:

for select DistinctField, sum(MySummationField) from twarehouse_sum group
by 1
into :DistinctField, :MySum do
begin
  delete from twarehouse_sum where DistinctField = :DistinctField;
  insert into twarehouse_sum(DistinctField, SummationField)
  values(:DistinctField, :MySum);
end

That would require you to generally change all your

  select DistinctField, MySummationField
  from twarehouse_sum

to

  select DistinctField, sum(MySummationField)
  from twarehouse_sum
  group by 1

whenever you want to find the sum, but it is scalable (i.e. a lot better
for concurrency) and avoids the problem you've observed.

Set

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
  AND P.SOC_SEC_NO = P2.SOC_SEC_NO)
ORDER BY P.SOC_SEC_NO

(well, you can do it without an index as well, but then it will be slow if
PERSON contains millions of records)

If you want to delete the duplicates, you can e.g. do (this will delete all
duplicates, except the one with the lowest PERSON_ID):

DELETE FROM PERSON P
WHERE EXISTS(SELECT * FROM PERSON P2
 WHERE P.PERSON_ID > P2.PERSON_ID
  AND P.SOC_SEC_NO = P2.SOC_SEC_NO)


2016-02-04 20:09 GMT+01:00 'stwizard' stwiz...@att.net [firebird-support] <
firebird-support@yahoogroups.com>:

>
>
> Greetings All,
>
>
>
> How do I form a SQL Select statement that will return which records in my
> PERSON table have duplicate SOC_SEC_NO.
>
>
>
> In other words I need a list of persons where the social security number
> appears in the database more than once.  Some SOC_SEC_NO may be null which
> I do not care about.
>
>
>
> PERSON:
>
> PERSON_ID
>
> SOC_SEC_NO
>
>
>
> Any help appreciated,
>
> Mike
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
[firebird-support] :

> On Wed, 27 Jan 2016 23:01:50 +0100, "setysvar setys...@gmail.com
> [firebird-support]"  wrote:
>
> > [..] On the other hand, there are cases
> > where EXECUTE BLOCK can help performance (note, I speak for 2.5, I know
> > nothing about Firebird 3):
> >
> > UPDATE  h
> > SET  = (SELECT  FROM  t WHERE
> > h. = t.)
> >
> > is much slower than
> >
> > EXECUTE BLOCK AS
> > Declare variable a integer;
> > Declare variable b integer;
> > BEGIN
> >FOR SELECT DISTINCT , 
> >FROM 
> >INTO :a, :b do
> >  UPDATE HugeTable
> > SET  = :b
> > WHERE  = :a;
> > END
>
> Out of curiosity: have you also compared this with using MERGE?
>
> Mark
>
>
> 
> Posted by: Mark Rotteveel 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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
since you probably want TBESTPOS anyway, it is not important unless you
decide to add another table). Sometimes (as part of optimizing a query),
that can be desirable, but only after finding the optimizer to come up with
a bad plan. Hence, I'd recommend that you change your query to (from the
FROM part):

FROM TKUNDEN
INNER JOIN TBESTELLUNGEN ON TKUNDEN.KUNDENNR = TBESTELLUNGEN.LIEFERANTNR
INNER JOIN TBESTPOS ON TBESTPOS.NR = TBESTELLUNGEN.NR AND TBESTPOS.JAHR =
TBESTELLUNGEN.JAHR
INNER JOIN TTEILE ON TTEILE.TEILENR = TBESTPOS.TEILENR
WHERE TBESTPOS.ABGESCHLOSSEN=-1
   AND TBESTPOS.TATSLT BETWEEN [forms]![fbestelleingang].[sa] AND
[forms]![fbestelleingang].[sb]
   AND UCase([tteile].[teilenr]) Like
nz(UCase([Formulare]![fbestelleingang]![st]) & "*","*")
   AND TKUNDEN.KUNDENNR Like nz([Formulare]![fbestelleingang]![sl],"*")
ORDER BY TBESTPOS.LIEFERTERMIN DESC , TBESTPOS.LIEFERZEIT DESC;

The reason I've removed lots of parenthesis, is simply because their main
purpose in a query containing only ANDs, is to confuse the reader of the
query. I don't understand nz([Formulare]![fbestelleingang]![sl],"*") and
similar parts of the query, I take it that it is Access transforming this
into some kind of parameter or constant. It can also be desireable to
change LIKE to STARTING if appropriate, since STARTING can use an index.

HTH,
Set

2016-01-20 7:07 GMT+01:00 'checkmail' check_m...@satron.de
[firebird-support] :

>
>
> Hello and good morning,
>
>
>
> the situation, firebird classic server 2.1 before, firebird classic server
> 2.5 (newest) after. The frontend is Microsoft Access, the tables odbc
> linked. Now, after the migration I open a Formular in Access, everything is
> fast, in less then one second the Data were displayed. But if I open the
> Formular the second time, I must wait 20 seconds. All Fields are indexed.
>
>
>
> The sql-code in Access
>
> SELECT TBESTELLUNGEN.JAHR, TBESTELLUNGEN.NR, TBESTPOS.LIEFERTERMIN,
> TBESTPOS.LIEFERZEIT, TBESTPOS.GELIEFERT, TTEILE.TEILENR,
> TTEILE.BEZEICHNUNG, TKUNDEN.FIRMA, TBESTELLUNGEN.GELIEFERT AS komplett,
> TBESTPOS.ANZAHL, TBESTPOS.GEWOGEN, TBESTPOS.BESTAETIGT,
> TBESTPOS.ABGESCHLOSSEN, TBESTPOS.TATSLT,
> IIf([einheitnr]=6,[tbestpos].[geliefert],[tbestpos].[geliefert]*[tteile].[gewicht])
> AS gewicht, UCase([tteile].[teilenr]) AS TNRG
>
> FROM TKUNDEN INNER JOIN (TTEILE INNER JOIN (TBESTPOS INNER JOIN
> TBESTELLUNGEN ON (TBESTPOS.NR = TBESTELLUNGEN.NR) AND (TBESTPOS.JAHR =
> TBESTELLUNGEN.JAHR)) ON TTEILE.TEILENR = TBESTPOS.TEILENR) ON
> TKUNDEN.KUNDENNR = TBESTELLUNGEN.LIEFERANTNR
>
> WHERE (((TBESTPOS.ABGESCHLOSSEN)=-1) AND
> ((TBESTPOS.TATSLT)>=[forms]![fbestelleingang].[sa] And
> (TBESTPOS.TATSLT)<=[forms]![fbestelleingang].[sb]) AND
> ((UCase([tteile].[teilenr])) Like
> nz(UCase([Formulare]![fbestelleingang]![st]) & "*","*")) AND
> ((TKUNDEN.KUNDENNR) Like nz([Formulare]![fbestelleingang]![sl],"*")))
>
> ORDER BY TBESTPOS.LIEFERTERMIN DESC , TBESTPOS.LIEFERZEIT DESC;
>
>
>
> It works fine with fb 2.1, but now.. What has been changed? The last odbc
> driver is installed
>
>
>
> Best regards
>
>
>
> Olaf
>
>
>
>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 certain UNION (as opposed to UNION ALL) should remove duplicates and
the same with DISTINCT. To get the latter query to return one row, GROUP BY
can be added, I don't know how to make the first query return only one row.

Is this a Firebird error, and if so, is it a new one or one fixed in newer
versions?

Set

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

Thanks Dmitry, learning that the problem is with blobs and not particularly
LIST, DISTINCT or UNION helped me realise that the simple workaround is to
use cast(list() as varchar()) whenever I want to return
distinct values of blobs.

Set

2016-01-11 14:31 GMT+01:00 Dmitry Yemanov dim...@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
> > 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
>
> LIST returns a blob. DISTINCT doesn't work with blobs properly, it
> operates with blob ids (that are surely distinct) instead. See:
>
> http://tracker.firebirdsql.org/browse/CORE-1345
> http://tracker.firebirdsql.org/browse/CORE-1530
>
>
> Dmitry
>
>
>
>
> 
> Posted by: Dmitry Yemanov <dim...@users.sourceforge.net>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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] :

>
>
> On Tue, Dec 22, 2015 at 9:40 AM, Luigi Siciliano luigi...@tiscalinet.it
> [firebird-support]  wrote:
>
>>
>>I must assign a serial number, without hole, in a column of a fiscal
>> document.  I must assign the number only when I know if the document is
>> complete
>> and I think the right moment is on a Before Insert Trigger for the table.
>>
>
> Yes that's a good place, but you've got to be very careful.
> Generators/Sequences won't
> work because they're deliberately non-transactional.  Once you take one
> its gone and if
> your operation fails, you'll have a hole.
>
>>
>> Is right or the insertion can fail? If not right, when I must assign the
>> number to be sure of not have a hole in numeration?
>>
>
> One way to get numbers without holes is to create a table with one field
> that contains
> the seed for  your numbers.  In your before insert trigger update that
> field adding one to it,
> then read to get the new value.  Unfortunately, if someone else has
> inserted a record
> concurrently, your transaction will wait then get an error and you'll need
> to re-run the
> whole thing.
>
>  Check the FAQ's at FirebirdSQL.org for other ways of handling this
> problem.
>
>
> Good luck,
>
> Ann
>
>
>>
>>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 things
down if searching for women (99% of breast cancer cases are women), whereas
it could be fairly useful for men (the remaining 1%).

Let's say you have 50 different values for usr, each of them equally much
used in rep$log. Usr = 'REPL' will then match 2% of the records, and that
means that using the index can eliminate the remaining 98% and using an
index is sensible. Usr <> 'REPL' on the other hand will match 98% of the
records and using the index will only eliminate 2%. Scanning the entire
table using NATURAL will always be quicker than using an index in such
cases.

I don't know where the threshold for when using an index is sensible or
not, but am pretty certain it is somewhere between those 2% and 98%.

HTH,
Set

2015-10-23 8:40 GMT+02:00 drci...@yahoo.com [firebird-support] <
firebird-support@yahoogroups.com>:

>
>
> Hello Karol,
> I don't think I understand you fully. Indexed reads a hudreds of times
> faster than non indexed. And the table will have millions of records, so I
> do need them.
> "then this is cheaper to scan table (500 records) then use 400 indexed
> reads"
> What do you mean scan table? How do I do that? Like a stored procedure
> with for select? I don't understand, please explain.
>
>
>
> 
>

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

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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) From tmp t2 where
t1.Reference <= t2.Reference) RunningTotal
>From tmp t1
Order by 1

Though sometimes this can be time consuming,
Set


2015-10-23 8:40 GMT+02:00 Maya Opperman m...@omniaccounts.co.za
[firebird-support] :

>
>
> Hi,
>
>
>
> I’d like to add a running total to my result set. For example:
>
>
>
> Table: Invoices
>
>
>
> Reference   Due
>
> Invoice1   50.00
>
> Invoice2   30.00
>
> Invoice3   20.00
>
>
>
> I’m guessing SQL would be something like:
>
> Select
>
>   Reference,
>
> Due,
>
> Sum_Total(Due) as Balance
>
> From Invoices
>
>
>
> Desired Result:
>
> Reference   DueBalance
>
> Invoice1   50.00 50.00
>
> Invoice2   30.00 80.00
>
> Invoice3   20.00 100.00
>
>
>
> I know I can do this quite easily from within a selectable stored
> procedure, but the problem there is the running total won’t be correct if a
> different sort order is specified, which happens often in my real world
> application.
>
>
>
> Is it possible to do this using just a simple SQL statement?
>
>
>
> Thanks
>
> Maya
>
>
>
>
> 
>

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

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

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.html or 
http://www.firebirdsql.org/manual/gfix-pagespace.html for more information.

Set

-Original Message-
From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: 8. juli 2015 11:48
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Gbaked and restored database larger than the 
original?

Hi,

Just for my cursiosity and for a better understanding of Firebird: what can be 
a possible reason for a gbaked and restored (to a different file) database 
being _larger_ than the original one?
I've got a db of size 104521728 bytes. I do gbak -B -T database.fdb 
database.gbk, then gbak -C database.gbk newdatabase.fdb. The size of 
newdatabase.fdb is 104751104 (~ 200kB more).
I'm the only one messing with these databases, the platform is Linux x64, 
Firebird 2.5.2, all operations performed on the same machine, in the same file 
system. Before the backup-restore, a quite large chunk of metadata was 
added/altered in the original database (procedures, fields), which is the only 
clue I can think of at the moment (adding a default value to a field which had 
none?).
It's quite obvious why a restored database might be smaller than the original 
one (no garbage), but the other way around is a bit puzzling.
Both databases seem to work fine, so I'm in no trouble, but my ignorance in 
this matter bothers me nonetheless ;) I'd be grateful for any plausible 
explanations.

thanks in advance and best regards
Tomasz



Posted by: Tomasz Tyrakowski t.tyrakow...@sol-system.pl


++

Visit http://www.firebirdsql.org and click the Documentation item on the main 
(top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

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

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

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 using them.



Probably having a real field, maintained with a trigger is better? I don't 
like having redundant

data in my database, but I'm note sure what is the better option: waste a bit 
of space or

(probably) decrease performance??

?
I've heard (don't remember who said it, and since I don't use calculated fields 
much myself, I cannot tell how reliable this information is) that calculated 
fields ought only to refer to the current record, i.e. not a different table or 
different record in the same table. So, as long as qty, price and tax all are 
part of the same record as your COMPUTED BY value, this ought to be fine. If 
they are stored in different lookup tables, then a view or a trigger maintained 
field may be preferrable.

Hopefully, others will verify this or tell that I'm wrong.

Set

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

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

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 want all rows returned:

Select ID_column
From Table t1
Where not exists(select * from Table t2 where t1.Sort  t2.Sort)

Alternatively, you could use

With tmp(Sort) as
(select min(Sort) from table)
Select t.ID_Column
From tmp
Join table t on tmp.Sort = t.Sort

Set

Re: [firebird-support] Soc Sec No comparison using Firebird

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

Greetings All,

I would like to pass into a stored procedure two social security numbers for 
comparison and have the result tell me if one character has changed, 
or if two characters were switched or if it does not compare at all.

Has anyone done anything like this they could share?  Or is it even possible?

Never done anything similar before, but strictly speaking, the following would 
answer your question:

execute block (ss1 varchar(20) = :ss1, ss2 varchar(20) = :ss2) returns 
(comparison varchar(50)) as
declare variable maxlen integer;
declare variable curpos integer;
declare variable ss1a char(1);
declare variable ss1b char(1);
declare variable ss2a char(1);
declare variable ss2b char(1);
begin
  Comparison = '';
  maxlen = character_length(ss1);
  if (maxlen  character_length(ss2)) then
maxlen = character_length(ss2);
  if (ss1 = ss2) then
comparison = 'Equal';
  if (comparison = '' and (ss1 starting ss2 or ss2 starting ss1)) then 
comparison = 'Substring';
  if (comparison = '') then
  begin
curpos = 1;
while (curpos = maxlen) do
begin
  ss1a = substring(ss1 from curpos   for 1);
  ss1b = substring(ss1 from curpos+1 for 1);
  ss2a = substring(ss2 from curpos   for 1);
  ss2b = substring(ss2 from curpos+1 for 1);
  if (ss1a is distinct from ss2a) then
  begin
if (comparison  '') then
  comparison = 'Differ more than one character';
if (comparison='') then
begin
  if (ss1a = ss2b and ss1b = ss2a) then
  begin
comparison = 'swapped';
curpos = curpos+1;
  end
  if (comparison='') then
comparison = 'One character differs';
end
  end
  curpos = curpos+1;
end
  end
  suspend;
end

I say strictly speaking, because whereas this would catch the difference 
between 'Hello' and 'Helol' or 'Hello' and 'Hel1o', it wouldn't catch 
additional or missing characters like 'Hello' vs 'Helo'. Should be fixable, but 
I'll leave that task for you (I'd expect you to need curpos1 and curpos2).

Having said all this, it wouldn't surprise me if there existed a UDF that did 
something similar to my execute block, I simply don't know since I've never 
needed one.

HTH,
Set

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

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

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(ca.name), j.jdate, 
j.source, i.job_number, j.source2, j.sourceid, s.name||' '||s.surname
 from journal j
 join invoices i   on j.source = i.inv_nr
 left join customer c  on j.cid= c.cid
 left join cashcustomer ca on j.source = ca.invnr
  and i.job_number = ca.jobnr
 left join staff s on j.serviceadvisOR = s.staff_id
 where j.source not starting 'STOCK UNIT'
   and j.jdate between '2015/06/01' AND '2015/06/22'
   and j.source not starting 'DEPOSIT'
   and j.cid  0
   and j.module  2
   and ((j.source2 not starting 'S/D'
 and j.source2 not starting 'P/D')
 or j.source2 is null)
   and j.description not starting 'SPLIT INV'
   and j.jtype in (1, 4)),
tmp2(surname, name, trxdate, source, job_number, creditnr, sourceid, 
serviceadvisOR, credVAT, VAT,invoicetotalINC, credinc, creditnote,
 invoiceINCRedo, invoiceVATRedo, cost) as
(select t.surname, t.name, t.jdate, t.source, t.job_number, t.creditnr, 
t.sourceid, t.serviceadvisOR
sum(iif(j.accnr = '5995300' and j.module in (66, 77) and t.source2 = 
j.source2 and j.jtype = 1, coalesce(j.creditamount, 0) - 
coalesce(j.debitamount, 0), 0)),
sum(iif(j.accnr = '5995300' and j.jtype = 1, coalesce(j.creditamount, 
0) - coalesce(j.debitamount, 0), 0)),
sum(iif(j.accnr = '3993100' and j.jtype = 1, j.debitamount, 0)),
sum(iif(j.accnr = '1501500' and j.jtype = 1, coalesce(j.debitamount, 
0)-coalesce(j.creditamount, 0), 0)),
sum(iif(j.accnr = '1501500' and j.jtype = 4, coalesce(j.creditamount, 
0)-coalesce(j.debitamount, 0), 0)),
sum(iif(j.accnr = '3993100' and j.jtype = 1 and j.module = 77, 
j.creditamount, 0)),
sum(iif(j.accnr = '5995300' and j.jtype = 1 and j.module = 77, 
coalesce(j.debitamount, 0)-coalesce(j.creditamount, 0), 0)),
sum(iif(j.accnr starting '6' and j.jtype = 1, coalesce(j.debitamount, 
0)-coalesce(j.creditamount, 0), 0))
from tmp1 t
join journal j on t.source  = j.source
  and t.trxdate = j.jdate
  and j.jtype  in (1, 4)
  and (j.accnr in ('1501500', '3993100', '5995300')
or j.accnr starting '6')
group by 1, 2, 3, 4, 5, 6, 7, 8)
select surname, name, trxdate, source, job_number, creditnr, sourceid, 
serviceadvisOR, credVAT, VAT, invoicetotalINC, invoicetotalINC-VAT+credvat as 
invoicetotal,
   credinc+creditnote-invoiceINCRedo+invoiceVATRedo credits, 
invoicetotalINC-VAT+credinc+creditnote-invoiceINCRedo as nett, cost,
   invoicetotalINC-VAT+credinc+creditnote-cost-invoiceINCRedo as gp,
   
invoicetotalINC-VAT+credinc+creditnote-cost-invoiceINCRedo/NULLIF(invoicetotalINC-VAT+credinc+creditnote,
 0)*100 as gppersent
from tmp
where invoiceINC-invoicevat+credvat  0
order by 1,3,4

Does this get you the right result?

By the way, I'm positively surprised if the performance improved from almost 16 
seconds to 0.16 seconds, I'd hoped for up to a 10-fold improvement, not 
100-fold. I'm sure you didn't mean 0.16 milliseconds, Firebird is simply not 
that quick.

HTH,
Set

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

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

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

This is across all the results any idea why?
 
Would you get six times as many rows in your result set with your original 
query if you didn't have the DISTINCT, Stef? Or  are any of the JOINs returning 
more than one row - e.g. are there six invoices for each entry in journal? This 
should be fairly simple to fix, but more information about the tables and how 
unique each JOIN are would be required.

Set

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

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

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 am not a SQL master but do know the basics.
I am have an issue with sub select queries and setting conditions.
Attached is the query I have a problem with,  see my comments on the last 
condition.

P.S see below is my plan I am sure the query could be done better for faster 
performance any advise would be greatly appreciated !!

Plan

PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16))
PLAN (STAFF INDEX (STAFF_IDX1))
PLAN (CUSTOMER INDEX (CUSTOMER_IDX1))
PLAN (CA INDEX (CASHCUSTOMER_IDX2, CASHCUSTOMER_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN SORT (JOIN (J INDEX (JOURNAL_IDX1, JOURNAL_IDX2, JOURNAL_IDX2), I INDEX 
(INVOICES_IDX5)))

Adapted Plan

PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX 
(JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, 
JOURNAL_IDX16)) PLAN (STAFF INDEX (STAFF_IDX1)) PLAN (CUSTOMER INDEX 
(CUSTOMER_IDX1)) PLAN (CA INDEX (CASHCUSTOMER_IDX2, CASHCUSTOMER_IDX1)) PLAN 
(JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, 
JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL 
INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, 
JOURNAL_IDX16)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16)) PLAN 
(JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, 
JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN SORT 
(JOIN (J INDEX (JOURNAL_IDX1, JOURNAL_IDX2, JOURNAL_IDX2), I INDEX 
(INVOICES_IDX5)))

Prepare time = 16ms
Execute time = 15s 828ms
Avg fetch time = 465.53 ms
Current memory = 74 375 188
Max memory = 74 391 660
Memory buffers = 8 192
Reads from disk to cache = 253
Writes from cache to disk = 0
Fetches from cache = 730 569

[Non-text portions of this message have been removed]

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

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

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 databases, 250 Mbyte the bigger one)

The application works very well, but I'm asking to the mailing list if it 
possible to have more performance considering the database side without
make a big software refactoring. Which release do you suggest? What will be 
the percentage of performances get?

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 regardless of Firebird 
version. If it is one particular query that is slow, you may be lucky with the 
optimizer working better on 2.5, but the correct way to fix things would still 
be to change the query. You may also experience the opposite when upgrading, 
i.e. that a query that works great on Fb 1.5, are slower on 2.5. And there are 
lots of other things that can make a database application slow.

Firebird 2.5 is generally better than Firebird 1.5, so a general advice would 
be to upgrade.  However, there’s no “performance factor” that was set to 5 in 
Fb 1.5 and 8 in Fb 2.5 and you’ve told us too little about your situation for 
us to give you any good advice. Some things will be quicker, some may be 
slower. ‘It works very well, how can I improve with minimum changes?’ is an 
almost impossible question to answer without more information regardless of 
whether you’re talking about databases, playing soccer or cooking.

Set

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

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

 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. I'm
no SQL guru, I just use Firebird as the backend of an app I'm writing,
but would you mind elaborating on what you've said. Just some
buzzwords/key words I should use on a google search would be greatly
appreciated.

Sure, Andrew, this is nothing complicated at all.

Once upon a time I used Paradox, a desktop database that I consider quite OK 
for small tables and single users. However, tables with one hundred thousand 
rows or so made things slow down considerably.

Using Firebird the Paradox way, i.e. loading all records and all fields every 
time is a bad idea except for tiny lookup tables. Firebird is good at finding 
the fields of the records you're interested in - including joining tables or 
doing some minor calculations upon the fields. Using Firebird simply as a place 
to store tables and doing all selection in code in your app rather than using a 
WHERE clause will make things slower - you may compare it to me prefering to 
take what I want to eat out of the fridge rather than take everything out of 
the fridge, find what I want to eat and put the rest back into the fridge. 
Similarly, I prefer to name the fields I need, rather than use SELECT * (there 
are exceptions, of course).

Generally speaking, a result set should only contain the data that you require 
for whatever you're doing, and the smaller the result set is, the better.

Using Firebird the client/server way with appropriate indexing, it doesn't 
normally matter all too much how big tables and databases are, although I must 
admit that I'm only used to working with tables with a few million rows, and 
have no experience with really big databases.

Set

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

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

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 integrity
gbak: ERROR:table/procedure has non-SQL security class defined
gbak:Exiting before completion due to errors

I read this as attempting to grant SYSDBA something that is impossible. E.g. it 
wouldn't surprise me if InterBase 6 allowed you to grant EXECUTE to a table 
(EXECUTE only makes sense to grant to a procedure), and that Firebird barks 
since is stricter regarding syntax than IB 6 was. 

Now, I don't know how to restore a database without grants specified to the 
table (others may know), but I'd recommend you to try restoring using the -o 
switch (one table at a time), possibly also using the -i (set indexes inactive) 
and -n (check constraints disabled) switches.

But as I said, this is rambling and guesswork, as opposed to Vlad's, Helen's 
and Thomas' knowhow.

Set

PS! Firebird 2.5 is a considerably better database than InterBase 6.0 was 
(although 2.5.1 had a flaw that almost required backup/restore when upgrading 
to 2.5.2 or higher)

[firebird-support] GROUP BY optional with constants?

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

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_SET_NAME)
FROM RDB$DATABASE

It does not complain if constants are used:

SELECT 'This works', list(RDB$CHARACTER_SET_NAME)
FROM RDB$DATABASE

or even

SELECT 'This works', RDB$RELATION_ID,  list(RDB$CHARACTER_SET_NAME)
FROM RDB$DATABASE
GROUP BY 2

Now, I do not object to not having to write GROUP BY - I'm just very surprised 
that it seems to be optional with constants. Is there any particular reason, or 
is this a 'bug to be appreciated rather than fixed'?
Set

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

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

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 login, a have one error, with 
insufficient sql rights for operation, no permission to read/select acces to 
TABLE. 
All different functions work OK. ROLE with GRANTS in all tables.
The solution from win7 is to give rights on USER for table CONFIG_BALANTA, the 
rights from the ROLE give this error
WHY THE DIFFERENT functionality? Same user, same rights on database. 

You may well know all I'm writing here, just thought I should point it out if 
it happened to be a simple misunderstanding.

It does not matter from where you log in. What does matter, is username 
(obviously) and that you connect using the correct role. Let's say I'm a member 
of two separate roles: DB_READ and DB_WRITE which have the obvious rights to a 
table that I have no access to myself. If I connect to the database specifying 
DB_WRITE as the role, I can do whatever I want, if I use DB_READ when 
connecting, then I can read, but not write data and if I do not specify any 
role when connecting, then I cannot do anything (not even read) unless rights 
have been granted me as a user rather than any role I'm a member of.

HTH,
Set

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

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

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 doing this:
SELECT CAST('13.000' AS INTEGER) FROM RDB$DATABASE
Becasue here precision is not lost.

Is it possible to obtain such behaviour in easy way or do I have to use 
regular expressions?

Not quite what you're asking for, Bruce, but 

SELECT CAST(13.245 AS INTEGER) FROM RDB$DATABASE
WHERE CAST(13.245 AS INTEGER) * 1000 = cast(13.245 * 1000 as Integer)

does return any rows, whereas this statement does:

SELECT CAST(13.000 AS INTEGER) FROM RDB$DATABASE
WHERE CAST(13.000 AS INTEGER) * 1000 = cast(13.000 * 1000 as Integer)

HTH,
Set

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

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

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 := :ID) INTO :SUM_AAA;
  SUSPEND;
END^
SET TERM ; ^
 
and run query
SELECT (SELECT P.SUM_AAA FROM MY_PROC_CALC_EXEC_PARAM(T.FIELD_ID) P) FROM 
TABLEX T
 
than select is prepared only once - ok :)

I'm impressed if Firebird understands that VAR_SQL doesn't change between each 
iteration so that preparing once is possible!

4. but how to change this sample code to use paramaters and do not prepare 
statement on every record?
 
SET TERM ^ ;
CREATE PROCEDURE MY_PROC_CALC_EXEC_PARAM(ID INTEGER) RETURNS(SUM_AAA INTEGER)
AS
DECLARE VARIABLE VAR_SQL VARCHAR(1000);
DECLARE VARIABLE VAR_BBB INTEGER;
DECLARE VARIABLE VAR_NR INTEGER;
BEGIN
  VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID ';
  VAR_NR = 1;
  FOR SELECT 
  BBB 
  FROM 
  TABLEZ 
  WHERE ID=:ID
  INTO :VAR_BBB
  DO
    BEGIN  
   /* DO SOME CALCULATION ON VAR_BBB */
   
   VAR_SQL = VAR_SQL || ' OR ID=:ID' || VAR_NR; 
        How to add here parameter to list of parameters for below execute 
statement?
    END
 
  EXECUTE STATEMENT (VAR_SQL) (ID := :ID,  ID1, ID2, ID3 ...) - How to 
specify parameters dynamically?
  INTO :SUM_AAA;
  
  SUSPEND;
END^
SET TERM ; ^
 
 
Is this somehow possible? Or should i add this to the Firebird bug tracker as 
new feature request?
This is important from performance point of view.
 
Here you change the SQL for each iteration, hence I cannot see how it would be 
possible to prepare only once. What you could try, is to use a temporary table, 
change your statement to:

SELECT SUM(Y.AAA) FROM TABLEY Y
JOIN TMP_TABLE T ON Y.ID = T.ID

and in your loop do

INSERT INTO TMP_TABLE(ID) VALUES(:VAR_NR)

HTH,
Set

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

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

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 with:
PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2))

Admittedly, my way of plan thinking is far from the way things are 
implemented, but still - it normally helps me understand why the optimizer 
behaves as it does.

A simple query like yours I typically consider from the first table to the last 
in the plan. Hence, I'd start with T1.

SELECT *
FROM TABLE_1 T1

Here, there's no limiting JOIN, WHERE or anything similar that can be used to 
limit the number of rows to return (T2 isn't involved yet, hence any clause 
referring to that tuple cannot be considered). So, the quickest and only 
sensible option (at least unless you want to slow things down) is to go NATURAL 
on T1.

Then, we add T2

INNER JOIN TABLE_2 T2 ON T2.TABLE_1_ID = T1.ID

Here, you already have T1 available, and using an index for T2.TABLE_1_ID will 
help reducing the potential number of records to look up considerably.

Is there no way to utilize the index for T1.ID? Sure there is, you can start 
with T2 rather than T1. However, then you would start with

SELECT * FROM
TABLE_2 T2

which cannot use an index.

Adding

INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID

would, however, benefit from using the index for T1.ID.

Is there really no way to use an index for both fields? No, not unless you add 
a WHERE (or ON) clause that limits the result set through including one or more 
constants, e.g.

AND T1.ID between 2 and 4

Having said that, it is only on rather big tables that it is bad to see NATURAL 
on the first tuple in the plan (well, in more complex queries - e.g. involving 
subselects - it may not be the first tuple). And I think you would normally use 
a proper WHERE clause on big tables, although it is of course thinkable to e.g. 
want to have a query that should find all persons being a citizen of both 
London and Paris (millions of persons in both tables, but only a very small 
subset being part of both). Such rare queries are normally slowish, sometimes 
even after optimization.

HTH,
Set

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

[Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]]

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_TYPE, 
   CAST(COALESCE(OPS_ONBOARD_TIME, OPS_DELIVERED_TIME, OPS_APPT_LOW, 
'TODAY') AS DATE),
   COALESCE((SELECT FIRST 1 CTRR_REGION_ID
   FROM CT_REGION_RANGES
   WHERE OPS_ZIP BETWEEN CTRR_LOW_ZIP AND CTRR_HIGH_ZIP
   ORDER BY CTRR_REGION_ID), 'N/A'),
   IIF(OPS_TYPE = 'P', 1, 0),
   IIF(OPS_TYPE = 'P', 0, 1)
  FROM OPS_STOP_REC
LEFT JOIN OPS_HEADER ON PB_ID = OPS_ORDER_ID
  WHERE PB_TYPE = 'O' AND
PB_DT_ENT = :PB_DT_ENT)

SELECT PB_LOAD_TYPE, APPT_DATE, REGION_ID, sum(PICKUP) as PICKUPS, 
sum(DELIVERY) as DELIVERIES
  FROM tmp
  GROUP BY PB_LOAD_TYPE, APPT_DATE, REGION_ID
  ORDER BY APPT_DATE, PB_LOAD_TYPE, REGION_ID

HTH,
Set