Groups | Search | Server Info | Login | Register

Groups > comp.databases.ingres > #3849

Re: Was I wrong to expect this to work?

Show all headers | View raw

G Jones wrote:

> On Thursday, August 4, 2022 at 4:24:50 PM UTC+1, Roy Hann wrote:
>> * create sequence genno as integer; 
>> * create procedure next_vno result row (integer) 
>> * as declare vno integer not null; 
>> * begin 
>> * select genno.nextval * 10 + generate_digit('LUHN_A',genno.currval) into :vno; 
>> * return row (:vno); 
>> * end 
>> * \g 
>> Executing . . . 
>> 
>> continue 
>> * select * from next_vno() \g 
>> Executing . . . 
>> 
>> E_LQ003A Cannot start up 'select' query. 
>> Unexpected initial protocol response. 
>> 
>> Roy
>
>
> I get E_SC0206, but splitting the query up slightly persuades it to produce a result:
>
> create procedure  next_vno result row (integer) as
> declare v1 integer not null;
>         v2 integer not null;
>         v3 integer not null;
> begin
>   select genno.nextval, genno.currval into :v1,:v2;
>   select :v1 * 10 + generate_digit('LUHN_A',:v2) into :v3;
>   return row(:v3);
> end;
>
> (Using 11.1 +p15773).

Thanks Geraint.

I did roughly the same thing so currval wasn't needed at all. But your
way confirms currval is not the source of the problem.

Incidentally I had previously tried using a view, on the basis that
any query can be a view. It turns out querying a sequence in a view is
explicitly disallowed. 

I cannot think what the justification would be. It's an annoying
exceptional case. But hey-ho; it's SQL.   :-P

Roy

Back to comp.databases.ingres | Previous | Next — Previous in thread | Find similar

Thread

Was I wrong to expect this to work? Roy Hann <specially@processed.almost.meat> - 2022-08-04 15:24 +0000
  Re: Was I wrong to expect this to work? G Jones <geraint.jones@ndph.ox.ac.uk> - 2022-08-05 00:20 -0700
    Re: Was I wrong to expect this to work? Roy Hann <specially@processed.almost.meat> - 2022-08-05 08:11 +0000

csiph-web