SPL into SELECT statement
User asked how to use an SPL procedure returning two values per row in a SELECT statement. Multiple solutions were provided: using TABLE() with collection derived tables to invoke procedures inline, using temp tables within procedures, or using ROW() return types with dot notation to access fields. Fernando's ROW() approach was confirmed as the working solution for the user's needs.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello y'all! I need of your expertize because I'm trying to do something "impossible". I got an SPL which returns two values per row. Is there a way to use it into a SELECT statement? I know I could use it only if it returns one value but I'm don't know if there is a trick to do what I want. I'll be waiting for your valious answer. Thanks in advance. Best regards.
I would use a collection derived table.
create function func2(a int, b int)
returning integer, integer
return a+b, a;
end function;
select t.a, t.b
from TABLE(func2(1,1)) as t(a,b)
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 09/16/2014 09:33:21 AM:
> From: "ANDRES DE LA OSSA" <andros89@gmail.com>
> To: ids@iiug.org,
> Date: 09/16/2014 09:34 AM
> Subject: SPL into SELECT statement [33791]
> Sent by: ids-bounces@iiug.org
>
> Hello y'all!
>
> I need of your expertize because I'm trying to do something "impossible".
I
> got an SPL which returns two values per row. Is there a way to use it
into a
> SELECT statement? I know I could use it only if it returns one value but
I'm
> don't know if there is a trick to do what I want.
>
> I'll be waiting for your valious answer.
>
> Thanks in advance. Best regards.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
select *
from table( myproc( arg1 ) );
> create procedure two_values( ) returning int as one, int as two;> return 12, 13;
> end procedure;
Routine created.
> execute procedure two_values();
one two
12 13
1 row(s) retrieved.
> select * from table( two_values());
unnamed_col_1 unnamed_col_2
12 13
1 row(s) retrieved.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on 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, Sep 16, 2014 at 12:33 PM, ANDRES DE LA OSSA <andros89@gmail.com>
wrote:
> Hello y'all!
>
> I need of your expertize because I'm trying to do something "impossible". I
> got an SPL which returns two values per row. Is there a way to use it into
> a
> SELECT statement? I know I could use it only if it returns one value but
> I'm
> don't know if there is a trick to do what I want.
>
> I'll be waiting for your valious answer.
>
> Thanks in advance. Best regards.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11340f1253cfd20503335997
Hi,
another option would be to create a temp table in a stored procedure and use
that one in your select.
That way, you can name the returned values and even return multiple different
values.
Or, you have the possibility to even return multiple rows from a stored
procedure,
which is also possible using a foreach clause and returning the values with
the resume keyword.
( http://www.ibm.com/developerworks/data/library/techarticle/dm-0806mottupalli
/)
Marcus Haarmann
----- Ursprüngliche Mail -----
Von: "Art Kagel" <art.kagel@gmail.com>
An: ids@iiug.org
Gesendet: Dienstag, 16. September 2014 20:59:27
Betreff: Re: SPL into SELECT statement [33793]
select *
from table( myproc( arg1 ) );
> create procedure two_values( ) returning int as one, int as two;> return 12, 13;
> end procedure;
Routine created.
> execute procedure two_values();
one two
12 13
1 row(s) retrieved.
> select * from table( two_values());
unnamed_col_1 unnamed_col_2
12 13
1 row(s) retrieved.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on 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, Sep 16, 2014 at 12:33 PM, ANDRES DE LA OSSA <andros89@gmail.com>
wrote:
> Hello y'all!
>
> I need of your expertize because I'm trying to do something "impossible". I
> got an SPL which returns two values per row. Is there a way to use it into
> a
> SELECT statement? I know I could use it only if it returns one value but
> I'm
> don't know if there is a trick to do what I want.
>
> I'll be waiting for your valious answer.
>
> Thanks in advance. Best regards.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11340f1253cfd20503335997
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
The answers provided will work assuming you don't need to pass values from
another table to the function....
If you want to really enter the crazy world of database extensibility you
can do something like:
castelo@primary:fnunes-> dbaccess -e stores test_f_table 2>&1| grep -v "^$"
Database selected.
DROP TABLE IF EXISTS test_f_table;Table dropped.
DROP FUNCTION IF EXISTS f1;Routine dropped.
CREATE FUNCTION f1 (a INTEGER, b INTEGER) RETURNING ROW(ret_a CHAR(10),
ret_b CHAR(10))RETURN ROW(a::CHAR(10), b::CHAR(10));;
END FUNCTION;
Routine created.
;
CREATE TABLE test_f_table
(
a INTEGER,
b INTEGER
);
Table created.
INSERT INTO test_f_table VALUES (1,1);1 row(s) inserted.
INSERT INTO test_f_table VALUES (2,2);1 row(s) inserted.
INSERT INTO test_f_table VALUES (3,3);1 row(s) inserted.
INSERT INTO test_f_table VALUES (4,4);1 row(s) inserted.
INSERT INTO test_f_table VALUES (5,5);1 row(s) inserted.
SELECT a, b, f1(a,b).ret_a, f1(a,b).ret_b
FROM test_f_table;
a b ret_a ret_b
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5
5 row(s) retrieved.
Database closed.
castelo@primary:fnunes->
Regards.
On Tue, Sep 16, 2014 at 5:33 PM, ANDRES DE LA OSSA <andros89@gmail.com>
wrote:
> Hello y'all!
>
> I need of your expertize because I'm trying to do something "impossible". I
> got an SPL which returns two values per row. Is there a way to use it into
> a
> SELECT statement? I know I could use it only if it returns one value but
> I'm
> don't know if there is a trick to do what I want.
>
> I'll be waiting for your valious answer.
>
> Thanks in advance. Best regards.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--20cf30684a4796720d050335952d
Thank you all for your answers! Fernando, your solution fit perfectly for what I need! Special thanks to you!