Hi Thomas, thank you, it works perfectly. Alan Alan J Davies Aldis
On 17/08/2014 06:47, Thomas Beckmann [email protected] [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 >> >> > ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
