Hi Thomas, thank you, it works perfectly.
Alan

Alan J Davies
Aldis

On 17/08/2014 06:47, Thomas Beckmann thomas.beckmann@...
[firebird-support] wrote:

> Hi Alan!
>
> The reason is very simple:
>
> The first select in your procedure is a singleton select, it can deal
> with one record only, because it's no "for select ...". Thus you get an
> error, if there are more than one record that match the condition
> (quantity>1).
>
> Do
> for select Id, description, quantity from Test_table where quantity>1
> into :Id1, :description1, :quantity1
> do
> while (quantity1>1) do
> begin
> insert into Test_table(Id, description, quantity)
> values(:Id1, :description1, 1);
> quantity1=quantity1-1;
> end
>
> Cheers, Thomas
>
>> create or alter procedure test_table_update
>> as
>> declare id1 integer;
>> declare description1 char(10);
>> declare quantity1 integer;
>> begin
>> select Id, description, quantity
>> from Test_table
>> where quantity>1
>> into :Id1, :description1, :quantity1;
>> while (:quantity1>1) do
>> begin
>> insert into Test_table(Id, description, quantity)
>> values(:Id1, :description1, 1);
>> quantity1=quantity1-1;
>> end
>> update Test_table
>> set quantity=1
>> where quantity>1;
>> end
>>
>> This works if there is one item with a quantity of more than 1 otherwise
>> I get "multiple rows in singleton select" error
>>
>> Id Description quantity sequence_no
>> 1 Beermat 3 1
>> 2 Beer Mug 3 2
>> 3 Whisky Glass 1 3
>>
>> Any advice gratefully received.
>> Alan
>>
>> --
>> Alan J Davies
>> Aldis
>>
>>
>