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,
Gentian Hila wrote:
> 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)?
>
You only use the INTO clause in the first SELECT clause.
Jeff