Hi Luc,

> You should have staid with never having used such a query before ;-)

OK, understood :-/

> What do you expect it to do???
> (and what is it good for???)

Do you really want to know it?

I have got to do a one-way replication with an access DB using a not
very sophisticated replication tool. The tables in my destination DB
(Firebird) have one other unique counter field in addition to the PKs.

The value for this counter is replicated from the access DB. When new
records are replicated from the access DB, the numbers in the counter
field increase, so that I have to adjust my generators to their new
maximum value.

It sounds weird, but the Firebird DB is still under development, so
there is no problem to change the values of the generators.

I do it using a stored proc called by the replicator:

SELECT MAX(PID) FROM T_Personal INTO :L_P_PID;
SELECT MAX(VID) FROM T_Vertraege INTO :L_V_VID;
SELECT MAX(KID) FROM T_Kunden INTO :L_K_KID;
EXECUTE STATEMENT 'SET GENERATOR Gen_T_Vertraege_Vid TO ' || :L_V_VID;
EXECUTE STATEMENT 'SET GENERATOR Gen_T_Personal_Pid TO ' || :L_P_PID;
EXECUTE STATEMENT 'SET GENERATOR Gen_T_Kunden_Kid TO ' || :L_K_KID;

Out of curiousity, I tried to retrieve the three MAX values using one
select statement, so I wrote the ugly statement we are talking about.

> You're doing a select on two different tables that have got nothing to
> do with each other, but if you'll have look at the plan of statement I'm
> sure you'll see that they'll be joined automatically.

> And you're asking for the MAX on a pk-field - if you haven't added an
> desc index on the pk field of the tables, for each record in T1 you
> should have 25k reads and each of these will read 25k times on table T2
> (and after that you'll have another 25K reads on T2).

Thanks for the explanation. I did not find anything of this in the IB
language reference.

BTW, is there a way to retrieve the three values using one
statement?


Christian