Re: SELECT INTO procedure variable and UNION clause
Posted in 2009
Topics: Stored Procedures & SPL, Versions, Editions & End-of-Life, Jobs, Consulting & Announcements
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.