Re: SELECT INTO procedure variable and UNION clause
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.
>
>