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 > > -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de <mailto:thomas.beckm...@assfinet.de> ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de <mailto:i...@assfinet.de> • www.assfinet.de <http://www.assfinet.de/> Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 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: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
