At 04:30 PM 7/11/2006, you wrote:

>Hi, (This is Firebird 1.5.3)
>I have an 'empty' database with a 'prefs' table which has a pk
>'prefs_sn', and a generator 'gen_prefs' which is initially set to 1.
>I have a routine which copies all rows of the prefs table from one DB
>into another (ALL columns, including the pk's).
>
>When I'm complete I need to set the generator to max(prefs_sn)+1 so
>here's my statement:
>
>SET GENERATOR GEN_PREFS TO (select (case when max(PREF_SN) is null
>then 1 else max(PREF_SN)+1 end) from PREFS)
>
>However I'm running into trouble after "SET GENERATOR GEN_PREFS TO " -
>it seems I can only make it work if I hard-code the value, i.e it
>doesn't like me having the inner select there (error is 'Invalid token
>select...').
>
>How do I get the system to update the next number in the generator
>(there's probably a simpler way than I tried!)?

Well...partly...a SELECT (even a subquery) needs a FROM clause...

The generator stores the most recent number generated and will
generate a unique new number *next* time - so the value you actually
want is the max value itself - as long as you're not using the same
generator for more than one purpose, of course!

select gen_id(GEN_PREFS,(coalesce((select max(PREF_SN) from
WHATEVER),0))) from rdb$database

Just make sure you have totally exclusive access when you're mucking
about like this.

./heLen