Subject RE: [firebird-support] Problem calling set generator...
Author Alan McDonald
> 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).

a better idea is to NOT include the PK field in the copy process. The PK
will be re-generated - no need to fiddle any more

> 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!)?
> Thanks, Phil