Subject Re: [ib-support] temporary tables
Author Frank Ingermann
Hi Duilio, (sorry if this msg comes twice)

Duilio Foschi wrote:
> I have to issue a special query.
> The query is so complex that I cannot guess how to do it in one step.
> So I have decided to create a temporary table, fill it in several steps,
> then open and go thru it.

have you considered using a storedproc? it helps a lot in breaking up
complex things into smaller parts, as you can e.g. use nested FOR SELECT
loops when there are lots of joins involved.

> As several users could be working on the same query, I build the name of
> the temporary table by adding a suffix and a number given by a generator.
> In this way I can be sure that the table name will be unique for every user.
> Are there better ways of using temporary tables in FB ?

yes indeed - don't use temporary tables like this at all! You could use
a *single* table and fill it temporarily, but it is imho not a good idea
to constantly modify the metadata of your db as a normal app behaviour.

In this single table you can take the generator and use it as a part of
the primary key, so each user will have the "impression" it's his/her own
temp table, while they are all really working on the same one. But you
should really try the storedproc as it may elminiate the need for temp
tables altogether.

> Duilio Foschi

regards & hth,

when parsers parse, and compilers compile, then why don't objects object?

fingerbirdy - fingerman's door to Firebird