Re: SELECT INTO procedure variable and UNION clause

OK, UNIONS work in stored procedures. The trick is that the INTO clause is attached to the first part of the query: FOREACH SELECT a, b, c, d INTO v1, v2, v3, v4 FROM.... WHERE.... UNION SELECT a1, b1, c1, d1 FROM..... WHERE....... END FOREACH Art On Wed, Mar 4, 2009 at 3:35 PM, Gentian Hila <genti.tech@gmail.com> wrote: > It's IDS 9.40 > > But it's already taken care I think from the test that I ran. > > On Wed, Mar 4, 2009 at 3:26 PM, Art Kagel <art.kagel@gmail.com> wrote: > > Engine version and platform would be helpful. > > > > Art > > > > On Wed, Mar 4, 2009 at 3:08 PM, Gentian Hila <genti.tech@gmail.com> > wrote: > >> > >> 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 > >> >> > >> > > >> _______________________________________________ > >> Informix-list mailing list > >> Informix-list@iiug.org > >> http://www.iiug.org/mailman/listinfo/informix-list > > > > > > > > -- > > Art S. Kagel > > Oninit (www.oninit.com) > > IIUG Board of Directors (art@iiug.org) > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > > do not reflect on my employer, Oninit, the IIUG, nor any other > organization > > with which I am associated either explicitly or implicitly. Neither do > > those opinions reflect those of other individuals affiliated with any > entity > > with which I am affiliated nor those of the entities themselves. > > > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.