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

[masb...@za-management.com [firebird-support]]

Hi Dimitry,
thank you for your response.
I think in my case I can replace the outer join with an inner join, because it 
is the same sp with the same query parameters I am calling twice.

Are there any future plans to change this re-evaluation structure in outter 
joins?

Kind regards
Christian

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

[Tim Ward t...@telensa.com [firebird-support]]

On 26/01/2015 09:53, masb...@za-management.com [firebird-support] wrote:


Hi Dimitry,
thank you for your response.
I think in my case I can replace the outer join with an inner join, 
because it is the same sp with the same query parameters I am calling 
twice.


Are there any future plans to change this re-evaluation structure in 
outter joins?


With a lot of complex queries typically involving several joins, both 
inner and outer, several CTEs, and sometimes some procedures as well, I 
find that sometimes changing an inner join to an outer join improves 
performance by orders of magnitude, and sometimes changing an outer join 
to an inner join improves performance by orders of magnitude. I have not 
been able to deduce any rhyme or reason to what's going on - when I find 
something that works I'm afraid I just have to leave it and move on to 
the next problem rather than take time to investigate in any more detail.


In some cases the logic of the query is such that inner and outer joins 
*can only* deliver the same result, so one might hope that an optimiser 
would spot this and pick the faster execution path (manual tweaks to the 
syntax that don't affect the meaning of the query should not affect the 
output of the optimiser), but this doesn't (always?) seem to be what's 
happening.


--
Tim Ward

[firebird-support] Re: Firebird Classic installation on Fedora 21 64-bit - problem

[map...@gmail.com [firebird-support]]

* According to Fedora packaging rules, firebird service is not started
automatically. You need to start it, as root :
systemctl start firebird-superclassic.service
for Classic :
systemctl start firebird-classic.socket
If you want to have firebird started at each boot, as root :
systemctl enable firebird-superclassic.service
for Classic :
systemctl enable firebird-classic.socket
 

 

 via centos7 install thread 
 

 Support for Users of Firebird Releases 
https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/125772
 
 
 
https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/125772
 
 
 Support for Users of Firebird Releases 
https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/125772
 In this forum, experienced users of Firebird can offer help and advice about 
using the database. See the download master page for all released versions. I...
 
 
 
 View on groups.yahoo.com 
https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/125772
 
 Preview by Yahoo 
 
 
  


 

 

 

[firebird-support] How to change Primary Key on a table by adding a new field

[Luigi Siciliano luigi...@tiscalinet.it [firebird-support]]

Hello,
   I have an application that use a Firebird database and I need to 
change a Primary Key on a table by adding a new field.

I can drop a primary key constraint by "ALTER TABLE MyTable DROP 
CONSTRAINT MyINTEG_000" where "MyINTEG_000" is the constraint on my 
development database file.

I cannot use the instruction above because I don't know in all 
production databases of my customers the name of the Primary Key I want 
update.

How I can solve?

Thanks.
-- 

Luigi Siciliano
--







++

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] How to change Primary Key on a table by adding a new field

['Bogdan Mihalache' bogdan.mihala...@soter.ro [firebird-support]]

Select trim(I.RDB$FIELD_NAME)
  from RDB$RELATION_CONSTRAINTS RC
  join RDB$INDEX_SEGMENTS I on (I.RDB$INDEX_NAME = RC.RDB$INDEX_NAME)
  join RDB$INDICES IDX on (IDX.RDB$INDEX_NAME = RC.RDB$INDEX_NAME)
  where (RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY') and
(RC.RDB$RELATION_NAME = :TABELA)
  order by I.RDB$FIELD_POSITION
  into :PKTEMP

Using that select you can fiind the name of the column who has the primary
key. 
Inspired from Carlos Cantu external log procedure. Maybe you can be inspired
to.

-Original Message-
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Monday, January 26, 2015 8:06 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] How to change Primary Key on a table by adding a
new field

Hello,
   I have an application that use a Firebird database and I need to 
change a Primary Key on a table by adding a new field.

I can drop a primary key constraint by "ALTER TABLE MyTable DROP 
CONSTRAINT MyINTEG_000" where "MyINTEG_000" is the constraint on my 
development database file.

I cannot use the instruction above because I don't know in all 
production databases of my customers the name of the Primary Key I want 
update.

How I can solve?

Thanks.
-- 

Luigi Siciliano
--







++

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] How to change Primary Key on a table by adding a new field

['Bogdan Mihalache' bogdan.mihala...@soter.ro [firebird-support]]

 

 

  

Select trim(RC.RDB$INDEX_NAME)
from RDB$RELATION_CONSTRAINTS RC
where (RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY') and
(RC.RDB$RELATION_NAME = :TABELA)
into :PKTEMP

Using that select you can fiind the name of the column who has the primary
key. 
Inspired from Carlos Cantu external log procedure. Maybe you can be inspired
to.

-Original Message-
From: firebird-support@yahoogroups.com
 
[mailto:firebird-support@yahoogroups.com] 
Sent: Monday, January 26, 2015 8:06 PM
To: firebird-support@yahoogroups.com
 
Subject: [firebird-support] How to change Primary Key on a table by adding a
new field

Hello,
I have an application that use a Firebird database and I need to 
change a Primary Key on a table by adding a new field.

I can drop a primary key constraint by "ALTER TABLE MyTable DROP 
CONSTRAINT MyINTEG_000" where "MyINTEG_000" is the constraint on my 
development database file.

I cannot use the instruction above because I don't know in all 
production databases of my customers the name of the Primary Key I want 
update.

How I can solve?

Thanks.
-- 

Luigi Siciliano
--





++

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] Function - what is it called

[Helen Borrie hele...@iinet.net.au [firebird-support]]

At 11:25 p.m. 23/01/2015, michael.vilhelm...@microcom.dk [firebird-support] 
wrote:

>I am looking for a function which are able to add values from a table as 
>records are fetched.
>
>Something like this:
>
>ValueCalculated value 
>
>11
>
>23
>
>36
>
>410
>
>515
>
>The calculated value is just the sum of all previous values of VALUE.
>
>Whats the name of such function in english and Firebird?

It is often referred to as a "running total".

>Does such a function even exist in Firebird 2.5?

I have not heard of a function that could generate such result from multiple 
rows, since a function returns a finite, single-row set from finite input and 
would have no way to retain the current accumulated value from one call to the 
next.

As Thomas indicated, the window functions in Fb3 will enable you to do such 
operations in direct SQL.  In 2.5 and lower, it is very easy to generate a set 
containing a running total, by way of a selectable stored procedure.


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

Re: [firebird-support] How to change Primary Key on a table by adding a new field

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

On Mon, 26 Jan 2015 19:05:33 +0100, "Luigi Siciliano
luigi...@tiscalinet.it
[firebird-support]"  wrote:
> Hello,
>I have an application that use a Firebird database and I need to 
> change a Primary Key on a table by adding a new field.
> 
> I can drop a primary key constraint by "ALTER TABLE MyTable DROP 
> CONSTRAINT MyINTEG_000" where "MyINTEG_000" is the constraint on my 
> development database file.
> 
> I cannot use the instruction above because I don't know in all 
> production databases of my customers the name of the Primary Key I want 
> update.
> 
> How I can solve?

Others have already shown ways to solve to immediate problem. I'd like to
suggest that moving forward you start using explicitly named constraints,
which prevent the need for workarounds like these as then the name of the
constraint is part of your database design itself.

Mark