Subject Re: Problem calling set generator...
Author phil_hhn
--- In, Helen Borrie <helebor@...> wrote:
> At 04:30 PM 7/11/2006, you wrote:
> >SET GENERATOR GEN_PREFS TO (select (case when max(PREF_SN) is null
> >then 1 else max(PREF_SN)+1 end) from PREFS)
> Well...partly...a SELECT (even a subquery) needs a FROM clause...

Ummm... my subquery ends with '...from PREFS'?

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

Yes thanks, I realised that mistake after my original post (blush) -
it should read "...then 0 else max(PREF_SN) end..."

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

errr... but this will not set the next value (or will it)? I.e after
run once does 'gen_id' set the value against the GEN_PREFS generator?
(Sorry, just got to know but I can't test for a day ;-) )

> Just make sure you have totally exclusive access when you're mucking
> about like this.
For a data pump like this - sure do! :)