Re: [firebird-support] SQL Error 303

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

27.05.2020 13:29, 'Olaf Kluge' olaf.kl...@satron.de [firebird-support] wrote:
> what function can I take instead of the isc_expand_dpb?

   There is no replacement for this function. You must use the same allocation 
function 
that was used for original DPB which depends on the host language.

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] SQL Error 303

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

The Firebird-support mailing list has moved from Yahoo Groups to Google 
Groups, https://groups.google.com/d/forum/firebird-support (or subscribe 
by emailing to firebird-support+subscr...@googlegroups.com and confirm 
by replying to the confirmation request). Could you please subscribe to 
the list on Google Groups and repost your question to that list?

Mark

On 2020-05-27 11:56, 'Check_Mail' check_m...@satron.de 
[firebird-support] wrote:
> Hello,
> 
> we have some problems since migration VC++ 2005 to VC++ 2016. The same
> code, the same fbclient.dll but now we get the error after connect:
> 
> Dynamic SQL Error
> 
> SQL error code = -303
> 
> Implementation of text subtype 205 not located.
> 
> What could it be? The charset is ISO8859_1, Firebird 3.0 superserver,
> no UDFs.
> 
> Thank you. Best regards.
> 
> Olaf

AW: [firebird-support] SQL Error 303

['Olaf Kluge' olaf.kl...@satron.de [firebird-support]]

Hello dmitry,

what function can I take instead of the isc_expand_dpb?

Thank you.


Mit freundlichen Grüßen / with best regards

Olaf Kluge



S A T R O N  Sachsen 
Steuerungstechnik GmbH
Johann-Gottlob-Pfaff Straße 7
D-09405 Zschopau


Tel: +49 (0) 3725 / 3506-31
Fax:    +49 (0) 3725 / 3506-12
Mobil:  +49 (0) 170 / 9292375
E-Mail:  olaf.kl...@satron.de
Internet: http://www.satron.de/

..
.
Geschäftsführer: Gerd Kaden
Amtsgericht: Chemnitz HRB1218
Ust-ID-Nr: DE141294791

..
.
Diese E-Mail ist vertraulich. Wenn Sie nicht der beabsichtigte Empfänger
sind, dürfen Sie die Informationen nicht offen legen oder benutzen. Wenn Sie
diese E-Mail durch einen Fehler bekommen haben, teilen Sie uns dies bitte
mit, indem Sie die E-Mail an den Absender zurücksenden. Bitte löschen Sie
danach diese E-Mail.
This email is confidential. If you are not the intended recipient, you must
not disclose or use the information contained in it.
If you have received this mail in error, please tell us immediately by
return email and delete the document.



-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com  
Gesendet: Mittwoch, 27. Mai 2020 12:53
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] SQL Error 303

27.05.2020 12:51, 'Check_Mail' check_m...@satron.de [firebird-support]
wrote:
> The name of the function is /*isc_expand_dpb*/

   This function is deprecated since Firebird 1.0.

-- 
   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] SQL Error 303

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

27.05.2020 12:51, 'Check_Mail' check_m...@satron.de [firebird-support] wrote:
> The name of the function is /*isc_expand_dpb*/

   This function is deprecated since Firebird 1.0.

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

AW: [firebird-support] SQL Error 303

['Check_Mail' check_m...@satron.de [firebird-support]]

The name of the function is isc_expand_dpb

 

Von: firebird-support@yahoogroups.com  
Gesendet: Mittwoch, 27. Mai 2020 12:39
An: firebird-support@yahoogroups.com
Betreff: AW: [firebird-support] SQL Error 303

 

  

Sorry, one thing. The firebird.dll from version 2 is working, the from fb3
not. Can anyone tell me what is different in this case from 2 to 3?

 

Von: firebird-support@yahoogroups.com
<mailto:firebird-support@yahoogroups.com>  mailto:firebird-support@yahoogroups.com> > 
Gesendet: Mittwoch, 27. Mai 2020 11:56
An: firebird-support@yahoogroups.com
<mailto:firebird-support@yahoogroups.com> 
Betreff: [firebird-support] SQL Error 303

 

  

Hello,

 

we have some problems since migration VC++ 2005 to VC++ 2016. The same code,
the same fbclient.dll but now we get the error after connect:

 

Dynamic SQL Error

 

SQL error code = -303

 

Implementation of text subtype 205 not located.

 

What could it be? The charset is ISO8859_1, Firebird 3.0 superserver, no
UDFs.

 

Thank you. Best regards.

 

Olaf

 

 

AW: [firebird-support] SQL Error 303

['Check_Mail' check_m...@satron.de [firebird-support]]

Sorry, one thing. The firebird.dll from version 2 is working, the from fb3
not. Can anyone tell me what is different in this case from 2 to 3?

 

Von: firebird-support@yahoogroups.com  
Gesendet: Mittwoch, 27. Mai 2020 11:56
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] SQL Error 303

 

  

Hello,

 

we have some problems since migration VC++ 2005 to VC++ 2016. The same code,
the same fbclient.dll but now we get the error after connect:

 

Dynamic SQL Error

 

SQL error code = -303

 

Implementation of text subtype 205 not located.

 

What could it be? The charset is ISO8859_1, Firebird 3.0 superserver, no
UDFs.

 

Thank you. Best regards.

 

Olaf

 

 

[firebird-support] SQL Error 303

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

we have some problems since migration VC++ 2005 to VC++ 2016. The same code,
the same fbclient.dll but now we get the error after connect:

 

Dynamic SQL Error

 

SQL error code = -303

 

Implementation of text subtype 205 not located.

 

What could it be? The charset is ISO8859_1, Firebird 3.0 superserver, no
UDFs.

 

Thank you. Best regards.

 

Olaf

 

 

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

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

OK, so the last change was successful: The +0 as suggested by Set works. 
The query now uses the faster query plan through the entire batch (at 
least it still does after 1769 chunks.

Many thanks for all your input! I still think it would be interesting 
with some theories about why the engine behaves like this, perhaps from 
the dev team...?

Mvh,
Kjell

Den 2020-05-01 kl. 09:53, skrev Kjell Rilbe 
kjell.ri...@marknadsinformation.se [firebird-support]:
>
> Well, the changed subselect didn't do the trick, but I think it speeds
> up the query slightly, overall.
>
> Now, it's this query that's causing problems:
>
> insert into "TmpFKExportUhant" ("ECO_ID", "Bärartyp")
> select FtgOmsar."Uppgiftshanterare", 'FöretagOmsättningsår' "Bärartyp"
> 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
> 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 Ar2."Årtal" > Ar."Årtal"
>   );
>
> The "breaking point" has now moved to 361 executions. Before that point,
> the "fast" execution plan looks like this (both styles):
>
> PLAN JOIN (
>   L NATURAL,
>   F INDEX ("IX_PK_Företag"),
>   FTGOMSAR INDEX ("IX_FöretagOmsättningsåDBN"),
>   AR INDEX ("IX_PK_År")
> )
>
> Select Expression
>     -> Nested Loop Join (inner)
>     -> Filter
>     -> Table "FöretagOmsättningsår" as "FTGOMSAR2" Access By ID
>     -> Bitmap
>     -> Index "IX_FöretagOmsättningsåDBN" Range Scan
> (full match)
>     -> Filter
>     -> Table "År" as "AR2" Access By ID
>     -> Bitmap
>     -> Index "IX_PK_År" Unique Scan
> Select Expression
>     -> Nested Loop Join (inner)
>     -> Table "TmpFKExportId" as "L" Full Scan
>     -> Filter
>     -> Table "Företag" as "F" Access By ID
>     -> Bitmap
>     -> Index "IX_PK_Företag" Unique Scan
>     -> Filter
>     -> Table "FöretagOmsättningsår" as "FTGOMSAR" Access By ID
>     -> Bitmap
>     -> Index "IX_FöretagOmsättningsåDBN" Range Scan
> (full match)
>     -> Filter
>     -> Table "År" as "AR" Access By ID
>     -> Bitmap
>     -> Index "IX_PK_År" Unique Scan
>
> After the slowdown, the "bad" query plan looks like this:
>
> PLAN JOIN (
>   L NATURAL,
>   F INDEX (IX_PK_Företag),
>   FTGOMSAR INDEX (IX_FöretagOmsättningsåDBN),
>   AR INDEX (IX_PK_År)
> )
>
> Select Expression
>     -> Nested Loop Join (inner)
>     -> Filter
>     -> Table "FöretagOmsättningsår" as "FTGOMSAR2" Access By ID
>     -> Bitmap
>     -> Index "IX_FöretagOmsättningsåDBN" Range Scan
> (full match)
>     -> Filter
>     -> Table "År" as "AR2" Access By ID
>     -> Bitmap
>     -> Index "IX_PK_År" Unique Scan
> Select Expression
>     -> Nested Loop Join (inner)
>     -> Table "År" as "AR" Full Scan
>     -> Filter
>     -> Table "FöretagOmsättningsår" as "FTGOMSAR" Access By ID
>     -> Bitmap
>     -> Index "IX_FöretagOmsättningså9OF" Range Scan
> (full match)
>     -> Filter
>     -> Table "TmpFKExportId" as "L" Access By ID
>     -> Bitmap
>     -> Index "PK_TmpFKExportId" Unique Scan
>     -> Filter
>     -> Table "Företag" as "F" Access By ID
>     -> Bitmap
>     -> Index "IX_PK_Företag" Unique Scan
>
> It's worth noting that the old-style query plans are identical, but the
> new-style ones do have a difference, which is probably causing the 
> problem.
>
> I am trying now with the +0 that Set suggested.
>
> But why does Firebird cosistently change it's execution strategy for a
> query that's being executed for the Nth time without change and on
> essentially unmodified data? During these batches, the tables "År" and
> "FöretagOmsättningår" are never changed in any way.. The table "Företag"
> may be changed in a few records, but not in the columns or indices used
> in the query. The table "TmpFKExportId" will have a new set of 250 id:s
> for each execution of the query, but its index selectivity would be
> constant (1/250). So, as far as I can see, there's absolutely no reason
> to be found in the actual data. It must be something else: cache,
> workload, garbage collector...?
>
> But during the batch, FBMonitor doesn't show any change in the garbage
> collector's behavior at the time of slowdown/change of query strategy.
>
> The only thing I can see in FBMonitor's graphs is that about 35 minutes
> before the slowdown, the I/O activity graph shows an increase in cache
> hits, but no apparent chan

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

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

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

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?

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

Well, the changed subselect didn't do the trick, but I think it speeds 
up the query slightly, overall.

Now, it's this query that's causing problems:

insert into "TmpFKExportUhant" ("ECO_ID", "Bärartyp")
select FtgOmsar."Uppgiftshanterare", 'FöretagOmsättningsår' "Bärartyp"
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
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 Ar2."Årtal" > Ar."Årtal"
   );

The "breaking point" has now moved to 361 executions. Before that point, 
the "fast" execution plan looks like this (both styles):

PLAN JOIN (
   L NATURAL,
   F INDEX ("IX_PK_Företag"),
   FTGOMSAR INDEX ("IX_FöretagOmsättningsåDBN"),
   AR INDEX ("IX_PK_År")
)

Select Expression
     -> Nested Loop Join (inner)
     -> Filter
     -> Table "FöretagOmsättningsår" as "FTGOMSAR2" Access By ID
     -> Bitmap
     -> Index "IX_FöretagOmsättningsåDBN" Range Scan 
(full match)
     -> Filter
     -> Table "År" as "AR2" Access By ID
     -> Bitmap
     -> Index "IX_PK_År" Unique Scan
Select Expression
     -> Nested Loop Join (inner)
     -> Table "TmpFKExportId" as "L" Full Scan
     -> Filter
     -> Table "Företag" as "F" Access By ID
     -> Bitmap
     -> Index "IX_PK_Företag" Unique Scan
     -> Filter
     -> Table "FöretagOmsättningsår" as "FTGOMSAR" Access By ID
     -> Bitmap
     -> Index "IX_FöretagOmsättningsåDBN" Range Scan 
(full match)
     -> Filter
     -> Table "År" as "AR" Access By ID
     -> Bitmap
     -> Index "IX_PK_År" Unique Scan

After the slowdown, the "bad" query plan looks like this:

PLAN JOIN (
   L NATURAL,
   F INDEX (IX_PK_Företag),
   FTGOMSAR INDEX (IX_FöretagOmsättningsåDBN),
   AR INDEX (IX_PK_År)
)

Select Expression
     -> Nested Loop Join (inner)
     -> Filter
     -> Table "FöretagOmsättningsår" as "FTGOMSAR2" Access By ID
     -> Bitmap
     -> Index "IX_FöretagOmsättningsåDBN" Range Scan 
(full match)
     -> Filter
     -> Table "År" as "AR2" Access By ID
     -> Bitmap
     -> Index "IX_PK_År" Unique Scan
Select Expression
     -> Nested Loop Join (inner)
     -> Table "År" as "AR" Full Scan
     -> Filter
     -> Table "FöretagOmsättningsår" as "FTGOMSAR" Access By ID
     -> Bitmap
     -> Index "IX_FöretagOmsättningså9OF" Range Scan 
(full match)
     -> Filter
     -> Table "TmpFKExportId" as "L" Access By ID
     -> Bitmap
     -> Index "PK_TmpFKExportId" Unique Scan
     -> Filter
     -> Table "Företag" as "F" Access By ID
     -> Bitmap
     -> Index "IX_PK_Företag" Unique Scan

It's worth noting that the old-style query plans are identical, but the 
new-style ones do have a difference, which is probably causing the problem.

I am trying now with the +0 that Set suggested.

But why does Firebird cosistently change it's execution strategy for a 
query that's being executed for the Nth time without change and on 
essentially unmodified data? During these batches, the tables "År" and 
"FöretagOmsättningår" are never changed in any way. The table "Företag" 
may be changed in a few records, but not in the columns or indices used 
in the query. The table "TmpFKExportId" will have a new set of 250 id:s 
for each execution of the query, but its index selectivity would be 
constant (1/250). So, as far as I can see, there's absolutely no reason 
to be found in the actual data. It must be something else: cache, 
workload, garbage collector...?

But during the batch, FBMonitor doesn't show any change in the garbage 
collector's behavior at the time of slowdown/change of query strategy.

The only thing I can see in FBMonitor's graphs is that about 35 minutes 
before the slowdown, the I/O activity graph shows an increase in cache 
hits, but no apparent change in disk reads or disk writes. Studying the 
actual statements around that time, I fail to see any significant change 
in the statistics. The statistics do change significantly at the time of 
slowdown though, when the bad query plans starts being used.

I think Firebird's behavior is really really weird here. It consistently 
changes to a worse query plan for the same query on essentially the same 
data, just because it's already been executed N times, where N = 316-320 
with the old query and apparently N = ~361 with the current query.

This must be caused by some internal transient state, associ

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

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

Hi Set!

Understood and I thank you for pointing it out. My last attempt was to 
put all the Uhant."ECO_ID" in a temp table and then use that to run the 
last part of the query. That avoids the unions, but adds some extra work 
to store the records in the temp table. All in all I suppose it's 
slightly more work for the engine.

But as it turns out, it's that part of the query that suddenly takes a 
lot longer to execute.

Trying now with the alternative subselect you suggested earlier, i.e. 
not exists (...). If that doesn't work out, I'll try the +0 you suggest 
below. Thumbs up!

Regards,
Kjell

Den 2020-04-30 kl. 23:14, skrev 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] 
>  >:
>
> 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
>
>
> firebird-support-fullfeatu...@yahoogroups.com
> 
>
>
> 


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

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: ODP: ODP: ODP: [firebird-support] SQL slower after N executions?

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

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]

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

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

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

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

Maybe it's worth noting that if I execute that exact query in 
FlameRobin, while the batch is running and suffering 50+ second 
execution time, FlameRobin gets it executed in 2-4 seconds every single 
time.

Weird.

I'll try to rewrite the query using execute block, a temporary table or 
something like that, to reduce query complexity.

Kjell

Den 2020-04-30 kl. 13:04, skrev Kjell Rilbe 
kjell.ri...@marknadsinformation.se [firebird-support]:
>
> Yes, but the transaction just wasn't there.
>
> I did let it run during the night. Here are FBMonitor diagrams from
> about 1 hour before slowdown up to now:
> https://cdn.mise.se/FirebirdDiagrams.png
>
> The vertical line in each diagram marks the time of slowdown (at about
> 22:05 last night).
>
> The upper left diagram shows in red that I accidentally left a
> transaction open in FlameRobin at about 20:50, which does not correlate
> to the time of slowdown. I committed it at 7:43 this morning.
>
> The I/O activity diagram shows that the cache activity goes up
> significantly at this time, while disk reads drop slightly. Disk writes
> drop a bit earlier. Nothing significant seems to happen with these
> params at the time of slowdown, except that there's a spike of disk
> reads at that moment. Noting noteworthy in any other diagrams as far as
> I can see.
>
> Looking in the statement history from FBMonitor, for the SQL query of
> interest, I see a significant change in the statistics and execution
> plan at the time of slowdown.
>
> ===Before slowdown===
> Fetches lies around 150 GB
> Table scan lies around 94-95 %
> Sequential reads lies around 2.3 million
> Indexed reads lies around 130 thousand
> Fragment reads lies around 650
>
> ===After slowdown===
> Fetches lies around 1.17 TB (up a factor 8)
> Table scan lies around 24 (down 70 %)
> Sequential reads lies around 4.7 million (up a factor 2)
> Indexed reads lies around 14.7 million (up a factor 113)
> Fragment reads lies around 2300 (up a factor 3.5)
>
> The execution plan (new format, but unfortunately truncated) changes
> from this:
>
> === BEFORE SLOWDOWN ===
>
> Select Expression
>     -> Singularity Check
>     -> Aggregate
>     -> Nested Loop Join (inner)
>     -> Filter
>     -> Table "FöretagOmsättningsår" as "UHANT
> FTGOMSAR2" Access By ID
>     -> Bitmap
>     -> Index "IX_FöretagOmsättningsåDBN" Range
> Scan (full match)
>     -> Filter
>     -> Table "År" as "UHANT AR2" Access By ID
>     -> Bitmap
>     -> Index "IX_PK_År" Unique Scan
> Select Expression
>     -> Filter
>     -> Table "Uppgiftsspärr" as "SP2" Access By ID
>     -> Bitmap
>     -> Index "IX_Uppgiftsspärr_Uppgi60J" Range Scan (full
> match)
> Select Expression
>     -> Nested Loop Join (inner)
>     -> Filter
>     -> Table "Uppgiftshanterare" as "UHANT2" Access By ID
>     -> Bitmap
>     -> Index "IX_PK_Uppgiftshanterare" Unique Scan
>     -> Filter
>     -> Table "Uppgiftsspärr" as "SP2" Access By ID
>     -> Bitmap
>     -> Index "IX_Uppgiftsspärr_Uppgi8MK" Range Scan
> (full match)
> Select Expression
>     -> Filter
>     -> Table "Uppgiftshållare" as "UH2" Access By ID
>     -> Bitmap
>     -> Index "IX_PK_Uppgiftshållare" Unique Scan
> Select Expression
>     -> Unique Sort (record length: 484, key length: 20)
>     -> Filter
>     -> Nested Loop Join (outer)
>     -> Nested Loop Join (outer)
>     -> Nested Loop Join (inner)
>     -> Nested Loop Join (inner)
>     -> Unique Sort (record length: 322, key
> length: 112)
>     -> Union
>     -> Nested Loop Join (inner)
>     -> Table "TmpFKExportId" as
> "UHANT L" Full Scan
>     -> Filter
>     -> Table "Företag" as
> "UHANT F" Access By ID
>     -> Bitmap
>     -> Index
> "IX_PK_Företag" Unique Scan
>     -> Filter
>     -> Table
> "Uppgiftshanterare" as "UHANT UHANT" Access By ID
>     -> Bitmap
>     -> Index
> "IX_PK_Uppgiftshanterare" Unique Scan
> === DIFF STARTS HERE ===
>     -> Nested Loop Join (inner)
>     -> Table "TmpFKExportId" as
> "UHANT L" Full Scan
>     -> Filter
>     -> Table "Företag" as
> "UHANT F" Ac

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

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

Yes, but the transaction just wasn't there.

I did let it run during the night. Here are FBMonitor diagrams from 
about 1 hour before slowdown up to now:
https://cdn.mise.se/FirebirdDiagrams.png

The vertical line in each diagram marks the time of slowdown (at about 
22:05 last night).

The upper left diagram shows in red that I accidentally left a 
transaction open in FlameRobin at about 20:50, which does not correlate 
to the time of slowdown. I committed it at 7:43 this morning.

The I/O activity diagram shows that the cache activity goes up 
significantly at this time, while disk reads drop slightly. Disk writes 
drop a bit earlier. Nothing significant seems to happen with these 
params at the time of slowdown, except that there's a spike of disk 
reads at that moment. Noting noteworthy in any other diagrams as far as 
I can see.

Looking in the statement history from FBMonitor, for the SQL query of 
interest, I see a significant change in the statistics and execution 
plan at the time of slowdown.

===Before slowdown===
Fetches lies around 150 GB
Table scan lies around 94-95 %
Sequential reads lies around 2.3 million
Indexed reads lies around 130 thousand
Fragment reads lies around 650

===After slowdown===
Fetches lies around 1.17 TB (up a factor 8)
Table scan lies around 24 (down 70 %)
Sequential reads lies around 4.7 million (up a factor 2)
Indexed reads lies around 14.7 million (up a factor 113)
Fragment reads lies around 2300 (up a factor 3.5)

The execution plan (new format, but unfortunately truncated) changes 
from this:

=== BEFORE SLOWDOWN ===

Select Expression
     -> Singularity Check
     -> Aggregate
     -> Nested Loop Join (inner)
     -> Filter
     -> Table "FöretagOmsättningsår" as "UHANT 
FTGOMSAR2" Access By ID
     -> Bitmap
     -> Index "IX_FöretagOmsättningsåDBN" Range 
Scan (full match)
     -> Filter
     -> Table "År" as "UHANT AR2" Access By ID
     -> Bitmap
     -> Index "IX_PK_År" Unique Scan
Select Expression
     -> Filter
     -> Table "Uppgiftsspärr" as "SP2" Access By ID
     -> Bitmap
     -> Index "IX_Uppgiftsspärr_Uppgi60J" Range Scan (full 
match)
Select Expression
     -> Nested Loop Join (inner)
     -> Filter
     -> Table "Uppgiftshanterare" as "UHANT2" Access By ID
     -> Bitmap
     -> Index "IX_PK_Uppgiftshanterare" Unique Scan
     -> Filter
     -> Table "Uppgiftsspärr" as "SP2" Access By ID
     -> Bitmap
     -> Index "IX_Uppgiftsspärr_Uppgi8MK" Range Scan 
(full match)
Select Expression
     -> Filter
     -> Table "Uppgiftshållare" as "UH2" Access By ID
     -> Bitmap
     -> Index "IX_PK_Uppgiftshållare" Unique Scan
Select Expression
     -> Unique Sort (record length: 484, key length: 20)
     -> Filter
     -> Nested Loop Join (outer)
     -> Nested Loop Join (outer)
     -> Nested Loop Join (inner)
     -> Nested Loop Join (inner)
     -> Unique Sort (record length: 322, key 
length: 112)
     -> Union
     -> Nested Loop Join (inner)
     -> Table "TmpFKExportId" as 
"UHANT L" Full Scan
     -> Filter
     -> Table "Företag" as 
"UHANT F" Access By ID
     -> Bitmap
     -> Index 
"IX_PK_Företag" Unique Scan
     -> Filter
     -> Table 
"Uppgiftshanterare" as "UHANT UHANT" Access By ID
     -> Bitmap
     -> Index 
"IX_PK_Uppgiftshanterare" Unique Scan
=== DIFF STARTS HERE ===
     -> Nested Loop Join (inner)
     -> Table "TmpFKExportId" as 
"UHANT L" Full Scan
     -> Filter
     -> Table "Företag" as 
"UHANT F" Access By ID
     -> Bitmap
     -> Index 
"IX_PK_Företag" Unique Scan
     -> Filter
     -> Table 
"FöretagOmsättningsår" as "UHANT FTGOMSAR" Access By ID
     -> Bitmap
     -> Index 
"IX_FöretagOmsättningsåDBN" Range Scan (full match)
     -> Filter
  

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

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Simply run query with join two tables MON$ATATACHEMENTS and MON$TRANSACTIONS – 
you can also be interested with joining with MON$STATEMENTS
And then you can see which attachement consume this transaction (you can 
retrive IP port and proces ID (PID)

Pozdrawiam,
Karol Bieniaszewski

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

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

Thanks,

I tried firing up Gregor Kobler's FBMonitor (similar to the old but dead 
Sinatica Monitor).

I found that OAT is 222085103 while OIT and OST (what's that?) is 
261140768 and growing. I assume the OAT must have got stuck somehow 
quite a while back, since it's so far behind the others. The machine was 
rebooted for upgrades rather recently, and I can't see any stuck 
transactions, so the OAT must be a zombie one.

No auto sweep (interval set to 0), so I'm trying a manual sweep now. 
Guess I should schedule that weekly or something.

I couldn't see anything interesting happening with the trans numbers 
when I exited the batch app. Will restart it after the manual sweep and 
see if anything interesting shows up. The slowdown, if still there, 
would probably happen after I go to sleep, so I'll have to follow up 
tomorrow.

Other info about the database, in case it says anything:

Page size: 16384
ODS: 12.0
Page buffers: 614400 (roughly 9.5 Gbyte)
Sweep interval: 0 (like I mentioned)
Forced writes: off (yes, I know the risks)

 From databases.conf:
DefaultDbCachePages = 600K (same as Page buffers above, of course)
FileSystemCacheThreshold = 1000K

 From firebird.conf:
FileSystemCacheSize = 40
TempCacheLimit = 4G
MaxUnflushedWrites = 100
MaxUnflushedWriteTime = 60
ServerMode = Super

All other performance params default. Some auth and access restriction 
params are non default, but I assume that's irrelevant.

System has 40 Gbyte RAM and the temp disks have 70+ Gbyte free.

Regards,
Kjell

Den 2020-04-29 kl. 14:00, skrev Karol Bieniaszewski 
liviusliv...@poczta.onet.pl [firebird-support]:

> Hi
>
> There are many possibilities without access i can only hint you:
>
> Look at MON$Tranasctions maybe you have active one which stop garbage 
> collecion.
>
> Look also at sort buffer setting if firebird.conf
>
> Look at settings about buffers in database itself (gfix -h show you 
> value).
>
> Look also at automatic sweep settings (also gfix -h show you value).
>
> Update Firebird to most recent version official is 3.0.5 but i use 
> most recent snapshot without problems.
>
> More can be tell after some details provided.
>
> regards,
>
> Karol Bieniaszewski
>
> 


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

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" 

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

[Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]]

Hi

There are many possibilities without access i can only hint you:
Look at MON$Tranasctions maybe you have active one which stop garbage collecion.
Look also at sort buffer setting if firebird.conf
Look at settings about buffers in database itself (gfix -h show you value).
Look also at automatic sweep settings (also gfix -h show you value).
Update Firebird to most recent version official is 3.0.5 but i use most recent 
snapshot without problems.

More can be tell after some details provided.

regards,
Karol Bieniaszewski

[firebird-support] SQL slower after N executions?

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

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" = F."ECO_ID"
     inner join "Person" P on P."ECO_ID" = BI."Person"
     inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" = 
P."Uppgiftshanterare"
   ) Uhant
inner join "Uppgiftshållare" UH on UH."Hanterare" = Uhant."ECO_ID"
inner join "Uppgift" Upg on Upg."Hållare" = UH."ECO_ID"
left join "Uppgiftsspärr" SpUhant on SpUhant."Uppgiftshanterare" = 
Uhant."ECO_ID" and Uhant."Bärartyp" in ('Epostadress', 'Person')
left join "Uppgiftsspärr" SpUH on SpUH."Uppgiftshållare" = UH."ECO_ID"
where Upg."TogsBort" is null
   and SpUhant."ECO_ID" is null
   and SpUH."ECO_ID" is null
   and not exists (
     select 1
     from "Uppgiftshållare" UH2
     where UH2."ECO_ID" = Upg."RelateradHållare"
   and (
     exists (
   select 1
   from "Uppgiftsspärr" Sp2
   where Sp2."Uppgiftshållare" = UH2."ECO_ID"
     )
     or exists (
   select 1
   from "Uppgiftsspärr" Sp2
   inner join "Uppgiftshanterare" Uhant2 on Uhant2."ECO_ID" = 
Sp2."Uppgiftshanterare"
   where Uhant2."ECO_ID" = UH2."Hanterare"
     )
   )
   )

Mvh,
Kjell



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

RE: [firebird-support] SQL

[mohamed hamza medh...@hotmail.com [firebird-support]]

What do you think about creating view? ( ex :viewAge ) and then we can write

select * from viewAge where state='CA'



De : firebird-support@yahoogroups.com  de la 
part de Richard Damon rich...@damon-family.org [firebird-support] 

Envoyé : mercredi 28 août 2019 05:27
À : firebird-support@yahoogroups.com 
Objet : Re: [firebird-support] SQL



On 8/5/19 4:20 AM, mohamed hamza medh...@hotmail.com [firebird-support]
wrote:
>
>
> Hi All,
>
>   I am new to sql,  I am coming  from  Xbase  system .
>  I want to know if it 's  possible to execute query using last query
> result?
>
> EXAMPLE
>
> 1  select * from persons where state='CA'
>
> 2 select * from persons where age> 18   but  we  have to use the
> result of 1
>
> I DO NOT WANT TO DO AN AND(  STATE = 'CA'AND   AGE > 18  ) ;
>
> Best Regards

Maybe something like:

SELECT * FROM ( SELECT * FROM persons where state = 'CA') where age > 18;

But using AND will generally be faster.

As far as I know, you can't just reference the previous query, unless
you explicitly put that result into a temporary table.

--
Richard Damon

Re: [firebird-support] SQL

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

> On Aug 28, 2019, at 12:54 AM, Kjell Rilbe kjell.ri...@marknadsinformation..se 
> [firebird-support]  wrote:
> 
> Den 2019-08-05 kl. 13:20, skrev mohamed hamza medh...@hotmail.com 
> [firebird-support]:
>> 
>> Hi All,
>> 
>>   I am new to sql,  I am coming  from  Xbase  system .
>>  I want to know if it 's  possible to execute query using last query 
>> result?
>> 
>> EXAMPLE
>> 1  select * from persons where state='CA'
>> 
>> 2 select * from persons where age> 18   but  we have to use the 
>> result of 1
>> 
>> I DO NOT WANT TO DO AN AND(  STATE = 'CA'AND  AGE > 18  ) ;
> 
> 
> First: Why not use and AND? That would be the most natural solution.

And will perform best.  If both terms are indexed, Firebird will use both 
indexes.  
> 
> Second: No, a plain sequence of SQL queries do not support that. But you 
> can put them in a stored procedure or an "execute block" statement ...

Would a CTE work?

Good luck,

Ann

Re: [firebird-support] SQL

[Richard Damon rich...@damon-family.org [firebird-support]]

On 8/5/19 4:20 AM, mohamed hamza medh...@hotmail.com [firebird-support]
wrote:
>  
>
> Hi All,
>
>   I am new to sql,  I am coming  from  Xbase  system .
>  I want to know if it 's  possible to execute query using last query
> result?
>
> EXAMPLE
>   
> 1  select * from persons where state='CA'
>
> 2 select * from persons where age> 18       but  we  have to use the
> result of 1
>
> I DO NOT WANT TO DO AN AND        (  STATE = 'CA'    AND   AGE > 18  ) ;
>
> Best Regards

Maybe something like:

SELECT * FROM ( SELECT * FROM persons where state = 'CA') where age > 18;

But using AND will generally be faster.

As far as I know, you can't just reference the previous query, unless
you explicitly put that result into a temporary table.

-- 
Richard Damon

Re: [firebird-support] SQL

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

Den 2019-08-05 kl. 13:20, skrev mohamed hamza medh...@hotmail.com 
[firebird-support]:
>
> Hi All,
>
>   I am new to sql,  I am coming  from  Xbase  system .
>  I want to know if it 's  possible to execute query using last query 
> result?
>
> EXAMPLE
> 1  select * from persons where state='CA'
>
> 2 select * from persons where age> 18       but  we have to use the 
> result of 1
>
> I DO NOT WANT TO DO AN AND        (  STATE = 'CA'    AND  AGE > 18  ) ;


First: Why not use and AND? That would be the most natural solution.

Second: No, a plain sequence of SQL queries do not support that. But you 
can put them in a stored procedure or an "execute block" statement, and 
save results in variables for later use.

For example:

set term #;
execute block as
declare person_id int;
begin
   for select id from persons where state = 'CA' into :person_id
   do select * from persons where age > 18 and id = :person_id;
end#
set term ;#

But you still end up with an "and" in your second SQL.

Maybe it's best if you explain what you're actually trying to achieve, 
and what the actual problem is, because using AND is not really a 
problem at all.

Regards,
Kjell Rilbe



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

[firebird-support] SQL

[mohamed hamza medh...@hotmail.com [firebird-support]]

Hi All,

  I am new to sql,  I am coming  from  Xbase  system .
 I want to know if it 's  possible to execute query using last query result?

EXAMPLE

1  select * from persons where state='CA'

2 select * from persons where age> 18   but  we  have to use the result of 1

I DO NOT WANT TO DO AN AND(  STATE = 'CA'AND   AGE > 18  ) ;

Best Regards

Re: [firebird-support] SQL output required in vertical format

[Vishal Tiwari vishuals...@yahoo.co.in [firebird-support]]

When 
SD.Branch_Code = 'Branch4' Then 1 Else 0 End) AS Branch4,Sum(Case When 
SD.Branch_Code = 'Branch5' Then 1 Else 0 End) AS Branch5,Sum(Case When 
SD.Branch_Code = 'Branch6' Then 1 Else 0 End) AS Branch6
>From Sales S
Left Join Sales_Details SD1 ON SD1.Sales_Person_Code = S.Sales_PersonAnd 
SD1.dtDate = '01/01/2010'Left Join Sales_Details SD2 ON SD2.Sales_Person_Code = 
S.Sales_PersonAnd SD2.dtDate = '01/02/2010'Left Join Sales_Details SD3 ON 
SD3.Sales_Person_Code = S.Sales_PersonAnd SD3.dtDate = '01/03/2010'Left Join 
Sales_Details SD4 ON SD4.Sales_Person_Code = S.Sales_PersonAnd SD4.dtDate = 
'01/04/2010'Left Join Sales_Details SD5 ON SD5.Sales_Person_Code = 
S.Sales_PersonAnd SD5.dtDate = '01/05/2010'Left Join Sales_Details SD6 ON 
SD6.Sales_Person_Code = S.Sales_PersonAnd SD6.dtDate = '01/06/2010'
Left Join Sales_Details SD ON SD.Sales_Person_Code = S.Sales_PersonWhere 
SD.dtDate Between '01/01/2010' And '01/06/2010'
Group By 
S.Sales_Person, SD1.Branch_Code, SD2.Branch_Code, SD3.Branch_Code, 
SD4.Branch_Code, SD5.Branch_Code, SD6.Branch_Code 

On Friday, 14 May 2010 1:40 AM, Svein Erling Tysvær 
 wrote:
 

     Hi Vishal!
Firebird is not ideal for your question no. 1, but if you know the number of 
columns to return, then, well, it is almost doable (you have to do some 
tweaking to my suggestion to get sensible column names, I have not included the 
dates although that would be just casting the dates as char or varchar and do a 
UNION). With an unknown number of columns to return, then I'd say it is 
unsolvable using only SQL. Here's one way to do things if there's three dates, 
it is easy to expand:

WITH RECURSIVE MyDates(MyDate) AS
(SELECT (CAST :FromDate) AS DATE
 FROM RDB$DATABASE
 UNION ALL
 SELECT MyDate+1
 FROM MyDates
 WHERE MyDate < :ToDate),
SalesPersons(SalePersonCode) AS
(SELECT DISTINCT SalesPersonCode
FROM SalesDetails
WHERE dtDate BETWEEN :FromDate and :ToDate)

SELECT SP.SalePersonCode, SD1.BranchCode, SD2.BranchCode, SD3.BranchCode
FROM SalesPersons SP
JOIN MyDates MD1 ON MD.MyDate = :FromDate
JOIN MyDates MD2 ON MD1.MyDate+1 = MD2.MyDate
JOIN MyDates MD3 ON MD2.MyDate+1 = MD3.MyDate
LEFT JOIN SalesDetails SD1 
 ON SP.SalePersonCode = SD1.SalePersonCode
 AND MD1.MyDate = SD1.dtDate
LEFT JOIN SalesDetails SD2 
 ON SP.SalePersonCode = SD2.SalePersonCode
 AND MD2.MyDate = SD2.dtDate 
LEFT JOIN SalesDetails SD3 
 ON SP.SalePersonCode = SD3.SalePersonCode
 AND MD3.MyDate = SD3.dtDate

Your question no. 2 is simpler:
SELECT SalePersonCode, 
 sum(CASE WHEN BranchCode = 'Brn1' THEN 1 ELSE 0 END) AS Brn1,
 sum(CASE WHEN BranchCode = 'Brn2' THEN 1 ELSE 0 END) AS Brn2,
 sum(CASE WHEN BranchCode = 'Brn3' THEN 1 ELSE 0 END) AS Brn3,
 sum(CASE WHEN BranchCode = 'Brn4' THEN 1 ELSE 0 END) AS Brn4,
 sum(CASE WHEN BranchCode = 'Brn5' THEN 1 ELSE 0 END) AS Brn5
FROM SalesDetails
WHERE dtDate BETWEEN :FromDate and :ToDate
GROUP BY 1

Mixing these two together should not be all too difficult, you just need 
separate referenced to SalesDetails and expand the GROUP BY.

HTH,
Set

-Original Message-
From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] On Behalf Of Vishal Tiwari
Sent: 14. mai 2010 06:41
To: firebird-support@yahoogroups.com
Subject: [firebird-support] SQL output required in vertical format

Hi All,
 
I have a table "SalesDetails" as follows:
 
dtDate Date Not Null,
SalesPersonCode Varchar(15) Not Null,
BranchCode Varchar(15),
Primary Key(dtDate, SalesPersonCode);

This table will contain the details of each sales person for each date under 
which brach he/she worked. There are fixed 5 branches. Please have a look into 
sample data:

dtDateSalesPersonCode BranchCode
01/01/2010  ABC    Brn1 
01/01/2010  LMN    Brn5 
01/01/2010  PQR    Brn2 
02/01/2010  ABC    Brn4 
02/01/2010  LMN    Brn5 
02/01/2010  PQR    Brn2 
 
 
1) I need to display the follwing desired output into the report, for this i 
need to get column wise each date details for each employee for give date 
period i.e. specified Start date to Specified End date. If i fail to express, 
please look into the following required query output for the report.
 
SalesPersoncode Date1 Date2 Date3 Date4 . Date30 
ABC        Brn1  Brn4  Brn4  Brn4  . Brn1
LMN    Brn5  Brn5  Brn5  Brn2  . Brn3
PQR    Brn2  Brn2  Brn2  Brn2  . Brn2
 
 
2) With this i need to get under each brench for given date period how many 
time SalesPerson has worked in tabular format:
 
SalesPerson Brn1 Brn2 Brn3 Brn4 Brn5
ABC     4    0    4    2    20 
LMN     4    0    2    4    20 
PQR20   0    4    6    0

I need to achieve above results using sql statement(s) only.
 
And is it possible to get No.1 and No.2 output in using single sql, if not then 
what could be the best way to achieve this output.

Thanks in advance.
 
Vishal


   

RE: [firebird-support] SQL performance SS FB 3.0.1 vs SC FB 2.5.2

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> Just found that on a particular case, SS FB 3.0.1 is about 4x slower than SC 
> FB
> 2.5.2.
> 
> Here is the query :
> ==
> select a.id,b.nm from tbl1 a, tbl2 b
> where a.id = b.id
> and a.id in (select c.id from tbl3 c)
> 
> If the query change to this :
> ==
> select a.id,b.nm from tbl1 a, tbl2 b, tbl3 c
> where a.id = b.id
> and a.id = c.id
> 
> SC FB 2.5.2 is about 1.3x faster than SS FB 3.0.1.

The queries are not really equivalent.

How about this query:

select a.id,b.nm 
from tbl1 a
  JOIN tbl2 b ON b.id = a.id
Where
  EXISTS( 
SELECT 1
FROM tbl3 c
WHERE
  c.id = a.id
  )

[firebird-support] SQL performance SS FB 3.0.1 vs SC FB 2.5.2

[trsk...@yahoo.com [firebird-support]]

Hi guys,

Just found that on a particular case, SS FB 3.0.1 is about 4x slower than SC FB 
2.5.2.

Here is the query : 
==
select a.id,b.nm from tbl1 a, tbl2 b 
where a.id = b.id 
and a.id in (select c.id from tbl3 c)

If the query change to this :
==
select a.id,b.nm from tbl1 a, tbl2 b, tbl3 c 
where a.id = b.id 
and a.id = c.id

SC FB 2.5.2 is about 1.3x faster than SS FB 3.0.1.

Regards,
Anto

Re: [firebird-support] SQL standard for sort fields and returned fields

['Mark Rotteveel' m...@lawinegevaar.nl [firebird-support]]

Could you please stop with your replies that contribute absolutely nothing to 
the discussion.
Mark

- Bericht beantwoorden -
Van: "kristinwens...@yahoo.com [firebird-support]" 

Aan: "firebird-support@yahoogroups.com" 
Onderwerp: [firebird-support] SQL standard for sort fields and returned fields
Datum: ma, mei 9, 2016 23:54

Perfect


On Monday, May 9, 2016 8:48 PM, "'Mark Rotteveel' m...@lawinegevaar.nl 
[firebird-support]"  wrote:















Ah. It is the exception, because ordering by column index (en order by 1, 3) 
uses the index of the column in the select list, so by definition that column 
needs to be in the list.
Mark

- Bericht beantwoorden -Van: "liviusliv...@poczta.onet.pl 
[firebird-support]" Aan: 
Onderwerp: [firebird-support] SQL standard 
for sort fields and returned fieldsDatum: ma, mei 9, 2016 21:40
Hi,
i am talking about your post 
“I don't have the SQL standard at hand right now, so I can't double 
check. As far as I know, the SQL standard allows you to sort on any 
field, even if it is not in the select list (with the exception of 
course of ordering by column index).”

and about line “with the exception of course of ordering by column index”


regards,
Karol Bieniaszewski


>>I see no exception, did you try to include an image in your e-mail? 
>>Please post query + error message as text instead.

>>Mark



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




Posted by: 


++

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 standard for sort fields and returned fields

[kristinwens...@yahoo.com [firebird-support]]

Perfect 

On Monday, May 9, 2016 8:48 PM, "'Mark Rotteveel' m...@lawinegevaar.nl 
[firebird-support]"  wrote:
 

     Ah. It is the exception, because ordering by column index (en order by 1, 
3) uses the index of the column in the select list, so by definition that 
column needs to be in the list.
Mark

- Bericht beantwoorden -
Van: "liviusliv...@poczta.onet.pl [firebird-support]" 

Aan: 
Onderwerp: [firebird-support] SQL standard for sort fields and returned fields
Datum: ma, mei 9, 2016 21:40
Hi,
i am talking about your post 
“I don't have the SQL standard at hand right now, so I can't double 
check. As far as I know, the SQL standard allows you to sort on any 
field, even if it is not in the select list (with the exception of 
course of ordering by column index).”

and about line “with the exception of course of ordering by column index”


regards,
Karol Bieniaszewski


>>I see no exception, did you try to include an image in your e-mail? 
>>Please post query + error message as text instead.

>>Mark



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




Posted by: 


++

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



  #yiv7219158047 #yiv7219158047 -- #yiv7219158047ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7219158047 
#yiv7219158047ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7219158047 
#yiv7219158047ygrp-mkp #yiv7219158047hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv7219158047 #yiv7219158047ygrp-mkp #yiv7219158047ads 
{margin-bottom:10px;}#yiv7219158047 #yiv7219158047ygrp-mkp .yiv7219158047ad 
{padding:0 0;}#yiv7219158047 #yiv7219158047ygrp-mkp .yiv7219158047ad p 
{margin:0;}#yiv7219158047 #yiv7219158047ygrp-mkp .yiv7219158047ad a 
{color:#ff;text-decoration:none;}#yiv7219158047 #yiv7219158047ygrp-sponsor 
#yiv7219158047ygrp-lc {font-family:Arial;}#yiv7219158047 
#yiv7219158047ygrp-sponsor #yiv7219158047ygrp-lc #yiv7219158047hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7219158047 
#yiv7219158047ygrp-sponsor #yiv7219158047ygrp-lc .yiv7219158047ad 
{margin-bottom:10px;padding:0 0;}#yiv7219158047 #yiv7219158047actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7219158047 
#yiv7219158047activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7219158047
 #yiv7219158047activity span {font-weight:700;}#yiv7219158047 
#yiv7219158047activity span:first-child 
{text-transform:uppercase;}#yiv7219158047 #yiv7219158047activity span a 
{color:#5085b6;text-decoration:none;}#yiv7219158047 #yiv7219158047activity span 
span {color:#ff7900;}#yiv7219158047 #yiv7219158047activity span 
.yiv7219158047underline {text-decoration:underline;}#yiv7219158047 
.yiv7219158047attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv7219158047 .yiv7219158047attach div a 
{text-decoration:none;}#yiv7219158047 .yiv7219158047attach img 
{border:none;padding-right:5px;}#yiv7219158047 .yiv7219158047attach label 
{display:block;margin-bottom:5px;}#yiv7219158047 .yiv7219158047attach label a 
{text-decoration:none;}#yiv7219158047 blockquote {margin:0 0 0 
4px;}#yiv7219158047 .yiv7219158047bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv7219158047 
.yiv7219158047bold a {text-decoration:none;}#yiv7219158047 dd.yiv7219158047last 
p a {font-family:Verdana;font-weight:700;}#yiv7219158047 dd.yiv7219158047last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7219158047 
dd.yiv7219158047last p span.yiv7219158047yshortcuts 
{margin-right:0;}#yiv7219158047 div.yiv7219158047attach-table div div a 
{text-decoration:none;}#yiv7219158047 div.yiv7219158047attach-table 
{width:400px;}#yiv7219158047 div.yiv7219158047file-title a, #yiv7219158047 
div.yiv7219158047file-title a:active, #yiv7219158047 
div.yiv7219158047file-title a:hover, #yiv7219158047 div.yiv7219158047file-title 
a:visited {text-decoration:none;}#yiv7219158047 div.yiv7219158047photo-title a, 
#yiv7219158047 div.yiv7219158047photo-title a:active, #yiv7219158047 
div.yiv7219158047photo-title a:hover, #yiv7219158047 
div.yiv7219158047photo-title a:visited {text-decoration:none;}#yiv7219158047 
div#yiv7219158047ygrp-mlmsg #yiv7219158047ygrp-msg p a 
span.yiv7219158047yshortcuts 
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv7219158047 
.yiv7219158047green {color:#628c2a;}#yiv7219158047 .yiv7219158047MsoNormal 
{mar

Re: [firebird-support] SQL standard for sort fields and returned fields

['Mark Rotteveel' m...@lawinegevaar.nl [firebird-support]]

Ah. It is the exception, because ordering by column index (en order by 1, 3) 
uses the index of the column in the select list, so by definition that column 
needs to be in the list.
Mark

- Bericht beantwoorden -
Van: "liviusliv...@poczta.onet.pl [firebird-support]" 

Aan: 
Onderwerp: [firebird-support] SQL standard for sort fields and returned fields
Datum: ma, mei 9, 2016 21:40

Hi,
i am talking about your post 
“I don't have the SQL standard at hand right now, so I can't double 
check. As far as I know, the SQL standard allows you to sort on any 
field, even if it is not in the select list (with the exception of 
course of ordering by column index).”

and about line “with the exception of course of ordering by column index”


regards,
Karol Bieniaszewski


>>I see no exception, did you try to include an image in your e-mail? 
>>Please post query + error message as text instead.

>>Mark



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




Posted by: 


++

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



https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] SQL standard for sort fields and returned fields

[liviusliv...@poczta.onet.pl [firebird-support]]

Hi,
i am talking about your post 
“I don't have the SQL standard at hand right now, so I can't double 
check. As far as I know, the SQL standard allows you to sort on any 
field, even if it is not in the select list (with the exception of 
course of ordering by column index).”

and about line “with the exception of course of ordering by column index”


regards,
Karol Bieniaszewski


>>I see no exception, did you try to include an image in your e-mail? 
>>Please post query + error message as text instead.

>>Mark



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

Re: ODP: Re: [firebird-support] SQL standard for sort fields and returned fields

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 2016-05-09 15:29, liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support] wrote:
> hi,
>
> could you explain this exception?
> And i also have not sql standard to look into and need some exact
> confirmation about this (something like Sql92 or sql2013.. say this)

I see no exception, did you try to include an image in your e-mail? 
Please post query + error message as text instead.

Mark

RE: Re: [firebird-support] SQL standard for sort fields and returned fields

[Ralf Jansen ralf.jan...@mailstore.com [firebird-support]]

What Exception?
Sql92 seems to not state anything in that regard. So this belongs presumably 
into the implementation specific area.

I’m sure if it had been defined there it would not be a clear yes/no thing.
For example, Firebird allows this even when the select clause contains DISTINCT 
so that some sort of grouping takes place. I have no idea on what info a 
meaningful sorting should take place then? The column wasn’t part of the 
grouping. In my (obviously wrong) mental model of what happens the column isn’t 
there anymore when ordering takes place.

Ralf


From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Montag, 9. Mai 2016 16:30
To: firebird-support@yahoogroups.com
Subject: ODP: Re: [firebird-support] SQL standard for sort fields and returned 
fields


hi,

could you explain this exception?
And i also have not sql standard to look into and need some exact confirmation 
about this (something like Sql92 or sql2013.. say this)

regards,
Karol Bieniaszewski


 Oryginalna wiadomość 
Od: "Mark Rotteveel m...@lawinegevaar.nl<mailto:m...@lawinegevaar.nl> 
[firebird-support]" 
mailto:firebird-support@yahoogroups.com>>
Data: 09.05.2016 13:20 (GMT+01:00)
Do: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com>
Temat: Re: [firebird-support] SQL standard for sort fields and returned fields



On 2016-05-09 11:45, liviuslivius 
liviusliv...@poczta.onet.pl<mailto:liviusliv...@poczta.onet.pl>
[firebird-support] wrote:
> Hi,
>
> This is not specific to Firebird question but:
> is something in SQL standard specification about sorted fields must
> be
> also returned?
> I mean:
>
> SELECT
> [FILM_DATE,]FILM_TITLE
> FROM
> FILMS
> ORDER BY FILM_DATE, FILM_TITLE
>
> is [FILM_DATE] required by SQL standard or it is optional or SQL
> standard tell nothing about this?

I don't have the SQL standard at hand right now, so I can't double
check. As far as I know, the SQL standard allows you to sort on any
field, even if it is not in the select list (with the exception of
course of ordering by column index).

Mark

ODP: Re: [firebird-support] SQL standard for sort fields and returned fields

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

hi,
could you explain this exception?And i also have not sql standard to look into 
and need some exact confirmation about this (something like Sql92 or sql2013.. 
say this)
regards,Karol Bieniaszewski

 Oryginalna wiadomość 
Od: "Mark Rotteveel m...@lawinegevaar.nl [firebird-support]" 
 
Data: 09.05.2016  13:20  (GMT+01:00) 
Do: firebird-support@yahoogroups.com 
Temat: Re: [firebird-support] SQL standard for sort fields and returned fields 


 



  



  
  
  On 2016-05-09 11:45, liviuslivius liviusliv...@poczta.onet.pl 

[firebird-support] wrote:

> Hi,

>

> This is not specific to Firebird question but:

> is something in SQL standard specification about sorted fields must 

> be

> also returned?

> I mean:

>

> SELECT

> [FILM_DATE,]FILM_TITLE

> FROM

> FILMS

> ORDER BY FILM_DATE, FILM_TITLE

>

> is [FILM_DATE] required by SQL standard or it is optional or SQL

> standard tell nothing about this?



I don't have the SQL standard at hand right now, so I can't double 

check. As far as I know, the SQL standard allows you to sort on any 

field, even if it is not in the select list (with the exception of 

course of ordering by column index).



Mark




 

Re: [firebird-support] SQL standard for sort fields and returned fields

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 2016-05-09 11:45, liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support] wrote:
> Hi,
>
> This is not specific to Firebird question but:
> is something in SQL standard specification about sorted fields must 
> be
> also returned?
> I mean:
>
> SELECT
> [FILM_DATE,]FILM_TITLE
> FROM
> FILMS
> ORDER BY FILM_DATE, FILM_TITLE
>
> is [FILM_DATE] required by SQL standard or it is optional or SQL
> standard tell nothing about this?

I don't have the SQL standard at hand right now, so I can't double 
check. As far as I know, the SQL standard allows you to sort on any 
field, even if it is not in the select list (with the exception of 
course of ordering by column index).

Mark

[firebird-support] SQL standard for sort fields and returned fields

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

Hi,
 
This is not specific to Firebird question but:
is something in SQL standard specification about sorted fields must be also 
returned?
I mean:
 
SELECT 
[FILM_DATE,]FILM_TITLE
FROM
FILMS
ORDER BY FILM_DATE, FILM_TITLE
 
is [FILM_DATE] required by SQL standard or it is optional or SQL standard tell 
nothing about this?
 
regards,
Karol Bieniaszewski

RE: [firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL?

['stwizard' stwiz...@att.net [firebird-support]]

Arno,

 

Thanks for your pointing out the issue with my SQL statement.

 

Mike

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Wednesday, April 06, 2016 4:36 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] SQL Error Code -104: What is wrong with this 
rather simply SQL?

 

  

Hi,

 

The message doesn’t tell you which expression is not contained in an aggregate 
function or GROUP BY clause, but is very clear that it is “P.PHONE_ID”

 

Not sure what you trying to achieve, but your SQL is indeed invalid, because 
there can be multiple PHONE_ID’s per AREA_CODE, PHONE_NO

 

Kind Regards,
Arno Brinkman

 

 

 

From: mailto:firebird-support@yahoogroups.com 

Sent: Wednesday, April 6, 2016 10:03 PM

To: firebird-support@yahoogroups.com 

Subject: [firebird-support] SQL Error Code -104: What is wrong with this rather 
simply SQL?

 






SELECT DISTINCT P.AREA_CODE, P.PHONE_NO,

  (SELECT COUNT(*) 

FROM PHONE P2 

   WHERE P2.PHONE_ID = P.PHONE_ID) AS CNT

  FROM PHONE P  

WHERE P.AREA_CODE IS NOT NULL 

GROUP BY 1, 2  

HAVING (SELECT COUNT(*) 

  FROM PHONE P3 

 WHERE P3.PHONE_ID = P.PHONE_ID

   AND P3.AREA_CODE IS NOT NULL) > 1

 

Dynamic SQL Error SQL error code = -104 Invalid expression in the select list 
(not contained in either an aggregate function or the GROUP BY clause)





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

RE: [firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL?

['stwizard' stwiz...@att.net [firebird-support]]

Tomasz,

Thanks for your guidance and explanation for a much simpler solution.

Mike

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, April 07, 2016 2:58 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] SQL Error Code -104: What is wrong with this 
rather simply SQL?

 

  

On 06.04.2016 o 22:03, 'stwizard' stwiz...@att.net [firebird-support] wrote:
> SELECT DISTINCT P.AREA_CODE, P.PHONE_NO,
>
> (SELECT COUNT(*)
>
> FROM PHONE P2
>
> WHERE P2.PHONE_ID = P.PHONE_ID) AS CNT
>
> FROM PHONE P
>
> WHERE P.AREA_CODE IS NOT NULL
>
> GROUP BY 1, 2

[ ... ]

select A, B, (select count(*) from ...)
from ...

is not the same as

select A, B, count(*)
from ...

In the former case, the sub-select is not an aggregate function. It 
simply calculates and returns a scalar (a single number), which is 
treated just like one more "field" in selected records.
Therefore, for the GROUP BY to work correctly, you'd have to include the 
third selected column (sub-select in your case) in the GROUP BY.
But that's not what you wanted, I suppose.
I guess what you really need is a normal grouped query:

select AREA_CODE, PHONE_NO, count(*)
from PHONE
where AREA_CODE is not null
group by 1, 2
having count(*) > 1

And that'll do the job.

Best regards
Tomasz





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

Re: [firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL?

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 06.04.2016 o 22:03, 'stwizard' stwiz...@att.net [firebird-support] wrote:
> SELECT DISTINCT P.AREA_CODE, P.PHONE_NO,
>
>(SELECT COUNT(*)
>
>  FROM PHONE P2
>
> WHERE P2.PHONE_ID = P.PHONE_ID) AS CNT
>
>FROM PHONE P
>
>   WHERE P.AREA_CODE IS NOT NULL
>
>   GROUP BY 1, 2

[ ... ]

select A, B, (select count(*) from ...)
from ...

is not the same as

select A, B, count(*)
from ...

In the former case, the sub-select is not an aggregate function. It 
simply calculates and returns a scalar (a single number), which is 
treated just like one more "field" in selected records.
Therefore, for the GROUP BY to work correctly, you'd have to include the 
third selected column (sub-select in your case) in the GROUP BY.
But that's not what you wanted, I suppose.
I guess what you really need is a normal grouped query:

select AREA_CODE, PHONE_NO, count(*)
from PHONE
where AREA_CODE is not null
group by 1, 2
having count(*) > 1

And that'll do the job.

Best regards
Tomasz

Re: [firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL?

['Arno Brinkman' fbsupp...@abvisie.nl [firebird-support]]

Hi,

The message doesn’t tell you which expression is not contained in an aggregate 
function or GROUP BY clause, but is very clear that it is “P.PHONE_ID”

Not sure what you trying to achieve, but your SQL is indeed invalid, because 
there can be multiple PHONE_ID’s per AREA_CODE, PHONE_NO

Kind Regards,
Arno Brinkman




From: mailto:firebird-support@yahoogroups.com 
Sent: Wednesday, April 6, 2016 10:03 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] SQL Error Code -104: What is wrong with this rather 
simply SQL?




SELECT DISTINCT P.AREA_CODE, P.PHONE_NO,

  (SELECT COUNT(*) 

FROM PHONE P2 

   WHERE P2.PHONE_ID = P.PHONE_ID) AS CNT

  FROM PHONE P  

WHERE P.AREA_CODE IS NOT NULL 

GROUP BY 1, 2  

HAVING (SELECT COUNT(*) 

  FROM PHONE P3 

 WHERE P3.PHONE_ID = P.PHONE_ID

   AND P3.AREA_CODE IS NOT NULL) > 1

 

Dynamic SQL Error SQL error code = -104 Invalid expression in the select list 
(not contained in either an aggregate function or the GROUP BY clause)

[firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL?

['stwizard' stwiz...@att.net [firebird-support]]

SELECT DISTINCT P.AREA_CODE, P.PHONE_NO,

  (SELECT COUNT(*) 

FROM PHONE P2 

   WHERE P2.PHONE_ID = P.PHONE_ID) AS CNT

  FROM PHONE P  

 WHERE P.AREA_CODE IS NOT NULL 

 GROUP BY 1, 2  

HAVING (SELECT COUNT(*) 

  FROM PHONE P3 

 WHERE P3.PHONE_ID = P.PHONE_ID

   AND P3.AREA_CODE IS NOT NULL) > 1

 

Dynamic SQL Error SQL error code = -104 Invalid expression in the select list 
(not contained in either an aggregate function or the GROUP BY clause)

Re: [firebird-support] SQL Optimation best way

['Arno Brinkman' fbsupp...@abvisie.nl [firebird-support]]

Hi,

You’re already have good suggestions, but mainly we’re missing input of what 
you exactly want and how the data is related.


Here another SQL example which could bring you by a solution:

SELECT
  SUM(a.Field3), dt.SumFieldC 
FROM
  TableA a
  LEFT JOIN (SELECT b.FieldA, b.FieldB, SUM(b.FieldC) AS SumFieldC 
   FROM TableB b GROUP BY b.FieldA, b.FieldB) dt ON (dt.FieldA = a.Field4 and 
dt.FieldB = a.Field5)
WHERE
  a.Field1 = 1 and a.Field2 = 2
GROUP BY
  dt.SumFieldC


Kind Regards,
Arno Brinkman
ABVisie

Re: [firebird-support] SQL Optimation best way

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Wed, Nov 4, 2015 at 8:41 AM, 'checkmail' check_m...@satron.de
[firebird-support]  wrote:

>
>
>  two tables
>
>
>
> A:
>
> Field1, Field2, Field3, Field4, Field5
>
>
>
> B
>
> FieldA,  FieldB, FieldC
>
>
>
> Now I would like to get the following:
>
>
>
> Sum(A.field3) where a.field1 = condition1 and a.field2 = condition2
>
> In the same statement I would include
>
> Sum(b.fieldc) where b.fielda = a.field4 and b.fieldb = a.field5
>
>
>

Without having tried it, I suggest:

select sum (A.field3) from A where A.field1 = condition1 and A.field2 =
condition2
UNION
select sum (b.fieldc) from B inner join A
 where  A.field1 = condition1
and A.field2 = condition2
and B.fielda = A.field4
and B.fieldb = A.field5

If A.field3 and B.fieldc are of different types you may need to cast them.

Good luck,

Ann



>

Re: [firebird-support] SQL Optimation best way

[setysvar setys...@gmail.com [firebird-support]]

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

These seems like two completely different queries with no correlation, 
so my guess is that the answer is no, you need a cte or something 
similar (e.g. execute block).

However, if A has a 1:many correlation to B, and you only want to count 
those B's that also match condition1 and condition2, then something like

select sum(A.field3), sum((select sum(b.fieldc) from B where b.fielda = 
a.field4 and b.fieldb = a.field5)) --you need double parenthesis and sum
from A
where a.field1 = :condition1 and a.field2 = :condition2

could be a solution (make sure to test so that you don't get double or 
triple of what you want from sum(B) if some records of A had the same 
value for field1, field2, field4 and field5).

I doubt this is a noticable optimization compared to cte or execute 
block, I think of it as an alternative suitable in some cases, but I 
neither expect it to be superior nor inferior in those cases.

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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] SQL Optimation best way

['checkmail' check_m...@satron.de [firebird-support]]

Hello @ll,

 

there are two tables

 

A

Field1

Field2

Field3

Field4

Field5

 

B

FieldA

FieldB

FieldC

 

Now I would like to get the following:

 

Sum(A.field3) where a.field1 = condition1 and a.field2 = condition2 

In the same statement I would include

Sum(b.fieldc) where b.fielda = a.field4 and b.fieldb = a.field5

 

It is possible without cte?

 

For example: select sum(a.field3) from tablea a (select sum(b.fieldc) where
b.fielda = a.field4 and b.fieldb = a.field5)

 

A.field1 and field2 are the intern project number, b.fielda and b.fieldb are
the extern ordernumber year and number. 

 

Thank you

 

 

Re: [firebird-support] SQL for TCC

[Robert martin r...@chreos.com [firebird-support]]

Sorry set the SQL to the wrong 'support' email.  Please ignore.

Thanks
Rob

On 27/08/2015 12:07 p.m., Robert martin r...@chreos.com 
[firebird-support] wrote:
> UPDATE CsItem
> SET YeRef = 7051
> WHERE ItemRef IN (688421, 688413, 688405, 688438, 688446)
>
> To run the above
> - On any ONE machine.
> - Go to Maintenance > Advanced > SQL Entry
> - Cut and paste the SQL into the 'SQL String to be processed' box
> - Click Process
> - The SQL has run so you can now close the screen
>
> --
> -
>
> Robert Martin
> Development Manager
>
> Ph 03 377-0495
> fax 03 377-0496
> email r...@chreos.com
>
> Wild Software Ltd
> www.chreos.com
>
> Please forward all support queries to supp...@chreos.com
> so we may respond to your query as quickly as possible.
>
> 
>
> No virus found in this message.
> Checked by AVG - www.avg.com 
> Version: 2015.0.6125 / Virus Database: 4409/10518 - Release Date: 08/26/15
>

[firebird-support] SQL for TCC

[Robert martin r...@chreos.com [firebird-support]]

UPDATE CsItem
SET YeRef = 7051
WHERE ItemRef IN (688421, 688413, 688405, 688438, 688446)


To run the above
- On any ONE machine.
- Go to Maintenance > Advanced > SQL Entry
- Cut and paste the SQL into the 'SQL String to be processed' box
- Click Process
- The SQL has run so you can now close the screen


-- 
-

Robert Martin
Development Manager


Ph 03 377-0495
fax03 377-0496
email  r...@chreos.com

Wild Software Ltd
www.chreos.com

Please forward all support queries to supp...@chreos.com
so we may respond to your query as quickly as possible.

Re: [firebird-support] SQL help, please

[brian br...@meadows.pair.com [firebird-support]]

On Sat, 30 May 2015 09:12:09 -0400, you wrote:

With thanks to Jonathan Finch, I think I have the problem sorted.
Substring comparisons are the answer (fairly obviously) but the bit I
was missing was the idea of wrapping the string of codes AND the
integer field in commas, to cope with the first and last values in the
string.

Brian.
>
>Hi folks, 
>
>I'm trying to help a friend out with writing a query, and it's giving
>me headaches. Can someone point me in the right direction, please? 
>
>Simplifying the problem, this is a two table database. The first table
>contains two fields, one is a character description, the other an
>integer code. The character description is what the end user keys in
>to do the search, and it has to work on partial strings. 
>
>The main database contains a character field which can contain an
>arbitrary number of these codes concatenated together as a
>comma-separated list - let's say 10 codes maximum, although I can't
>see that matters, and yes, it's possible for this field to be empty.
>These codes are not padded, so the character field could contain
>1,11,21,101   for example, and 1 must only match with 1, not with the
>other three. 
>
>What the query needs to do is a SELECT * on the main database where
>the field of concatenated codes contains any one of the zero or more
>codes returned by a subquery on the lookup table. 
>
>Can someone point me in the right direction, please? This is testing
>my (very rusty) SQL beyond breaking point. Yes, I know some functions
>can vary across different SQL implementations (the differences between
>Oracle and DB2 used to be a major curse when I was doing this sort of
>thing for a living ~20 years ago) but assume Firebird, I think I'm
>capable of sorting out any differences there may be between Firebird
>and the phone-based database that's actually being used. 
>
>Thanks, 
>
>Brian. 
>
>
>
>
>
>Posted by: brian 
>
>
>++
>
>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] SQL help, please

[brian br...@meadows.pair.com [firebird-support]]

Hi folks, 

I'm trying to help a friend out with writing a query, and it's giving
me headaches. Can someone point me in the right direction, please? 

Simplifying the problem, this is a two table database. The first table
contains two fields, one is a character description, the other an
integer code. The character description is what the end user keys in
to do the search, and it has to work on partial strings. 

The main database contains a character field which can contain an
arbitrary number of these codes concatenated together as a
comma-separated list - let's say 10 codes maximum, although I can't
see that matters, and yes, it's possible for this field to be empty.
These codes are not padded, so the character field could contain
1,11,21,101   for example, and 1 must only match with 1, not with the
other three. 

What the query needs to do is a SELECT * on the main database where
the field of concatenated codes contains any one of the zero or more
codes returned by a subquery on the lookup table. 

Can someone point me in the right direction, please? This is testing
my (very rusty) SQL beyond breaking point. Yes, I know some functions
can vary across different SQL implementations (the differences between
Oracle and DB2 used to be a major curse when I was doing this sort of
thing for a living ~20 years ago) but assume Firebird, I think I'm
capable of sorting out any differences there may be between Firebird
and the phone-based database that's actually being used. 

Thanks, 

Brian. 

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

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

[I've removed irrelevant details from your original question]

>I sincerely hope I am in the right place. We have a query which takes 4 
>minutes 30 seconds to execute this is simply
>too long. Please help !

Definitely, Stef, most performance problems can be solved on this list, and I 
agree that 4 and a half minute sounds long.

>CREATE TABLE JOURNAL (
>JDATE   DATE,
>SOURCE2 VARCHAR(40),
>ACCNR   INTEGER,
>SUPPID  INTEGER,
>JTYPE   INTEGER);

>CREATE INDEX JOURNAL_IDX1 ON JOURNAL (JDATE);
>CREATE INDEX JOURNAL_IDX13 ON JOURNAL (SUPPID);
>CREATE INDEX JOURNAL_IDX16 ON JOURNAL (SOURCE2);
>CREATE INDEX JOURNAL_IDX2 ON JOURNAL (JTYPE);
>CREATE INDEX JOURNAL_IDX3 ON JOURNAL (ACCNR);

>There are only 350,000 (350 Thousand)records in the table

>Below is my actual sql query
>
>select distinct
>sum((case (extract (year from jdate )||''||extract ( month from jdate ))
>when 20154 then (coalesce(invoicetotal,0)-coalesce(paid,0)) else 0 end))  as 
>Current_days,
>sum((coalesce(invoicetotal,0)-coalesce(paid,0)))as totaldue
> from (select (jdate)as jdate,
>(Select COALESCE(sum(debitamount),0 )
> from journal where accnr = '5995100'  and suppid=j.suppid and 
> source2 = j.source2 and jdate = j.jdate)as paid,
>(Select COALESCE(sum(creditamount),0 )
> from journal where accnr = '5995100'  and suppid=j.suppid and source2 
> = j.source2  and jdate = j.jdate)as invoicetotal
>   from journal j
>where j.jdate >= '2015/04/28'
>and j.accnr = 5995100
>and j.SUPPID = '1'
>and (j.jtype = 2 or j.jtype = 99))
>having sum((invoicetotal-paid))<>0
>order by  2 asc
>
>Please let me know if I should provide more information.

The generated plan could have given more hints as to what is wrong, but I've 
tried to rewrite your query anyway. So, could you see if the following query 
gets the same result and whether or not it is any quicker?

with DateSource(jdate, source2) as
(select distinct jdate, source2
 from journal
 where jdate >= '2015/04/28'
   and accnr = 5995100
   and SUPPID = 1
   and jtype in (2, 99)),
tmp(jdate, paid, invoicetotal) as
(select d.jdate, sum(debitamount), sum(creditamount)
 from journal j
 join DateSource d on j.jdate = d.jdate and j.source2 = d.source2
 where j.accnr = 5995100
   and j.SUPPID = 1
 group by 1)

select sum(iif(jdate between '2015/04/01' and '2015/04/30', invoicetotal - 
paid, 0) current_days,
   sum(invoicetotal - paid) totaldue
from tmp
having sum(invoicetotal - paid) <> 0
order by 2

It would be interesting to learn whether or not this query is any quicker, and 
if it isn't, the plans of both your original query and my modified version. 
Please also check whether the  modified query gets the correct result, there 
may be subleties that I missed.

HTH,
Set

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

['Stef' s...@autotech.co.za [firebird-support]]

I sincerely hope I am in the right place.


 


We have a query which takes 4 minutes 30 seconds to execute this is simply
too long. Please help !


 


I am going to list as much detail as possible


 


Firebird Version   =  WI-V2.5.2.26540 Firebird 2.5


Operating system = 32 bit Window 7 Ultimate  


4 gig ram 


Intel core 2  dual processor 2.4 Ghz


 


 


Below is the DDL for the table in question


 


SET SQL DIALECT 3;


 


SET NAMES NONE;


 


/***
***/


/Tables
/


/***
***/


 


 


CREATE TABLE JOURNAL (


JOURID  INTEGER,


JDATE   DATE,


JTYPE   INTEGER,


MODULE  INTEGER,


TRXDATE DATE,


SOURCEIDINTEGER,


SOURCE  VARCHAR(40),


DEBITAMOUNT CURRENCYVALUE /* CURRENCYVALUE = NUMERIC(18,2) DEFAULT
0.00 */,


DESCRIPTION VARCHAR(254),


STAFFID INTEGER,


STATUS  VARCHAR(30),


ACCNR   INTEGER,


ACCCLASSINTEGER,


LEDGERTYPE  VARCHAR(2),


CREDITAMOUNTCURRENCYVALUE /* CURRENCYVALUE = NUMERIC(18,2) DEFAULT
0.00 */,


DEPARTMENT  INTEGER,


COMPANY INTEGER,


SOURCE2 VARCHAR(40),


CID INTEGER,


VSIDINTEGER,


SUPPID  INTEGER,


DDATE   DATE,


SOURCENAME  VARCHAR(100),


FPERIOD VARCHAR(7),


RECONCILED  VARCHAR(5),


BATCH   INTEGER,


HASVAT  VARCHAR(5),


SERVICEADVISOR  STAFFNR /* STAFFNR = INTEGER */,


TRANSNR INTEGER


);


 


 


 


 


/***
***/


/   Indices
/


/***
***/


 


CREATE INDEX JOURNAL_IDX1 ON JOURNAL (JDATE);


CREATE INDEX JOURNAL_IDX11 ON JOURNAL (FPERIOD);


CREATE INDEX JOURNAL_IDX12 ON JOURNAL (RECONCILED);


CREATE INDEX JOURNAL_IDX13 ON JOURNAL (SUPPID);


CREATE INDEX JOURNAL_IDX14 ON JOURNAL (CID);


CREATE INDEX JOURNAL_IDX15 ON JOURNAL (BATCH);


CREATE INDEX JOURNAL_IDX16 ON JOURNAL (SOURCE2);


CREATE INDEX JOURNAL_IDX18 ON JOURNAL (TRANSNR);


CREATE INDEX JOURNAL_IDX2 ON JOURNAL (JTYPE);


CREATE INDEX JOURNAL_IDX3 ON JOURNAL (ACCNR);


CREATE INDEX JOURNAL_IDX4 ON JOURNAL (MODULE);


CREATE INDEX JOURNAL_IDX5 ON JOURNAL (STATUS);


CREATE INDEX JOURNAL_IDX6 ON JOURNAL (SOURCE);


 


There are only 350,000 (350 Thousand)records in the table


 


Below is my actual sql query


 


select distinct




 


sum((case (extract (year from jdate )||''||extract ( month from jdate ))
when 20154 then (coalesce(invoicetotal,0)-coalesce(paid,0)) else 0 end))  as
Current_days,


sum((coalesce(invoicetotal,0)-coalesce(paid,0)))as totaldue




 




 from (select (jdate)as jdate,


 


(Select COALESCE(sum(debitamount),0 )




 from journal where accnr = '5995100'  and suppid=j.suppid and
source2 = j.source2 and jdate = j.jdate)as paid,


 




(Select COALESCE(sum(creditamount),0 )




 from journal where accnr = '5995100'  and suppid=j.suppid and
source2 = j.source2  and jdate = j.jdate)as invoicetotal




 




   from journal j




 




where j.jdate >= '2015/04/28'


and j.accnr = 5995100


and j.SUPPID = '1'


and (j.jtype = 2 or j.jtype = 99)


  )




 


having sum((invoicetotal-paid))<>0




order by  2 asc


 


 


Please let me know if I should provide more information.


 


Regards


 


Stef


 


Email s...@autotech.co.za


 






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

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

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

>In a table called "Steps", I have the following fields:
>
>ID, B4Me, Dsc,
>
>-ID field contains a unique ID for each record
>-B4Me contains the ID of some other record in the table that MUST appear in a 
>result set, BEFORE this record. B4Me may be null. This is called the "B4Me" 
>order.
>-Records will be entered in random order, so Natural order cannot be relied 
>upon as a substitute for B4Me
>
>I need an SQL statement that will return all records with a user supplied 
>search word (such as "oil", or "glycol" or "micron") in the Dsc field, which 
>could return 
>a variable number of records, but what ever the number of records returned, 
>the *order* must be such that each record is preceded by the record who's ID 
>matches the B4Me value of a given record.&nbs p;
>
>Important Notes:
>
>- The SQL may return one or more records with a blank B4Me. In which case, 
>Natural order can be used for those records, and they are all considered 
>"First" or 
>rather, "Before everything else".  Once all the records with a blank B4Me are 
>listed, the remaining must be in the B4Me order.
>
>- It is possible, though unlikely, that following the B4Me order will result 
>in a circular reference. (Two or more records that reference each other, or 
>the last 
>record in a large result set, references the first (or any other in the result 
>set) record in the B4Me field).  In these cases, the SQL must either list all 
>records until 
>a circular reference is detected, then don't list the "offending" record, or 
>display an error message, but it must not hang FB and get an "out of memory" 
>error.
>
>Thanks in advance for any help you can provide.

The possibility of a circular reference, makes this a difficult task. SQL is 
great for working with sets of data, iteration and recursion are more 
difficult, but doable with CTEs or stored procedures, but I cannot think of how 
to exit a circular reference "gracefully" within one statement.

Hence, I'd recommend using a global temporary table:

CREATE GLOBAL TEMPORARY TABLE MyTmp
(ID INTEGER, OrderNo INTEGER)
ON COMMIT DELETE ROWS; --doesn't really matter whether you commit or delete 
rows in this particular case...

and then EXECUTE BLOCK or a STORED PROCEDURE;

EXECUTE BLOCK(MySearch varchar(50) = :MySearch) Returns(OutID Integer) as
  declare variable ID integer;
  declare variable ID2 integer;
  declare variable OrderNo integer;
begin
  delete from MyTmp; --start afresh every time you execute this block
  OrderNo = 1000;
  for select ID, B4Me
  from "Steps"
  where Dsc containing :MySearch
  into :ID, :ID2 do
  begin
while (:ID is not null) do
begin
  if exists(select * from MyTmp where ID = :ID) then
  begin  --This is how to escape from a circular reference
id = null;
  end
  else
  begin
INSERT INTO MyTmp(ID, OrderNo)
VALUES(:ID, :OrderNo);
OrderNo = OrderNo - 1;
ID = ID2;
if (ID is not null) then
begin
  SELECT B4Me from "Steps" where ID = :ID into :ID2;
end
  end
end
  end
  for select ID from MyTmp order by OrderNo into :OutID do
suspend;
end

I've interpreted "Before everything else" to mean "before any of its own 
children", not "before any children regardless of heritage" - or, to put it 
simple - you would appear before all your children, but not necessarily before 
my children.

Hopefully, this can serve as a template for how you can solve your situation, 
despite being more than one statement.
Set

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

[liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

or better for “The SQL may return one or more records with a blank B4Me. In 
which case, Natural order can be used for those records, and they are all 
considered "First" or rather, "Before everything else”

select  CASE WHEN B4ME IS NULL THEN 0 ELSE 1 END,  CASE WHEN B4ME IS NULL THEN 
ID ELSE B4ME+1 END, other_fields
FROM
STEPS
ORDER BY 1 ASC, 2 ASC

regards,
Karol Bieniaszewski

From: mailto:firebird-support@yahoogroups.com 
Sent: Saturday, April 11, 2015 10:03 AM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] SQL puzzle: Order based on Prerequisite

  

Hi,

you must rethink what you try to retrive. What is then sense of returning 
somethink in case of cycles?
If you have cycles then you do not get something before..

But if you need something strange then it is simple

ID as integer

select CASE WHEN B4ME IS NULL THEN ID ELSE B4ME+1 END, other_fields
FROM
STEPS
ORDER BY 1 ASC

but with cycles i will prefere recursive cte or stored proc and make 
possibility of duplicates in resultset

regards,
Karol Bieniaszewski



From: mailto:firebird-support@yahoogroups.com 
Sent: Wednesday, April 08, 2015 4:28 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] SQL puzzle: Order based on Prerequisite

  

In a table called "Steps", I have the following fields:




ID, B4Me, Dsc, 





-ID field contains a unique ID for each record

-B4Me contains the ID of some other record in the table that MUST appear in a 
result set, BEFORE this record. B4Me may be null. This is called the "B4Me" 
order.


-Records will be entered in random order, so Natural order cannot be relied 
upon as a substitute for B4Me





I need an SQL statement that will return all records with a user supplied 
search word (such as "oil", or "glycol" or "micron") in the Dsc field, which 
could return a variable number of records, but what ever the number of records 
returned, the *order* must be such that each record is preceded by the record 
who's ID matches the B4Me value of a given record.&nb sp; 





Important Notes:




- The SQL may return one or more records with a blank B4Me. In which case, 
Natural order can be used for those records, and they are all considered 
"First" or rather, "Before everything else".  Once all the records with a blank 
B4Me are listed, the remaining must be in the B4Me order.




- It is possible, though unlikely, that following the B4Me order will result in 
a circular reference. (Two or more records that reference each other, or the 
last record in a large result set, references the first (or any other in the 
result set) record in the B4Me field).  In these cases, the SQL must either 
list all records until a circular reference is detected, then don't list the 
"offending" record, or display an error message, but it must not hang FB and 
get an "out of memory" error./ p> 




Thanks in advance for any help you can provide.

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

[liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

you must rethink what you try to retrive. What is then sense of returning 
somethink in case of cycles?
If you have cycles then you do not get something before..

But if you need something strange then it is simple

ID as integer

select CASE WHEN B4ME IS NULL THEN ID ELSE B4ME+1 END, other_fields
FROM
STEPS
ORDER BY 1 ASC

but with cycles i will prefere recursive cte or stored proc and make 
possibility of duplicates in resultset

regards,
Karol Bieniaszewski



From: mailto:firebird-support@yahoogroups.com 
Sent: Wednesday, April 08, 2015 4:28 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] SQL puzzle: Order based on Prerequisite

  

In a table called "Steps", I have the following fields:




ID, B4Me, Dsc, 





-ID field contains a unique ID for each record

-B4Me contains the ID of some other record in the table that MUST appear in a 
result set, BEFORE this record. B4Me may be null. This is called the "B4Me" 
order.


-Records will be entered in random order, so Natural order cannot be relied 
upon as a substitute for B4Me





I need an SQL statement that will return all records with a user supplied 
search word (such as "oil", or "glycol" or "micron") in the Dsc field, which 
could return a variable number of records, but what ever the number of records 
returned, the *order* must be such that each record is preceded by the record 
who's ID matches the B4Me value of a given record.&nb sp; 





Important Notes:




- The SQL may return one or more records with a blank B4Me. In which case, 
Natural order can be used for those records, and they are all considered 
"First" or rather, "Before everything else".  Once all the records with a blank 
B4Me are listed, the remaining must be in the B4Me order.




- It is possible, though unlikely, that following the B4Me order will result in 
a circular reference. (Two or more records that reference each other, or the 
last record in a large result set, references the first (or any other in the 
result set) record in the B4Me field).  In these cases, the SQL must either 
list all records until a circular reference is detected, then don't list the 
"offending" record, or display an error message, but it must not hang FB and 
get an "out of memory" error. 




Thanks in advance for any help you can provide.

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

[firebirdbest...@yahoo.com [firebird-support]]

In a table called "Steps", I have the following fields:
 

 ID, B4Me, Dsc, 

 

 -ID field contains a unique ID for each record
 -B4Me contains the ID of some other record in the table that MUST appear in a 
result set, BEFORE this record. B4Me may be null. This is called the "B4Me" 
order.

 -Records will be entered in random order, so Natural order cannot be relied 
upon as a substitute for B4Me

 

 I need an SQL statement that will return all records with a user supplied 
search word (such as "oil", or "glycol" or "micron") in the Dsc field, which 
could return a variable number of records, but what ever the number of records 
returned, the *order* must be such that each record is preceded by the record 
who's ID matches the B4Me value of a given record.  

 

 Important Notes:
 

 - The SQL may return one or more records with a blank B4Me. In which case, 
Natural order can be used for those records, and they are all considered 
"First" or rather, "Before everything else".  Once all the records with a blank 
B4Me are listed, the remaining must be in the B4Me order.
 

 - It is possible, though unlikely, that following the B4Me order will result 
in a circular reference. (Two or more records that reference each other, or the 
last record in a large result set, references the first (or any other in the 
result set) record in the B4Me field).  In these cases, the SQL must either 
list all records until a circular reference is detected, then don't list the 
"offending" record, or display an error message, but it must not hang FB and 
get an "out of memory" error.
 

 Thanks in advance for any help you can provide.

 

 

RE: [firebird-support] SQL Creation question

[michael.vilhelm...@microcom.dk [firebird-support]]

So simple.  Thank you

RE: [firebird-support] SQL Creation question

['Norbert Saint Georges' n...@tetrasys.eu [firebird-support]]

Select * from group, supplier

 

 

   Norbert Saint Georges

 

   TetraSys Oy

   Bergantie 69, FI-02540 Kylmälä

   Tel. : +358 (0) 400 27 25 18

   E- mail : n...@tetrasys.fi <mailto:taru.kaukova...@tetrasys.eu> 

 

De : firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Envoyé : mercredi 1 avril 2015 12:18
À : firebird-support@yahoogroups.com
Objet : [firebird-support] SQL Creation question

 

  

Hi

 

I have two tables.

They are not related in anyway.

 

One table contains groups and the other contains suppliers

 

Lets say they have this content

 

GROUP

=

Group 1

Group 2

 

 

 

SUPPLIER



Supplier 1

Supplier 2

 

 

Can I create a SELECT statement, which will return every combination of the two 
tabels?

Here it should be:

 

 

Group 1, Supplier 1

Group 1, Supplier 2

Group 2, Supplier 1

Group 2, Supplier 2

 

 

Regards

Michael

 

[firebird-support] SQL Creation question

[michael.vilhelm...@microcom.dk [firebird-support]]

Hi
 

 I have two tables.
 They are not related in anyway.
 

 One table contains groups and the other contains suppliers
 

 Lets say they have this content
 

 GROUP
 =
 Group 1
 Group 2
 

 

 

 SUPPLIER
 
 Supplier 1
 Supplier 2
 

 

 Can I create a SELECT statement, which will return every combination of the 
two tabels?
 Here it should be:
 

 

 Group 1, Supplier 1
 Group 1, Supplier 2
 Group 2, Supplier 1
 Group 2, Supplier 2
 

 

 Regards
 
 Michael
 

Re: [firebird-support] SQL Error -104

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

>i just don't get it what the heck i'm doing wrong. I would like to have
>something like that:
>
>select ba.artno
>from mov_invoices mi, bas_articles ba
>where mi.invdate between '2014-05-01 00:00:00' and '2014-08-01 00:00:00'
>and case
>when (ba.artno like '90__') then '9000'
>when (ba.artno like '80__') then '8000'
>when (ba.artno like '70__') then '7000'
>when (ba.artno like '60__') then '6000'
>when (ba.artno like '50__') then '5000'
>when (ba.artno like '40__') then '4000'
>when (ba.artno like '30__') then '3000'
>when (ba.artno like '20__') then '2000'
>when (ba.artno like '10__') then '1000'
>end
>
>any ideas somebody?

The reason this fails, Johannes, is that all parts a the WHERE clause should 
evaluate to a Boolean value and your query doesn't.

mi.invdate between '2014-05-01 00:00:00' and '2014-08-01 00:00:00' will 
evaluate to true or false (or null), but you have no equality, between or 
similar for your case statement (you have only specified one side of the 
comparison - it's the same as specifying mi.invdate and forgetting between and 
the dates).

Also, there's no need for you to use CASE in your case. Try something like:

select ba.artno
from bas_articles ba
join mov_invoices mi on substring(ba.artno from 1 for 2) || '00' = 
mi.
where mi.invdate between '2014-05-01 00:00:00' and '2014-08-01 00:00:00'

I'm guessing that your case statement creates a value that should be compared 
to a value in mov_invoices, and that you do not want to return articles not 
related to the invoice. If I'm wrong, replace JOIN with CROSS JOIN, remove the 
ON part and move the substring to the WHERE clause.

HTH,
Set

Odp: [firebird-support] SQL Error -104

['liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

Better will be if you include error message instead of code only
i think that problem can be with timestamp is your field date or timestamp?

And also is your case statement full? Can contain values like 0101?
Better will be using substring + PAD instead of case statement

Regards,
Karol Bieniaszewski

- Reply message -
Od: "Johannes Grimm johannes.g...@gmail.com [firebird-support]" 

Do: 
Temat: [firebird-support] SQL Error -104
Data: pt., wrz 5, 2014 10:47
Hi folks,



i just don't get it what the heck i'm doing wrong. I would like to have 

something like that:



select ba.artno

from mov_invoices mi, bas_articles ba

where mi.invdate between '2014-05-01 00:00:00' and '2014-08-01 00:00:00'

and case

when (ba.artno like '90__') then '9000'

when (ba.artno like '80__') then '8000'

when (ba.artno like '70__') then '7000'

when (ba.artno like '60__') then '6000'

when (ba.artno like '50__') then '5000'

when (ba.artno like '40__') then '4000'

when (ba.artno like '30__') then '3000'

when (ba.artno like '20__') then '2000'

when (ba.artno like '10__') then '1000'

end



any ideas somebody?



greets johannes

RE: [firebird-support] SQL Error -104

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> i just don't get it what the heck i'm doing wrong. I would like to have
> something like that:
> 
> select ba.artno
> from mov_invoices mi, bas_articles ba
> where mi.invdate between '2014-05-01 00:00:00' and '2014-08-01 00:00:00'
> and case
> when (ba.artno like '90__') then '9000'
> when (ba.artno like '80__') then '8000'
> when (ba.artno like '70__') then '7000'
> when (ba.artno like '60__') then '6000'
> when (ba.artno like '50__') then '5000'
> when (ba.artno like '40__') then '4000'
> when (ba.artno like '30__') then '3000'
> when (ba.artno like '20__') then '2000'
> when (ba.artno like '10__') then '1000'
> end

Try

select 
  case
  when (ba.artno like '90%') then '9000'
  when (ba.artno like '80%') then '8000'
  when (ba.artno like '70%') then '7000'
  when (ba.artno like '60%') then '6000'
  when (ba.artno like '50%') then '5000'
  when (ba.artno like '40%') then '4000'
  when (ba.artno like '30%') then '3000'
  when (ba.artno like '20%') then '2000'
  when (ba.artno like '10%') then '1000'
end
  as ba.artno
from mov_invoices mi, bas_articles ba
where 
  mi.invdate between '2014-05-01 00:00:00' and '2014-08-01 00:00:00'


Sean

[firebird-support] SQL Error -104

[Johannes Grimm johannes.g...@gmail.com [firebird-support]]

Hi folks,


i just don't get it what the heck i'm doing wrong. I would like to have 
something like that:


select ba.artno
from mov_invoices mi, bas_articles ba
where mi.invdate between '2014-05-01 00:00:00' and '2014-08-01 00:00:00'
and case
when (ba.artno like '90__') then '9000'
when (ba.artno like '80__') then '8000'
when (ba.artno like '70__') then '7000'
when (ba.artno like '60__') then '6000'
when (ba.artno like '50__') then '5000'
when (ba.artno like '40__') then '4000'
when (ba.artno like '30__') then '3000'
when (ba.artno like '20__') then '2000'
when (ba.artno like '10__') then '1000'
end


any ideas somebody?


greets johannes

Re: [firebird-support] SQL Log/Rollback?

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

> On Jun 22, 2014, at 11:35 PM, "Jack Cane jwc...@enwphotos.com 
> [firebird-support]"  wrote:
> 
> Please point me to documentation where I can read further about whether 
> Firebird has the ability to roll back to a previous state, and/or 
> location/name of a log that can be queried for this purpose.

Firebird does not currently have the ability to undo committed transactions.  
You can use NBackup with incremental backup levels to restore an old version 
and apply increments to the time desired.  

Good luck,

Ann

[firebird-support] SQL Log/Rollback?

[Jack Cane jwc...@enwphotos.com [firebird-support]]

Please point me to documentation where I can read further about whether 
Firebird has the ability to roll back to a previous state, and/or location/name 
of a log that can be queried for this purpose.

Best,
jwc

[firebird-support] SQL query aborts when SQL statement is too long or complex.

[hosodaamiya]

Hi all.

SQL query aborts when SQL statement is too long or complex.
Firebird seems to fail to handle the error of complecated SQL statement
when an error occurs at fbembedde.dll.
SQL query that includes IN operator with multiple columns and
OR conditons have been aborted in our development environment.
What is the cause of forced termination?

error:

http://schemas.microsoft.com/win/2004/08/events/event";>


1000
2
100
0x80

3463
Application
WIN-SAH55JS7A8C



w3wp.exe
7.5.7601.17514
4ce7afa2
fbembed.DLL
2.5.1.26351
4e89609d
c0fd
0015090a



The embedded DB is accessed via .NetFramework Web service in IIS.

Environment information:
OS:win2008R2Server
IIS:7.5
fbembed.dll:2.5.1.26351
FirebirdSql.Data.FirebirdClient.dll:2.6.0
.NetFramework:3.5SP1

RE: [firebird-support] SQL error code = -104 Unexpected end of command, from Delphi App

[Maya Opperman]

1.   Your insert is failing, but you haven’t provided us with the SQL you 
are using for an insert. Also, check the refresh SQL property of your DB 
component.

2.   I notice you have an ; at the end of all your SQL. I don’t think that 
is necessary when entering the SQL into the SQL property of your Delphi 
component (it isn’t for the IBXpress and FIBPlus components). You might be 
managing to confuse it somehow with that.

3.   You are correct, when passing parameters as variables in Delphi, you 
don’t need to quote the text to cater for apostrophes. If you were to have 
built your SQL inline, without passing parameters, THEN you would have had to 
do that. So, the way your are working is best there, so you don’t have to worry 
about that.

4.   I see your are using ShowMessage() to debug. Not sure if that was just 
to describe it simply to us, or if your are not using Delphi debugger (F,7 F8) 
to step through your code. If you aren’t using F7/F8, you’ll find the problem a 
lot faster if you take some time to learn how to use the debugger, and do away 
with the showMessage() method.

5.   You might find it a lot easier to locate the problem if you use a SQL 
monitor. Then you’ll see the exact SQL statement that is failing, and can go 
look at what is character 30 of line 1. I think IBExpert has a SQL monitor, or 
your Delphi component set might also have a component you can use to create 
your own SQL monitor.
Cheers
Maya

Re: [firebird-support] SQL error code = -104 Unexpected end of command, from Delphi App

[homer]

Thanks for the advise. I sincerely appreciate it. In my defense, this Delphi 
application has just shy of 900,000 lines of my code. The Data Module alone has 
115 TIBCQueries. As you might imagine, they all successfully execute Insert, 
Post, Open, and Close commands more times than I can count. None of them 
require an apostrophe from my code. Naturally, that doesn't mean the component 
doesn't have a syntax error somewhere, but one would think that would show up 
in one of the other 114 components. 
 

 I half-way expected to be told to stop bothering this group because I 
mentioned Delphi. I also expect to be told the same thing by folks in Delphi 
groups because I mentioned Firebird. I just thought there might be fewer 
Firebird users in a Delphi group and more Delphi users in a Firebird group. I 
didn't mean to imply that this was a Firebird problem. I knew it wasn't. I only 
hoped that some other Delphi/Firebird user might have solved this problem. I 
also know that it is important to keep inappropriate messages out of the group. 
Message received. I'll go away.:-)

Re: [firebird-support] SQL error code = -104 Unexpected end of command, from Delphi App

[Helen Borrie]

At 10:37 a.m. 9/01/2014, ho...@agencybusys.com wrote:


>I looked through previous topics, and although this seems like a common error, 
>I didn't find one quite like my situation.

You realise this is a problem with your Delphi client code, don't you?  And 
that this list is not a Delphi support list.


>I'm using a TIBCQuery in Delphi 2007. 
>
>SQL.Text := 'Select * From AR ORDER BY DueDate, TransDate, PolNum;';
>
>The error occurs when I issue an Insert, or Close command.

Insert and Close are Delphi methods.  So is Post which, in most data access 
components, should be called after the data for an Insert or Update operation 
has been assigned.


>Actual Error: Dynamic SQL Error SQL error code = -104 Unexpected end of 
>command - line 1, column 30

This is a Firebird exception, that occurs when client code issues an incomplete 
SQL statement, which can come about from mishandling of the single-quoting of 
strings.  You need an SQL monitoring tool or a Delphi debug tool to see what 
this invalid statement is.

>I have backed up and restored the database just in case it was some kind of 
>corruption. 

The error is occurring somewhere in your Delphi code.  A statement being issued 
to the server is terminating early because the parser got to "something" in a 
statement that prevented it from making sense of the whole thing. A very common 
cause is mishandling of the quoting of strings, most especially apostrophes.

>The problem persisted after that. It is possible to display and edit data in 
>an DevEx Express Grid. The problem only seems to exist when I try to Insert 
>from Delphi.
>
>Actual Delphi Code (qAR is the TIBCQuery):
>
>  With DM.qAR do
>
>  begin
>
>iCarrierNo := FieldByName('CarrierNo').AsInteger;
>
>sCarrier := FieldByName('Carrier').AsString;
>
>sInvoiceNo := FieldByName('InvoiceNo').AsString;
>
> 
>
>ShowMessage('1');
>
>Insert;
>
>ShowMessage('2');
>
>  {Duplicate AR field values}
>
>  FieldByName('CarrierNo').AsInteger := iCarrierNo;

// Protect against the occurrence of a name like O'Brien or Johnson's Removals:
  sCarrier := QuotedStr(sCarrier);


>  FieldByName('Carrier').AsString := sCarrier;

Try the same with sInvoiceNo if there is any possibility that the string 
contains apostrophes.


>  FieldByName('InvoiceNo').AsString :=  sInvoiceNo ;
>
>and so on
>
>   
>
>It gets as far as the Showmessage('1'), but does not reach Showmessage('2'). 
>
>
>I hope I've given enough info for you to understand what's going on. I've 
>tried what I know, which apparently isn't much.

 Moderator Hat ON: This time it was sort-of OK to use this support list because 
you didn't seem to recognise that it wasn't a Firebird problem and you *did* 
get an exception from the database.  Don't make it a habit to use this list for 
Delphi problems, though. I don't know where TIBCQuery comes from but most 
third-party data access components have their own support lists.


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__ 

[firebird-support] SQL error code = -104 Unexpected end of command, from Delphi App

[homer]

I looked through previous topics, and although this seems like a common error, 
I didn't find one quite like my situation. I'm using a TIBCQuery in Delphi 
2007.  SQL.Text := 'Select * From AR ORDER BY DueDate, TransDate, PolNum;'; The 
error occurs when I issue an Insert, or Close command. Actual Error: Dynamic 
SQL Error SQL error code = -104 Unexpected end of command - line 1, column 30 
 I have backed up and restored the database just in case it was some kind of 
corruption. The problem persisted after that. It is possible to display and 
edit data in an DevEx Express Grid. The problem only seems to exist when I try 
to Insert from Delphi. Actual Delphi Code (qAR is the TIBCQuery):
   With DM.qAR do   begin iCarrierNo := FieldByName('CarrierNo').AsInteger; 
sCarrier := FieldByName('Carrier').AsString; sInvoiceNo := 
FieldByName('InvoiceNo').AsString;   ShowMessage('1'); Insert; 
ShowMessage('2');   {Duplicate AR field values}   
FieldByName('CarrierNo').AsInteger := iCarrierNo;   
FieldByName('Carrier').AsString := sCarrier;   
FieldByName('InvoiceNo').AsString :=  sInvoiceNo ; and so on
 It gets as far as the Showmessage('1'), but does not reach Showmessage('2'). 
 

 

 I hope I've given enough info for you to understand what's going on. I've 
tried what I know, which apparently isn't much.
 

 Thanks in advance. 

AW: [firebird-support] sql and firebird

[checkmail]

Hello Mark,
it is very simple, I know. But sometimes... 
Thank you very much, best regards.
Olaf

On 4-11-2013 15:52, checkmail wrote:
>
>
> Hello,
>
> I would update a table (Primary key year and number), all records with 
> the same ordernumber for example - but not the record where is 
> currently in use and already updatet.
>
> Update table set field = value where ordernumber = XXX and (not record 
> with year 2013 and Number 1)

You just need to use the right syntax for the condition:

Update table set field = value
where ordernumber = XXX
and not (year = 2013 and Number = 1)

Mark
--
Mark Rotteveel




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

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

++
Yahoo Groups Links

Re: [firebird-support] sql and firebird

[Mark Rotteveel]

On 4-11-2013 15:52, checkmail wrote:
>
>
> Hello,
>
> I would update a table (Primary key year and number), all records with
> the same ordernumber for example – but not the record where is currently
> in use and already updatet.
>
> Update table set field = value where ordernumber = XXX and (not record
> with year 2013 and Number 1)

You just need to use the right syntax for the condition:

Update table set field = value
where ordernumber = XXX
and not (year = 2013 and Number = 1)

Mark
-- 
Mark Rotteveel




++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

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

++
Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
http://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] sql and firebird

[checkmail]

Hello,

I would update a table (Primary key year and number), all records with the
same ordernumber for example - but not the record where is currently in use
and already updatet.

 

Update table set field = value where ordernumber = XXX and (not record with
year 2013 and Number 1)

How can I realize this?

 

Best thanks.

 

Regards

Olaf

 

 

Re: [firebird-support] SQL ignores spaces?

[Frank]

On 04/24/2013 12:44 PM, uwekeim wrote:
> Hello, 
> 
> i have a table with a field F1 sized VARCHAR(1).
> 
> Some values are '' (field is empty).
> 
> Astonishing following SQLs produce the same result:
> 
> select * from test t where t.f1=''
> 
> or 
> 
> select * from test t where t.f1=' '
> 
> or 
> 
> select * from test t where t.f1='   '
> 
> No matter how many spaces i insert into the sql, the result presents all 
> datasets where field is empty.
> 
> I testet this with several databases and several Firebird versions: 1.5, 2.1 
> and 2.5 (all 32 bit). Result is allways the same.
> 
> In my oppinion this is a bug, isnt it? Only the first SQL shoud return the 
> datasets, where F1 is empty. Or am i wrong? 


>From the SQL1992 Standard:

>  3) The comparison of two character strings is determined as fol-
> lows:
> 
> a) If the length in characters of X is not equal to the length
>   in characters of Y, then the shorter string is effectively
>   replaced, for the purposes of comparison, with a copy of
>   itself that has been extended to the length of the longer
>   string by concatenation on the right of one or more pad char-
>   acters, where the pad character is chosen based on CS. If
>   CS has the NO PAD attribute, then the pad character is an
>   implementation-dependent character different from any char-
>   acter in the character set of X and Y that collates less
>   than any string under CS. Otherwise, the pad character is a
>   .

in other words, before comparison, the shorter string is padded with
"pad-character" (usually a space) to the length of the longer string.

So, it's not a bug, but a SQL-standards feature.

been there too some years ago :-)

fsg

-- 
"Fascinating creatures, phoenixes, they can carry immensely heavy loads,
  their tears have healing powers and they make highly faithful pets."
  - J.K. Rowling

[firebird-support] SQL ignores spaces?

[uwekeim]

Hello, 

i have a table with a field F1 sized VARCHAR(1).

Some values are '' (field is empty).

Astonishing following SQLs produce the same result:

select * from test t where t.f1=''

or 

select * from test t where t.f1=' '

or 

select * from test t where t.f1='   '

No matter how many spaces i insert into the sql, the result presents all 
datasets where field is empty.

I testet this with several databases and several Firebird versions: 1.5, 2.1 
and 2.5 (all 32 bit). Result is allways the same.

In my oppinion this is a bug, isnt it? Only the first SQL shoud return the 
datasets, where F1 is empty. Or am i wrong? 

Re: AW: [firebird-support] sql error

[Mark Rotteveel]

On 20-3-2013 11:20, Olaf Kluge wrote:
> I think it's a problem of ibexpert Version 2009.  Although I can select the
> fb 2.5 version, but while debugging I get this error. If I execute the sp,
> it works.

Debugging a stored procedure is not identical to actual execution 
because Firebird doesn't actually support debugging. IB Expert is 
simulating the execution of the stored procedure, and apparently it is 
doing something wrong with that.

Mark
-- 
Mark Rotteveel

AW: [firebird-support] sql error

[Olaf Kluge]

Hi,

 

Thanks you and Paul.

 

I think it's a problem of ibexpert Version 2009.  Although I can select the
fb 2.5 version, but while debugging I get this error. If I execute the sp,
it works.

 

Before I check the count of any Prio, I have a look for any free storage
compartments.

 

Thank you for the suggestions. 

 

Best regards.

 

Olaf

>But now I get: Token unknown, column 1 line 24 (

Without seeing at least 24 lines of code, it is hard to tell why it doesn't
understand the parenthesis at the beginning of line 24 ;o)

>with this code I get an sql error, but it works until today perfect :o(

What has changed? Have you upgraded anything?

>From what you've shown so far, I'd change to:

with wgetnextprio as 
(select prio, count(*) as kount 
from tfachzuordnung
where le is null 
and gesperrt = 0
and elementtyp = 1 
and paltyp = :paltyp_anfrage 
group by prio
order by 2 desc
rows 1)
select prio 
from wgetnextprio 
into :prio_to_find;

select fachnr 
from tfachzuordnung 
where le is null 
and gesperrt = 0 
and elementtyp = 1 
and paltyp = :paltyp_Anfrage
and dispo = 0 
and prio = :prio_to_find
order by fachnr
rows 1
into :ziel_einl;

(or possibly mix them together if you don't need prio_to_find later). I note
that only the last select includes dispo, I assume you've checked that the
content of ziel_einl is as desired if the record with the highest kount
doesn't have any row with dispo = 0.

HTH,
Set





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

RE: [firebird-support] sql error

[Svein Erling Tysvær]

>But now I get: Token unknown, column 1 line 24 (

Without seeing at least 24 lines of code, it is hard to tell why it doesn't 
understand the parenthesis at the beginning of line 24 ;o)

>with this code I get an sql error, but it works until today perfect :o(

What has changed? Have you upgraded anything?

>From what you've shown so far, I'd change to:

with wgetnextprio as 
(select prio, count(*) as kount 
 from tfachzuordnung
 where le is null 
   and gesperrt = 0
   and elementtyp = 1 
   and paltyp = :paltyp_anfrage 
 group by prio
 order by 2 desc
 rows 1)
select prio 
from wgetnextprio 
into :prio_to_find;

select fachnr 
from tfachzuordnung 
where le is null 
  and gesperrt = 0 
  and elementtyp = 1 
  and paltyp = :paltyp_Anfrage
  and dispo = 0 
  and prio = :prio_to_find
order by fachnr
rows 1
into :ziel_einl;

(or possibly mix them together if you don't need prio_to_find later). I note 
that only the last select includes dispo, I assume you've checked that the 
content of ziel_einl is as desired if the record with the highest kount doesn't 
have any row with dispo = 0.

HTH,
Set

Re: [firebird-support] sql error

[Mercea Paul]

On 2013.03.20 11:34 AM, Olaf Kluge wrote:
> select first 1 prio from wgetnextprio where kount = (select max(kount)
> from wgetnextprio)into :prio_to_find;
I can't say anything about your error but i think this query can get 
better results :

select first 1 prio from wgetnextprio
order by kount desc
into :prio_to_find;


-- 
Paul MERCEA
___

E-mail: paul.mer...@almexa.ro

___

[firebird-support] sql error

[Olaf Kluge]

Hello,

 

with this code I get an sql error, but it works until today perfect :o(

 

with wgetnextprio as (select prio, count(*) as kount from tfachzuordnung
where le is null and gesperrt = 0

and elementtyp = 1 and paltyp = :paltyp_anfrage group by prio)

select first 1 prio from wgetnextprio where kount = (select max(kount)
from wgetnextprio)into :prio_to_find;

select first 1 fachnr from tfachzuordnung a where a.elementtyp = 1 and
a.paltyp = :paltyp_Anfrage

and a.dispo = 0 and prio = :prio_to_find and a.le is null and a.gesperrt
= 0 order by fachnr

into :ziel_einl;

 

paltyp_anfrage = 2. If I execute the first part, I get: Prio A 1 piece
available, Prio B 1 piece available, Prio C 1 piece available.

 

But now I get: Token unknown, column 1 line 24 (

 

What can be wrong?

 

Thanks.

 

BR

Olaf

 



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

Re: AW: [firebird-support] sql query in firebird

[Nagy Szilveszter]

Hey there,


i did not follow the beginning of this thread but this looks like a proper SQL 
query...and it should have nothing to do with the ODBC driver, because the 
driver just sends the query to the server and the server will run it.

Could you please provide the error message you receive at running this query?

And surely you can put this query in an SP but it should work also the way you 
wrote it.


Szilvi





 From: Olaf Kluge 
To: firebird-support@yahoogroups.com 
Sent: Tuesday, February 12, 2013 12:39 PM
Subject: AW: [firebird-support] sql query in firebird
 

  
Hello again,

now I have test it with a pass through query and it works!

Thanks!

Hello Sean

Thanks a lot!

Unfortunately the gemini interbase odbc driver works not with this union
query. Even not if I create 3 single queries and call this in a union query.
Is there an easy way to put them into a stored procedure without duplicates?
Then I can get the return values by calling this stored procedure.

Thank you.

SELECT 

a.empf_sbnr, b.email

FROM tbestellungen a

join tsachbearbeiter b on a.empf_sbnr = b.sbnr

WHERE a.status = 1 and b.email is not null

UNION

SELECT 

b.bauleiter as sbnr, e.email

FROM tprojekte b

join tsachbearbeiter e on b.bauleiter = e.sbnr

JOIN tbestellungen a ON b.kommisionsnr = a.kommisionsnr and b.auftrag =
a.auftrag

where e.email is not null

UNION

SELECT 

c.sbnr, f.email

FROM tprojversand c

join tsachbearbeiter f on c.sbnr = f.sbnr

JOIN tbestellungen a ON c.kommisionsnr = a.kommisionsnr and c.auftrag =
a.auftrag

where f.email is not null

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

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


 

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

AW: [firebird-support] sql query in firebird

[Olaf Kluge]

Hello again,

 

now I have test it with a pass through query and it works!

 

Thanks!

 

Hello Sean

Thanks a lot!

Unfortunately the gemini interbase odbc driver works not with this union
query. Even not if I create 3 single queries and call this in a union query.
Is there an easy way to put them into a stored procedure without duplicates?
Then I can get the return values by calling this stored procedure.

Thank you.

SELECT 

a.empf_sbnr, b.email

FROM tbestellungen a

join tsachbearbeiter b on a.empf_sbnr = b.sbnr

WHERE a.status = 1 and b.email is not null

UNION

SELECT 

b.bauleiter as sbnr, e.email

FROM tprojekte b

join tsachbearbeiter e on b.bauleiter = e.sbnr

JOIN tbestellungen a ON b.kommisionsnr = a.kommisionsnr and b.auftrag =
a.auftrag

where e.email is not null

UNION

SELECT 

c.sbnr, f.email

FROM tprojversand c

join tsachbearbeiter f on c.sbnr = f.sbnr

JOIN tbestellungen a ON c.kommisionsnr = a.kommisionsnr and c.auftrag =
a.auftrag

where f.email is not null

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





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

AW: [firebird-support] sql query in firebird

[Olaf Kluge]

Hello Sean

 

Thanks a lot!

 

Unfortunately the gemini interbase odbc driver works not with this union
query. Even not if I create 3 single queries and call this in a union query.
Is there an easy way to put them into a stored procedure without duplicates?
Then I can get the return values by calling this stored procedure.

 

Thank you.

 

SELECT 

a.empf_sbnr, b.email

FROM tbestellungen a

join tsachbearbeiter b on a.empf_sbnr = b.sbnr

WHERE a.status = 1 and b.email is not null

 

UNION

 

SELECT 

b.bauleiter as sbnr, e.email

FROM tprojekte b

join tsachbearbeiter e on b.bauleiter = e.sbnr

JOIN tbestellungen a  ON b.kommisionsnr = a.kommisionsnr and b.auftrag =
a.auftrag

where e.email is not null

 

UNION

 

SELECT 

c.sbnr, f.email

FROM tprojversand c

join tsachbearbeiter f on c.sbnr = f.sbnr

JOIN tbestellungen a  ON c.kommisionsnr = a.kommisionsnr and c.auftrag =
a.auftrag

where f.email is not null



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

RE: [firebird-support] sql query in firebird

[Leyne, Sean]

Olaf,

>  fb 2.1 cs is running
> 
>  Now I have four tables:
> 
> Persons:
> ID
> Name
> EMAIL
> 
> ORDER:
> ProjectID
> PERSON_WHO_GET (references ID from Persons)
> STATUS
> 
> Projects:
> ProjectID
> Manager (references ID from Persons)
> 
> ProjectSEND:
> ProjectID
> PERSON_WHO_GET_INFO (references ID from Persons)
> 
> Each Person (ID) is unique, each ProjectID too. Every order has one
> person_who_get, one Project has 1:n orders. In ProjectSend can I configure
> many persons who get an email for one Project.
> 
> Starting with the order table and its status (open), I can reference to the
> project, than to the projectSend-Table. I would get all Persons (ID) grouped,
> which are deposited with the order, the project (in dependence on
> procect) and who is configurated to became an email (ProjectSend)
> 
> 
> For example:
> 
> Order 123
> Person who get Order: 2
> For Projekct ABC
> ProjectManager for ABC: 23
> 
> ProjectSend f
> Project: ABC, Person_who_get_info: 32
> Project: ABC, Person_who_get_info: 33
> 
> 
> Now I would get some results:
> 
> Person 2
> Person 23
> Person 32
> Person 33
> 
> It is possible without a stored procedure?

Yes, UNION is your friend!

SELECT 
  Order.PERSON_WHO_GET as Person
FROM Order
  WHERE Order.ID = 123

UNION

SELECT 
  Project.Manager as Person
FROM Project
  JOIN Order ON Order.ProjectID = Project.ProjectID
WHERE 
  Order.ID = 123

UNION

SELECT 
  ProjectSend.Person_who_get_info as Person
FROM ProjectSend
  JOIN Order ON Order.ProjectID = ProjectSend.ProjectID
WHERE 
  Order.ID = 123


Sean

[firebird-support] sql query in firebird

[Olaf Kluge]

Hello,

 

fb 2.1 cs is running

 

Now I have four tables:

 

Persons:

ID

Name

EMAIL

 

ORDER:

ProjectID

PERSON_WHO_GET (references ID from Persons)

STATUS

 

Projects:

ProjectID

Manager (references ID from Persons)

 

ProjectSEND:

ProjectID

PERSON_WHO_GET_INFO (references ID from Persons)

 

Each Person (ID) is unique, each ProjectID too. Every order has one
person_who_get, one Project has 1:n orders. In ProjectSend can I configure
many persons who get an email for one Project.

 

Starting with the order table and its status (open), I can reference to the
project, than to the projectSend-Table. I would get all Persons (ID)
grouped, which are deposited with the order, the project (in dependence on
procect) and who is configurated to became an email (ProjectSend)

 

For example:

 

Order 123

Person who get Order: 2

For Projekct ABC

 

ProjectManager for ABC: 23

 

ProjectSend f

Project: ABC, Person_who_get_info: 32

Project: ABC, Person_who_get_info: 33

 

Now I would get some results:

Person 2

Person 23

Person 32

Person 33

 

It is possible without a stored procedure?

 

Thanks in advance.

 

Best regards

 

Olaf

 

 



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

Re: [firebird-support] SQL Server to Firebird data migration tools

[Thomas Steinmaurer]

>> Are there any free tools for SQL Server to Firebird data migration?
>
> Not free, but worth every penny/cent, with its multi-DBMS capabilities,
> but I'm biased.

Database Workbench that is:
http://www.upscene.com/go/?go=dbw


-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Re: [firebird-support] SQL Server to Firebird data migration tools

[Thomas Steinmaurer]

> Are there any free tools for SQL Server to Firebird data migration?

Not free, but worth every penny/cent, with its multi-DBMS capabilities, 
but I'm biased.

-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

AW: [firebird-support] SQL Server to Firebird data migration tools

[Nikolaus Kern]

Hello,

 

maybe EMS Datapump can do the job in your case.

 

Niko

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Im Auftrag von Newbie
Gesendet: Samstag, 29. September 2012 01:50
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] SQL Server to Firebird data migration tools

 

  

Are there any free tools for SQL Server to Firebird data migration?





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

[firebird-support] SQL Server to Firebird data migration tools

[Newbie]

Are there any free tools for SQL Server to Firebird data migration?

Re: [firebird-support] SQL Puzzle: Two Way String Matching

[unordained]

-- Original Message ---
From: "red_october2009" 
> I need an SQL statement that will return all records where at least 
> one human name is found in the pet names field (not the other way 
> around).  In this case the only records that qualify are:
--- End of Original Message ---

create procedure split_list (incoming varchar(32000), delimeter varchar(5))
returns (
 outgoing varchar(32000)
) as
begin
 /* iterate through incoming, using substring(), pos(), and suspend to return 
each chunk individually; also, I suggest you trim() the results and convert 
them 
to a well-defined case (upper or lower) */
end

select people.id, real_names, pet_names
from people 
where exists (select * from split_list(pet_names) as pet_split where 
pet_split.outgoing in (select real_split.outgoing from split_list(real_names) 
as 
real_split);

You could do some stuff with joining (left) to the SP, but I think that's 
nastier.
Untested, and I'm feeling rather under the weather, but generally that's the 
direction I'd go. And then you can use that same SP that you build to also 
normalize your data as Norman suggests.

-Philip

Re: [firebird-support] SQL Puzzle: Two Way String Matching

[Norman Dunbar]

Evening Red October 2009,

 > ...

> 1 ^ 185 ^ RALF JOEY FRED  ^ BRUTUS
> 1 ^ 105 ^ KARL JACOB NEWTON   ^ SNACKER
> 1 ^ 973 ^ MARLEEN MUFFY ABE   ^ LUVER POPEYE TANGO
> 2 ^ 243 ^ BAXTER MILTON MARY  ^ RIPPER JOEY TOMMY
> 2 ^ 239 ^ ABIGAIL HARMONY ^ SPOT MUFFY TIPPY
> 2 ^ 982 ^ KEVIN PETE PAT  ^ THOR TINKLES KELSEY
>
> I need an SQL statement that will return all records where at least one human 
> name is found in the pet names field (not the other way around).  In this 
> case the only records that qualify are:

Are you saying that you want an SQL statement that will go through every 
"list of human names" extracting each name, and for each one, go through 
every "list of pet names" to see if there's a match?

> 2-243  (JOEY)
> 2-239  (MUFFY)
>
> Anyone got any ideas?  Every time I attempt this, it gets overly complicated.

I don't have an answer at the moment, sorry. I rather suspect you might 
have to write a procedure to do this, as opposed to a single plain SQL 
statement. I'm afraid that this is a nightmare caused by having a 
non-normalised table.

Please excuse the following eRant.

You shouldn't have lists of anything in a single column of a table. You 
shouldn't have columns named pet_name_1, pet_name_2 etc because that's 
not normalised either.

You will have other problems as well, if there are more names than can 
fit into your space delimited human and/or pet names. The names of 
humans and/or pets should be extracted to a separate table, possibly of 
the following format:

USR_ID INTEGER
ID_NMBR INTEGER
PET_OR_HUMAN VARCHAR(1)
A_NAME VARCHAR(50)

The first two columns would be a foreign key back to the appropriate 
primary key in the main table, and the value in A_NAME would be *one 
single* name, of a pet or human, as indicated by the PET_OR_HUMAN column 
- which would of course have a constraint to ensure only the two values 
expected - say P or H - were indeed allowed.

Your main table would be redefined as:

USR_ID INTEGER  (primary key, field 1)
ID_NMBR INTEGER (primary key, field 2)

I assume that there are other columns not mentioned.

Now it's far easier to write a statement to extract wrongly stored 
names. Assuming it was needed of course!

As Ann often says, good luck!


Cheers,
Norm.

-- 
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767

[firebird-support] SQL Puzzle: Two Way String Matching

[red_october2009]

I have a table with an ID number, and two string fields, one for family human 
names and one for family pet names. The names are delimited by space character.

USR_ID INTEGER  (primary key, field 1)
ID_NMBR INTEGER (primary key, field 2)
MY_FAMILY_HUMAN_NAMES VARCHAR(150)
MY_FAMILY_PET_NAMES VARCHAR(150)

Data:  

The ^ denotes field boundaries.. not part of the data
Extra spaces are for readability only.. not part of the data

1 ^ 185 ^ RALF JOEY FRED  ^ BRUTUS
1 ^ 105 ^ KARL JACOB NEWTON   ^ SNACKER
1 ^ 973 ^ MARLEEN MUFFY ABE   ^ LUVER POPEYE TANGO
2 ^ 243 ^ BAXTER MILTON MARY  ^ RIPPER JOEY TOMMY
2 ^ 239 ^ ABIGAIL HARMONY ^ SPOT MUFFY TIPPY
2 ^ 982 ^ KEVIN PETE PAT  ^ THOR TINKLES KELSEY

I need an SQL statement that will return all records where at least one human 
name is found in the pet names field (not the other way around).  In this case 
the only records that qualify are:

2-243  (JOEY)
2-239  (MUFFY)

Anyone got any ideas?  Every time I attempt this, it gets overly complicated.

[firebird-support] SQL Code = -838 Database ? shutdown in ? seconds

[sboydlns]

I installed Firebird 2.1.Firebird-2.1.4.18393 on a virgin Windows 2008 R2 
system.

I was reloading a database from back up.  It ran for about 8 hours, reloaded 
about 26GB of data and then terminated with this error:

SQL Code = -838 Database @1? shutdown in @2? seconds

Firebird was still running and no one used gfix to shutdown the DB. I had to 
restart Firebird after this error before I could rerun the restore.  I got 
connection refused errors until Firebird was restarted.

Does anyone have any idea what this means?

[firebird-support] SQL Firebird 2.0.6 Monitor in real time

[Gustavo Moda]

Hello,

Are there any software for SQL monitor witch runs in real-time on Firebird
2.0.6 Server?

Best Regards,

Gustavo Moda


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

Re: [firebird-support] SQL Performance question

[Vander Clock Stephane]

it's depend where you filter will be the most evident
ie : on VareFrvStrNr or on Dato ...

for exemple it their is zillions reccord with VareFrvStrNr = '090179' 
then it's better to
use the index on Dato and read the data in the dato order still will see 
one row with
VareFrvStrNr = '090179'

on the other way, if few rec with VareFrvStrNr = '090179', it's better 
for the engine
to retrieve ALL the record with VareFrvStrNr = '090179' and look in it 
the low dato ...

ok firebird as this great hability to use 2 index :) but not always so good
this what he try to do in your query

the firebird engine can not be all the time cleaver to know with 
strategy to use !

so in this way you can specify the plan in you query :)


On 2/1/2012 2:49 PM, Michael Vilhelmsen wrote:
>
> Hi
>
> I have a table containing some 33.879.139 records.
>
> If I do a simple select like this:
>
> Select Dato from Transaktioner
> Where
> VareFrvStrNr='090179'
> AND Art=11
> And Transaktioner.Afdeling_ID<>'9'
>
> it fecthes 11 records within 0.3 seconds.
> It will use the index on VareFrvStrNr according to the plan.
> PLAN (TRANSAKTIONER INDEX (TRANS_VAREFRVSTRNR))
>
> If I instead uses this SQL
>
> Select Min(Dato)As MinimuSTG from Transaktioner
> Where
> VareFrvStrNr='090179'
> AND Art=11
> And Transaktioner.Afdeling_ID<>'9'
>
> It takes at least 3 seconds. Often more.
> Now the plan is like this:
> PLAN (TRANSAKTIONER ORDER TRANS_DATO INDEX (TRANS_VAREFRVSTRNR))
>
> The TRANS_DATO index has almost no duplicates.
> The TRANS_VAREFRVSTRNR can have some dublicates.
>
> Why is there such a big difference is the performance?
> can I do anything to optimize this?
>
> Michael
>
> 


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

RE: [firebird-support] SQL Performance question

[Svein Erling Tysvær]

Hi Michael!

>I have a table containing some 33.879.139 records.
>
>If I do a simple select like this:
>
>
>Select Dato from Transaktioner
>Where
>  VareFrvStrNr='090179'
>  AND Art=11
>  And Transaktioner.Afdeling_ID<>'9'
>
>it fecthes 11 records within 0.3 seconds.
>It will use the index on VareFrvStrNr according to the plan.
>PLAN (TRANSAKTIONER INDEX (TRANS_VAREFRVSTRNR))
>
>If I instead uses this SQL
>
>Select Min(Dato)As MinimuSTG from Transaktioner
>Where
>  VareFrvStrNr='090179'
>  AND Art=11
>  And Transaktioner.Afdeling_ID<>'9'
>
>It takes at least 3 seconds. Often more. 
>Now the plan is like this:
>PLAN (TRANSAKTIONER ORDER TRANS_DATO INDEX (TRANS_VAREFRVSTRNR))
>
>The TRANS_DATO index has almost no duplicates.
>The TRANS_VAREFRVSTRNR can have some dublicates.
>
>Why is there such a big difference is the performance?

I don't know, though I guess 090179 isn't the VareFrvStrNr with the highest 
number of duplicates and since Firebird doesn't (yet) use histograms (I think), 
the optimizer doesn't know that using TRANS_DATO will slow down this query. If 
you used a common VareFrvStrNr (in this sense, NULL IS a value :o), then I 
guess the slow query might be quicker than the quick one to fetch all rows.

>can I do anything to optimize this?

Well, I would guess a variant of the old +0 trick would help:

Select Min(Dato+0) As MinimuSTG from Transaktioner Where ...

HTH,
Set

[firebird-support] SQL Performance question

[Michael Vilhelmsen]

Hi

I have a table containing some 33.879.139 records.

If I do a simple select like this:


Select Dato from Transaktioner
Where
  VareFrvStrNr='090179'
  AND Art=11
  And Transaktioner.Afdeling_ID<>'9'


it fecthes 11 records within 0.3 seconds.
It will use the index on VareFrvStrNr according to the plan.
PLAN (TRANSAKTIONER INDEX (TRANS_VAREFRVSTRNR))



If I instead uses this SQL


Select Min(Dato)As MinimuSTG from Transaktioner
Where
  VareFrvStrNr='090179'
  AND Art=11
  And Transaktioner.Afdeling_ID<>'9'

It takes at least 3 seconds. Often more. 
Now the plan is like this:
PLAN (TRANSAKTIONER ORDER TRANS_DATO INDEX (TRANS_VAREFRVSTRNR))


The TRANS_DATO index has almost no duplicates.
The TRANS_VAREFRVSTRNR can have some dublicates.



Why is there such a big difference is the performance?
can I do anything to optimize this?

Michael

Re: [firebird-support] SQL user management in FB 2.5

[Tomasz Tyrakowski]

Dear Helen,

Thank you for the clarification. I think this particular user ("IS") was 
added by our customer in FB 1.0.3, but that's irrelevant. And yes, we've 
been upgrading the security database along the servers, sticking to 
dialect 1 for the main databases for lack of a good enough reason to 
make the effort of migrating to dialect 3 (600k lines of Delphi code to 
scrutinize, plus about 40 databases with average size of 20GB to 
transfer to a new structure and a lot of triggers and stored procedures 
to verify).
I'm not sure I understand your question regarding unquoted "special" 
users in our security databases. The scenario is simple and can be 
repeated easily with the current version of FB. Just create a weird 
user, say IS ;), using gsec (or services). Then you can't do anything 
with that user if you issue the ALTER USER statements being logged in to 
a dialect 1 database, while everything works fine if you're logged in to 
a dialect 3 database (despite the fact that you're actually working with 
the very same security DB). Now at least, thanks to your hints, I fully 
understood why that's the case, and I admit I forgot that quoting of DB 
objects requires dialect 3.
In fact, the previous sentences suggest a simple workaround: create an 
empty dialect 3 database and make your app attach to it just to execute 
CREATE/ALTER/DROP USER safely (with quoted user names). The server 
doesn't care which DB you're working on anyway - it's the dialect that 
matters.

thanks again
Tomasz

On 2011-10-03 22:30, Helen Borrie wrote:
> At 08:32 AM 4/10/2011, Tomasz Tyrakowski wrote:
>> Hi guys,
>>
>> Have you managed to make the statement
>>
>> alter user IS grant admin role
>>
>> work? Or any other alter/create user with an SQL keyword as the user name?
>> If anybody happened to read somewhere that it's downright impossible,
>> please post a link so that other people stop wasting their time searching.
>
> Let's put it this way:  it *shouldn't* be possible with a Dialect 1 database. 
>  Reason:  IS is a reserved word in Fb 2.5.  The only way to refer to that 
> identifier in SQL would be to double-quote it.  The double-quote feature is 
> not supported in Dialect 1.
>
> The interesting part is how you managed to have this identifier existing 
> unquoted in your v.11.2 security database. The only way I can think of is 
> that it has been progressively upgraded as a Dialect 1 database since IB 4 
> times.
>
> ./hb
>
>


-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__