db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Unnecessary increment of sequence
Date Tue, 04 Mar 2014 19:10:29 GMT
On 1/3/14 4:49 AM, Tim Dudgeon wrote:
> 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
>
>
Hi Tim,

I have looked more closely at this. I believe that Derby should not 
allow NEXT VALUE FOR inside a COALESCE expression. My reasoning can be 
found on this issue: https://issues.apache.org/jira/browse/DERBY-6494. 
However, I don't feel inclined to fix this divergence from the SQL 
Standard if you rely on it.

Thanks,
-Rick

Mime
View raw message