Re: SELECT INTO procedure variable and UNION clause

Anthony's suggestion took care of the issue - at least in the test procedure but it's the same situation in the real procedure as well. Thank you for the help. Appreciate it very much. On Wed, Mar 4, 2009 at 2:51 PM, Gentian Hila <genti.tech@gmail.com> wrote: > I forgot to put WITH RESUME.  I need a list of customer from customer > and ship_to customer as well not just the last result. > > This is a simplified procedure as the real one is quite long but in > princip they are the same. > > I will give this a try. > > Thank you, > > On Wed, Mar 4, 2009 at 2:40 PM, Anthony Judish <ajudish@lextron-inc.com> wrote: >> I don't believe stored procedures support unions. You can do with 2 separate FOREACH loops: >> >> CREATE PROCEDURE test1() >> RETURNING CHAR(10)>> >> DEFINE v_cust_num  CHAR(10); >> >> FOREACH cs_inv_line FOR >> select cust_num into v_cust_num from customer >> RETURN v_cust_num with resume; >> >> END FOREACH; >> >> >> FOREACH cs_inv_line FOR >> select cust_num into v_cust_num from cust_shp >> RETURN v_cust_num with resume; >> >> END FOREACH; >> >> END PROCEDURE >> >> >> What are you trying to get from the procedure? A list of all customer and ship to customer numbers? Your procedure will return just the last customer number. >> >> Anthony Judish >> Software Design Architect >> Lextron Inc. >> ph 970.378.2056 >> fax 970.346.2356 >> ajudish@lextron-inc.com >> >> >> -----Original Message----- >> From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Gentian Hila >> Sent: Wednesday, March 04, 2009 12:03 PM >> To: IIUG Informix List >> Subject: SELECT INTO procedure variable and UNION clause >> >> I have a Stored Procedure that return multiple rows. Something like this: >> >> CREATE PROCEDURE test1() >> RETURNING CHAR(10)>> >> DEFINE v_cust_num  CHAR(10); >> >> FOREACH cs_inv_line FOR >> select cust_num into v_cust_num from customer >> >> END FOREACH >> >> RETURN v_cust_num; >> >> END PROCEDURE >> >> >> and it works fine. However now I need to merge results from two tables >> with UNION and want to have it like this: >> >> CREATE PROCEDURE test1() >> RETURNING CHAR(10)>> >> DEFINE v_cust_num  CHAR(10); >> >> FOREACH cs_inv_line FOR >> select cust_num into v_cust_num from customer >> UNION >> select cust_num into v_cust_num from cust_shp >> >> >> END FOREACH >> >> RETURN v_cust_num; >> >> END PROCEDURE >> >> But it returns me an error. >> >> >> How can I put results returned from UNION of two SQL stored into >> procedure variables (v_cust_num)? >> >> Thank you, >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >> >