Unnecessary increment of sequence

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

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

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

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

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

On 1/3/14 4:49 AM, Tim Dudgeon wrote:
> 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');
Hi Tim,

You could write a function to issue the "next value for" only when 
necessary. Something like this:

import java.sql.*;

public class w
{
     public  static  Integer coalesce( Integer arg1, String sequenceName )
         throws SQLException
     {
         if ( arg1 != null ) { return arg1; }

         Connection  conn = DriverManager.getConnection( 
"jdbc:default:connection" );
         ResultSet   rs = conn.prepareStatement( "values next value for 
" + sequenceName ).executeQuery();

         rs.next();
         int     retval = rs.getInt( 1 );
         rs.close();

         return retval;
     }
}

...and then use the function like this:

connect 'jdbc:derby:memory:db;create=true';

create sequence FOO;

create function myCoalesce( arg1 int, sequenceName varchar( 32672 ) ) 
returns int
language java parameter style java reads sql data
external name 'w.coalesce';

values syscs_util.syscs_peek_at_sequence( 'APP', 'FOO' );
values myCoalesce( 99, 'FOO' );
values syscs_util.syscs_peek_at_sequence( 'APP', 'FOO' );
values myCoalesce( null, 'FOO' );
values syscs_util.syscs_peek_at_sequence( 'APP', 'FOO' );


Hope this helps,
-Rick