RE: SELECT INTO procedure variable and UNION clause
If I remember correctly, having the variable in the second select of a union will cause an error
>From this
> 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
To this
> FOREACH cs_inv_line FOR
> select cust_num into v_cust_num from customer
> UNION
> select cust_num from cust_shp
Thank you,
Jim Goldrick
Judson University
1151 North State Street
Elgin, Illinois 60123
573-332-7739
http://www.judsonu.edu
jgoldrick@judsonu.edu
Tech Services on the Web
-----Original Message-----
From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Gentian Hila
Sent: Wednesday, March 04, 2009 1:51 PM
To: IIUG Informix List
Subject: Re: SELECT INTO procedure variable and UNION clause
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