db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Unnecessary increment of sequence
Date Fri, 03 Jan 2014 12:49:12 GMT
I'm trying to use a sequence to generate a value where one is not 
supplied, but I'm not able to only increment the sequence when its 
needed. This is actually going on in a trigger, but to simplify matters 
here is an example that illustrates the problem:

VALUES SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE('APP', 'SEQ_CPD_CODE');
VALUES COALESCE(99, NEXT VALUE FOR seq_cpd_code);
VALUES SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE('APP', 'SEQ_CPD_CODE');

In the coalesce function the first argument is always non null, so the 
second argument should never be needed, but you will see that the 
sequence is incremented anyway.
Are there any alternative approaches that can avoid this?
I was thinking of trying in a CASE statement instead, but sequences 
can't be used there :-(

Thanks
Tim


Mime
View raw message