Christian Gütter schrieb:

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

Since these three values have got NOTHIMNG to do with each other you
shouldn't / msut not use a single query to retrieve them.

And: keep in mind that you have to add the descending index on the
pk-field to make it really fast!

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

I think this is not IB/FB specific that's just standard SQL.

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

Not really.
If you want to have several selects in one statement you have to use an
appropriate container - like a stored procedure.
Or you have to do it sequentially, one by one.


But for setting a generator to the highest used value you can use sth. like:

SELECT GEN_ID ( Gen_T_Personal_Pid,
( SELECT MAX ( PID ) FROM T_Personal )
- GEN_ID ( Gen_T_Personal_Pid, 0 ) )
FROM RDB$DATABASE

(select a generator value from RDB$DATABASE (which is always a
ONE-RECORD-TABLE) and increment it by the maximum value of the primary
key minus the current generator value)

But better do that when you're the only one working on the database :-)



Luc.