Re: SELECT INTO procedure variable and UNION clause
Posted in 2009
Topics: Stored Procedures & SPL, Versions, Editions & End-of-Life, Jobs, Consulting & Announcements
You are so right ...
It works. And this works better because avoids duplicates. Having tow
FOREACH does not avoid duplicates.
Thanks a lot.
On Wed, Mar 4, 2009 at 3:54 PM, Art Kagel <art.kagel@gmail.com> wrote:
> 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.
>
>