Stored Procedure in Select
Gustavo wanted a SELECT to call a stored procedure returning six columns, but got errors ("procedure returns too many values", then a syntax error on TABLE). Art and Jacques explained the TABLE(myproc()) AS a(c1,c2,...) syntax, noting you cannot pass a column from another table in the query into the function (error -999); the workaround is a wrapper function that loops over the select with FOREACH and RETURN ... WITH RESUME. Gustavo ultimately solved it himself: removing WITH RESUME from his procedure let it return all the columns he needed.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello people of the forum! I need to create a query that calls a Stored Procedure which returns more than one value. Obviously I throws an error. Is there any way to do this? Thank you for your attention!
Do you want to know how to write such a proc or how to call it? If the
latter, what host language?
In dbaccess you can:
execute procedure myproc();
If you need a host language cursor for that you can PREPARE the execute
then open a cursor against it.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Aug 20, 2013 at 10:49 AM, GUSTAVO ECHENIQUE <
gustavo.echenique@cemdo.com.ar> wrote:
> Hello people of the forum!
> I need to create a query that calls a Stored Procedure which returns more
> than
> one value.
> Obviously I throws an error. Is there any way to do this?
>
> Thank you for your attention!
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c233ea5eed4804e4626fca
Hi Art! Thank you very much for responding so quickly. I I have written the Stored Procedure and returns 6 columns. It works very well. But when I need to generate a Select and call it from there, tells me that the Stored Procedure returns too many values​​. The problem is that I need all these values​​.
So you need to return the six columns from the procedure along with other
data from some table? As long as you do not have to pass in values from
the other tables in the query you can call it like this:
select a.col1, a.col2, b.col1, b.col2, ...
from sometable as a, table( myproc() ) as b( col1, col2, col3, ...)...;
If you try to pass in values from one of the joined tables you will get a
-999 error, "not implemented yet".
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Aug 20, 2013 at 11:13 AM, GUSTAVO ECHENIQUE <
gustavo.echenique@cemdo.com.ar> wrote:
> Hi Art!
>
> Thank you very much for responding so quickly.
> I I have written the Stored Procedure and returns 6 columns. It works very
> well.
> But when I need to generate a Select and call it from there, tells me that
> the
> Stored Procedure returns too many values​​.
> The problem is that I need all these values​​.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7b343f126d1dc804e462ba9f
If you do need to pass in values from the other tables in the select, then you will have to write a wrapper routine that runs the select from the 'tables' in the query in a loop, executes the function for each row, and returns the combined set of values from the select and the function call. THAT routine you can then EXECUTE to get the full result set. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 20, 2013 at 11:13 AM, GUSTAVO ECHENIQUE < gustavo.echenique@cemdo.com.ar> wrote: > Hi Art! > > Thank you very much for responding so quickly. > I I have written the Stored Procedure and returns 6 columns. It works very > well. > But when I need to generate a Select and call it from there, tells me that > the > Stored Procedure returns too many values​​. > The problem is that I need all these values​​. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0158b878c09f3e04e462c945
Original post:
Hello people of the forum!
I need to create a query that calls a Stored Procedure which returns more than
one value.
Obviously I throws an error. Is there any way to do this?
Thank you for your attention!
Response:
So I think you are trying to do something like this?
create function jr2() returning char(15), char(15);
define f1 char(15);
define l1 char(15);
foreach cur1 for select fname, lname into f1,l1 from customer
return f1,l1 with resume;
end foreach;
end function;
select a.c1, a.c2 from table(jr2()) as a(c1,c2);
Jacques Renaut
IBM Informix Advanced Support
APD Team
Sorry Art, do not quite understand what I explain. My query is as follows: "SELECT xxDeudaGas1 (cc.IdCbte) FROM cc WHERE Cbtes_Coop cc.IdSuministro = 100377 AND cc.Estado! = 'X' AND cc.Srv_Saldo> 0"
Hi Jaques! Thank you very much for responding so quickly! My query is as follows: "SELECT a.vComprobante, a.vVto, a.vTotalimp, a.vSrv_Saldo, a.vTotalRecNormal, a.vTotal FROM TABLE (xxDeudaGas1 (cc.IdCbte)) AS a (vComprobante, vVto, vTotalimp, vSrv_Saldo, vTotalRecNormal, vTotal) , Cbtes_Coop cc = 100377 AND WHERE cc.IdSuministro cc.Estado! = 'X' AND cc.Srv_Saldo> 0 The syntax error appears in clause TABLE
Original post: Hi Jaques! Thank you very much for responding so quickly! My query is as follows: "SELECT a.vComprobante, a.vVto, a.vTotalimp, a.vSrv_Saldo, a.vTotalRecNormal, a.vTotal FROM TABLE (xxDeudaGas1 (cc.IdCbte)) AS a (vComprobante, vVto, vTotalimp, vSrv_Saldo, vTotalRecNormal, vTotal) , Cbtes_Coop cc = 100377 AND WHERE cc.IdSuministro cc.Estado! = 'X' AND cc.Srv_Saldo> 0 The syntax error appears in clause TABLE Response: That select statement doesn't look correct, particularly this area ", Cbtes_Coop cc = 100377 AND WHERE cc.IdSuministro". I'm not sure if that is just a cut and paste error or what, but it makes it hard to say what it should be since I'm not exactly sure what you are trying to do. Also, I'm not sure if you can pass a column of a different table, your "xxDeudaGas1 (cc.IdCbte)" via this virtual table method. Jacques Renaut IBM Informix Advanced Support APD Team
Yes, as I said, you cannot do that you will instead have to:
create function xxDeudaGas2( a_IdSuministro <type>, a_Estado <type>,
a_Srv_Saldo <type> )
returning <type> as r1, <type> as r2, <type> as r3, <type> as r4,
<type> as r5, <type> as r6;
define rtn1, rtn2, rtn3, .....;
define lcl_IdCbte <type>;
foreach
select IdCtbe from cc
where cc.IdSuministro = a_IdSuministro
AND cc.Estado != a_Estado AND cc.Srv_Saldo > a_Srv_Saldo
into lcl_IdCbte
let rtn1, rtn2, rtn3, rtn4, rtn5, rtn6 = xxDeudaGas1( lcl_IdCbte );
return rtn1, rtn2, rtn3, rtn4, rtn5, rtn6 with resume;
end foreach
end function;
Then you will be able to:
execute function xxDeudaGas2();
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Aug 20, 2013 at 11:43 AM, GUSTAVO ECHENIQUE <
gustavo.echenique@cemdo.com.ar> wrote:
> Sorry Art, do not quite understand what I explain.
> My query is as follows:
> "SELECT xxDeudaGas1 (cc.IdCbte) FROM cc WHERE Cbtes_Coop cc.IdSuministro =
> 100377 AND cc.Estado! = 'X' AND cc.Srv_Saldo> 0"
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e013d14ca2389b504e46475b7
Art, Jaques e Ignacio: Muchas gracias por su ayuda, que fue muy rápida. Al final terminé resolviendo el problema de otra manera: Ya que el Stored Procedure que yo llamaba desde el Select tenía la cláusula RETURN... WITH RESUME, el cursor quedaba dentro de la misma y no volvía al SELECT (lo descubrí de casualidad). Una vez eliminado el WITH RESUME, ya el SP pudo devolver la cantidad de columnas que yo necesito. Reitero nuevamente mi gratitud hacia ustedes. Gustavo Echenique