Re: Unnecessary increment of sequence

[Rick Hillegas <ri...@oracle.com>]

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

Re: Unnecessary increment of sequence

[Tim Dudgeon <td...@informaticsmatters.com>]

On 04/03/2014 19:10, Rick Hillegas wrote:
> 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
>
Rick

Sorry for delay - I only just spotted your response.
I'm not actually using NEXT VALUE FOR inside COALESCE as it didn't give 
me what I wanted (the sequence was always being incremented even when 
not necessary). So changing behaviour won't impact me.
Instead I reverted to using Java stored procedure as this allowed me to 
do the conditional stuff that I needed.
But its seems like an unnecessary workaround. I would have thought it 
would be useful to conditionally grab a value from a sequence, and if 
this can't be done with CASE or COALESCE then its probably not possible 
except by reverting to Java.
So I've got no strong feelings either way.

Tim