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
