use stored proc that return multiple columns in a query
Poster wanted to call an SPL procedure returning two values (price and description) inside a SELECT, hitting errors 684 ("returns too many values") and 9786. Art Kagel questioned the design (suggesting a lookup table/join), but others gave a workaround: create a ROW type with the two fields, write a wrapper function returning that row type, and use it in a SELECT, optionally unnesting via TABLE(MULTISET(...)) in a view. After syntax fixes (DEFINE ... ROW type, or RETURN ROW(a,b)::mytype), the poster got it working in dbaccess, though isql gave error 1820.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Is there a way of using a stored procedure that returns multiple
columns in query?
I have a stored proc that returns 2 columns and would like to use it
inside a query. Something like this:
select product_id,get_product_info(1) from products;
assuming get_product_info() returns price and description
This is a simplification of the problem and not the real problem I'm
trying to solve. The stored procedure I want to use has business logic
that cant be implemented just linking tables.
Currently the stored procedure is used in 4gl without issues.
So far I have tried different variations of the query but haven't have
luck.
Using isql/dbacces running the following:
execute procedure get_product_info(1) ;
684: Function (informix.get_product_info) returns too many values.
select id,get_product_info(id) from tmpproducts;
684: Function (informix.get_product_info) returns too many values.
SELECT * FROM TABLE(FUNCTION(get_product_info(1) )) as x1(p1,p2);
9786: Only iterative UDR allowed in this context.
scripts for the test:
create table 'informix'.tmpproducts (
id CHAR(20)
)
extent size 32 next size 32
lock mode page;
CREATE PROCEDURE get_product_info(product_id INTEGER ) RETURNINGMONEY(10,2), char(20);
DEFINE product_price MONEY(10,2);
DEFINE product_desc char(20);
IF(product_id = 1)THEN
LET product_price = "$10";
LET product_desc = "JUICE";
ELIF (product_id = 1)THEN
LET product_price = "$20";
LET product_desc = "BEER";
ELIF (product_id = 1)THEN
LET product_price = "$40";
LET product_desc = "NUTS";
ELIF (product_id = 1)THEN
LET product_price = "$80";
LET product_desc = "TOMATOES";
ELSE
LET product_price = "0";
LET product_desc = "ERROR";
END IF
RETURN product_price,product_desc;
END PROCEDURE;
forgot to include version info:
IBM Informix Dynamic Server Version 10.00.UC6W5 -- On-Line -- Up 207
days 22:14:39 -- 1441100 Kbytes
I'm a bit rusty on stored procedures but I do have a "hack solution". You can concatonate multiple columns so that they appear as 1. A SQL example is: SELECT column1 || column2 FROM table1 . . . -L.S.
On Oct 6, 4:54 pm, LIGHT SCANS <light_sc...@yahoo.com> wrote: > I'm a bit rusty on stored procedures but I do have a "hack solution". > You can concatonate multiple columns so that they appear as 1. A SQL > example is: > > SELECT column1 || column2 FROM table1 . . . > > -L.S. I tough about that but as the stored proc is used elsewhere I cant modify the return values. Another alternative is witting a wrapper that will call the stored proc and return one of the vales depending of an input flag. Then I would have to make to separate calls to get price and description but I think it will hit performance.
Maybe I'm just being dense, but why a stored procedure for this product
descriptive information in the first place? Why not just convert the stored
procedure to a table and query/join the table:
create table product_id_info( product_id int, price money(10,2), descriptionchar(20) );
insert into product_id_info values (1, 10.00, "JUICE" );...
CREATE UNIQUE INDEX product_id_info_pk ON product_id_info( product_id);
ALTER TABLE product_id_info ADD CONSTRAINT PRIMARY KEY( product_id )CONSTRAINT product_id_info_pk;
SELECT p.product_id, pii.price, pii.description
FROM product as p, product_id_info as pii
WHERE p.product_id = pii.product_id
AND p.product_id = 20;
As to any existing code that's already dependent on the procedure, just
rewrite it to fetch the data from the table instead of from the IF..
ELIF...END IF clause!
Sounds like you're suffering from the hammer syndrome.
Art
On Mon, Oct 6, 2008 at 4:23 PM, brenddie <brenddie@gmail.com> wrote:
> Is there a way of using a stored procedure that returns multiple
> columns in query?
>
> I have a stored proc that returns 2 columns and would like to use it
> inside a query. Something like this:
>
> select product_id,get_product_info(1) from products;>
> assuming get_product_info() returns price and description
>
> This is a simplification of the problem and not the real problem I'm
> trying to solve. The stored procedure I want to use has business logic
> that cant be implemented just linking tables.
> Currently the stored procedure is used in 4gl without issues.
>
> So far I have tried different variations of the query but haven't have
> luck.
> Using isql/dbacces running the following:
>
> execute procedure get_product_info(1) ;>
> 684: Function (informix.get_product_info) returns too many values.>
>
> select id,get_product_info(id) from tmpproducts;>
> 684: Function (informix.get_product_info) returns too many values.>
>
> SELECT * FROM TABLE(FUNCTION(get_product_info(1) )) as x1(p1,p2);>
> 9786: Only iterative UDR allowed in this context.>
>
>
>
>
>
> scripts for the test:
>
> create table 'informix'.tmpproducts (
> id CHAR(20)
> )
> extent size 32 next size 32
> lock mode page;
>
>
> CREATE PROCEDURE get_product_info(product_id INTEGER ) RETURNING> MONEY(10,2), char(20);
>
> DEFINE product_price MONEY(10,2);
> DEFINE product_desc char(20);
>
>
>
> IF(product_id = 1)THEN
> LET product_price = "$10";
> LET product_desc = "JUICE";
> ELIF (product_id = 1)THEN
> LET product_price = "$20";
> LET product_desc = "BEER";
> ELIF (product_id = 1)THEN
> LET product_price = "$40";
> LET product_desc = "NUTS";
> ELIF (product_id = 1)THEN
> LET product_price = "$80";
> LET product_desc = "TOMATOES";
> ELSE
> LET product_price = "0";
> LET product_desc = "ERROR";
> END IF
>
> RETURN product_price,product_desc;
>
> END PROCEDURE;
>
>
>
>
> _______________________________________________
> 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.
On Oct 6, 5:46 pm, "Art Kagel" <art.ka...@gmail.com> wrote:
> Maybe I'm just being dense, but why a stored procedure for this product
> descriptive information in the first place? Why not just convert the stored
> procedure to a table and query/join the table:
>
> create table product_id_info( product_id int, price money(10,2), description> char(20) );
> insert into product_id_info values (1, 10.00, "JUICE" );> ...
> CREATE UNIQUE INDEX product_id_info_pk ON product_id_info( product_id);
> ALTER TABLE product_id_info ADD CONSTRAINT PRIMARY KEY( product_id )> CONSTRAINT product_id_info_pk;
>
> SELECT p.product_id, pii.price, pii.description
> FROM product as p, product_id_info as pii
> WHERE p.product_id = pii.product_id
> AND p.product_id = 20;>
> As to any existing code that's already dependent on the procedure, just
> rewrite it to fetch the data from the table instead of from the IF..
> ELIF...END IF clause!
>
> Sounds like you're suffering from the hammer syndrome.
>
This is a very old production system I'm trying to maintain. All the
business logic was written on 4gl. Slowly some of the logic has been
converted to stored procedures so they can be used by external
applications without needing to rewrite the business logic on the
client application.
Maybe I chose a bad example by using a products scenario. The stored
proc in the example was something I wrote real quick to demonstrate
the errors Im getting. The real problem involves a more complicated
setup that involves a lot of business logic. Following the example of
a price/description, the price would be calculated following some
rules that are not represented in any tables. I wasn't around when
they decided to do it this way and I cant change most of this stuff
without breaking the rest of the system.
If I could execute a stored procedure that returns multiple columns
inside a query that would save me from messing with the legacy code or
having to rewrite the logic on the client application.
Thanks for your reply
Hi there,
Arts remark and warning is correct. Still there are situations
where you "inherited" such a procedure, cant redo the
whole design and must make it usable in selects. Often
wrappers for the individual values are ok - i.e. if performance
is not a big issue. If it is, there is no easy way.
The following works for me:
Lets assume your procedure name is myproc and it returns
two values a of type a_type and b of type b_type. Lets also assume
it has one input parameter inp of some type. You want to call
the procedure in a select on table mytable on col0. Some other
column col1 of this table is also to be selected.
Apparently
SELECT myproc(col0), col1 FROM mytable WHERE ...
cant work as you know.
Now the following steps will get you what you want:
1. Define a rowtype myrowtype containing fields a of a_type
and b of b_type
2. Write a wrapper mywrapper for your stored procedure
returning myrowtype by calling the original procedure and setting
the values a and b in the myrowtype object being returned.
3. This wrapper procedure can now be used in selects:
SELECT mywrapper(col0), col1 FROM mytable
will work. But: most clients will have problems in getting
a rowtype value from the resultset.
4. Therefore, we rewrite the result to a simple resultset:
SELECT val.a, val.b, col1 FROM
TABLE (
MULTISET(
SELECT mywrapper(col0) AS val, col1 FROM mytable WHERE...
)
)
It is a good idea to hide this statement in a view.
Hope this helps.
Regards,
Dirk
--
--
-- Dipl.-Math. Dirk Gunsth'vel
-- -professional services-
--
-- Dirk Gunsth'vel IT Systemanalyse - GunCon
-- Hammer Str. 13
-- D-48153 Muenster
-- phone: +49 (0) 251 28446- 0
-- fax: +49 (0) 251 28446-55
-- web: http://www.GunCon.de
-- email: info@GunCon.de
-- UStId: DE 189527667
--
-- 'One now understands why some animals eat their young.'
-- (Andrew in 'Bicentennial Man' 1999)
"brenddie" <brenddie@gmail.com> schrieb im Newsbeitrag
news:18e77dff-49ba-4b97-b7d5-efa9271b5362@u46g2000hsc.googlegroups.com...
> Is there a way of using a stored procedure that returns multiple
> columns in query?
>
> I have a stored proc that returns 2 columns and would like to use it
> inside a query. Something like this:
>
> select product_id,get_product_info(1) from products;>
> assuming get_product_info() returns price and description
>
> This is a simplification of the problem and not the real problem I'm
> trying to solve. The stored procedure I want to use has business logic
> that cant be implemented just linking tables.
> Currently the stored procedure is used in 4gl without issues.
>
> So far I have tried different variations of the query but haven't have
> luck.
> Using isql/dbacces running the following:
>
> execute procedure get_product_info(1) ;>
> 684: Function (informix.get_product_info) returns too many values.>
>
> select id,get_product_info(id) from tmpproducts;>
> 684: Function (informix.get_product_info) returns too many values.>
>
> SELECT * FROM TABLE(FUNCTION(get_product_info(1) )) as x1(p1,p2);>
> 9786: Only iterative UDR allowed in this context.>
>
>
>
>
>
> scripts for the test:
>
> create table 'informix'.tmpproducts (
> id CHAR(20)
> )
> extent size 32 next size 32
> lock mode page;
>
>
> CREATE PROCEDURE get_product_info(product_id INTEGER ) RETURNING> MONEY(10,2), char(20);
>
> DEFINE product_price MONEY(10,2);
> DEFINE product_desc char(20);
>
>
>
> IF(product_id = 1)THEN
> LET product_price = "$10";
> LET product_desc = "JUICE";
> ELIF (product_id = 1)THEN
> LET product_price = "$20";
> LET product_desc = "BEER";
> ELIF (product_id = 1)THEN
> LET product_price = "$40";
> LET product_desc = "NUTS";
> ELIF (product_id = 1)THEN
> LET product_price = "$80";
> LET product_desc = "TOMATOES";
> ELSE
> LET product_price = "0";
> LET product_desc = "ERROR";
> END IF
>
> RETURN product_price,product_desc;
>
> END PROCEDURE;
>
>
>
>
On Oct 6, 7:58 pm, "Dirk Gunsthövel" <d...@guncon.de> wrote:
> Hi there,
>
> Arts remark and warning is correct. Still there are situations
> where you "inherited" such a procedure, cant redo the
> whole design and must make it usable in selects. Often
> wrappers for the individual values are ok - i.e. if performance
> is not a big issue. If it is, there is no easy way.
>
> The following works for me:
>
> Lets assume your procedure name is myproc and it returns
> two values a of type a_type and b of type b_type. Lets also assume
> it has one input parameter inp of some type. You want to call
> the procedure in a select on table mytable on col0. Some other
> column col1 of this table is also to be selected.
>
> Apparently
> SELECT myproc(col0), col1 FROM mytable WHERE ...
> cant work as you know.
>
> Now the following steps will get you what you want:
>
> 1. Define a rowtype myrowtype containing fields a of a_type
> and b of b_type
>
> 2. Write a wrapper mywrapper for your stored procedure
> returning myrowtype by calling the original procedure and setting
> the values a and b in the myrowtype object being returned.
>
> 3. This wrapper procedure can now be used in selects:
> SELECT mywrapper(col0), col1 FROM mytable
> will work. But: most clients will have problems in getting
> a rowtype value from the resultset.
>
> 4. Therefore, we rewrite the result to a simple resultset:
> SELECT val.a, val.b, col1 FROM
> TABLE (
> MULTISET(
> SELECT mywrapper(col0) AS val, col1 FROM mytable WHERE...
> )
> )>
> It is a good idea to hide this statement in a view.
>
> Hope this helps.
>
> Regards,
> Dirk
>
This sounds exactly like what Im looking for. I created a custom data
type with the required fields. Then tried to create a wrapper but I'm
stuck trying to return the custom data type.
Following my previous example,
Created a data type
create row type 'informix'.product_info_type (
price MONEY(12,2),
description VARCHAR(100))
Then tried to create a wrapper around get_product_info()
CREATE PROCEDURE get_product_info_wrapper(product_id INTEGER )
RETURNING product_info_type; DEFINE product_info_type type product_info_type;
CALL get_product_info(product_id)
RETURNING product_info_type.price, product_info_type.description;
RETURN product_info_type;
END PROCEDURE;
But I'm stuck trying to figure out the correct usage of row types. I
haven't worked with custom data types/rows before and cant find the
correct way of declaring and returning row types. This must be basic
stuff but I cant find any examples on the documentation or anywhere on
the internet.
How do you define, assign and return row types ?
Thanks
hmmm....
create row type mytype ( a int, b char(18));
create function x( myin int)
returning mytype;
define ret mytype;
define anint int;
define achar char(18);
let ret = row(1,"novalue") :: mytype ;
select customer_num , lname into anint , achar
from customer where customer_num = myin;
let ret = row(anint,achar) :: mytype ;
return ret;
end function;
select x(customer_num) , * from customer
.... i would NOT put all the logic into spl, this is a sure way to
kill yourself and asking for problems,
it will/can cause memory issues locking etc etc.
please reconsider, use spls when needed, too much and you will be in
trouble.
Superboer.
On 7 okt, 05:39, brenddie <brend...@gmail.com> wrote:
> On Oct 6, 7:58 pm, "Dirk Gunsthövel" <d...@guncon.de> wrote:
>
>
>
> > Hi there,
>
> > Arts remark and warning is correct. Still there are situations
> > where you "inherited" such a procedure, cant redo the
> > whole design and must make it usable in selects. Often
> > wrappers for the individual values are ok - i.e. if performance
> > is not a big issue. If it is, there is no easy way.
>
> > The following works for me:
>
> > Lets assume your procedure name is myproc and it returns
> > two values a of type a_type and b of type b_type. Lets also assume
> > it has one input parameter inp of some type. You want to call
> > the procedure in a select on table mytable on col0. Some other
> > column col1 of this table is also to be selected.
>
> > Apparently
> > SELECT myproc(col0), col1 FROM mytable WHERE ...
> > cant work as you know.
>
> > Now the following steps will get you what you want:
>
> > 1. Define a rowtype myrowtype containing fields a of a_type
> > and b of b_type
>
> > 2. Write a wrapper mywrapper for your stored procedure
> > returning myrowtype by calling the original procedure and setting
> > the values a and b in the myrowtype object being returned.
>
> > 3. This wrapper procedure can now be used in selects:
> > SELECT mywrapper(col0), col1 FROM mytable
> > will work. But: most clients will have problems in getting
> > a rowtype value from the resultset.
>
> > 4. Therefore, we rewrite the result to a simple resultset:
> > SELECT val.a, val.b, col1 FROM
> > TABLE (
> > MULTISET(
> > SELECT mywrapper(col0) AS val, col1 FROM mytable WHERE...
> > )
> > )>
> > It is a good idea to hide this statement in a view.
>
> > Hope this helps.
>
> > Regards,
> > Dirk
>
> This sounds exactly like what Im looking for. I created a custom data
> type with the required fields. Then tried to create a wrapper but I'm
> stuck trying to return the custom data type.
>
> Following my previous example,
>
> Created a data type
>
> create row type 'informix'.product_info_type (
> price MONEY(12,2),
> description VARCHAR(100))
>
> Then tried to create a wrapper around get_product_info()
>
> CREATE PROCEDURE get_product_info_wrapper(product_id INTEGER )
> RETURNING product_info_type;> DEFINE product_info_type type product_info_type;
>
> CALL get_product_info(product_id)
> RETURNING product_info_type.price, product_info_type.description;
>
> RETURN product_info_type;
>
> END PROCEDURE;
>
> But I'm stuck trying to figure out the correct usage of row types. I
> haven't worked with custom data types/rows before and cant find the
> correct way of declaring and returning row types. This must be basic
> stuff but I cant find any examples on the documentation or anywhere on
> the internet.
>
> How do you define, assign and return row types ?
>
> Thanks
See my comment below in the procedure declaration:
Art
On Mon, Oct 6, 2008 at 11:39 PM, brenddie <brenddie@gmail.com> wrote:
> On Oct 6, 7:58 pm, "Dirk Gunsthövel" <d...@guncon.de> wrote:
> > Hi there,
> >
> > Arts remark and warning is correct. Still there are situations
> > where you "inherited" such a procedure, cant redo the
> > whole design and must make it usable in selects. Often
> > wrappers for the individual values are ok - i.e. if performance
> > is not a big issue. If it is, there is no easy way.
> >
> > The following works for me:
> >
> > Lets assume your procedure name is myproc and it returns
> > two values a of type a_type and b of type b_type. Lets also assume
> > it has one input parameter inp of some type. You want to call
> > the procedure in a select on table mytable on col0. Some other
> > column col1 of this table is also to be selected.
> >
> > Apparently
> > SELECT myproc(col0), col1 FROM mytable WHERE ...
> > cant work as you know.
> >
> > Now the following steps will get you what you want:
> >
> > 1. Define a rowtype myrowtype containing fields a of a_type
> > and b of b_type
> >
> > 2. Write a wrapper mywrapper for your stored procedure
> > returning myrowtype by calling the original procedure and setting
> > the values a and b in the myrowtype object being returned.
> >
> > 3. This wrapper procedure can now be used in selects:
> > SELECT mywrapper(col0), col1 FROM mytable
> > will work. But: most clients will have problems in getting
> > a rowtype value from the resultset.
> >
> > 4. Therefore, we rewrite the result to a simple resultset:
> > SELECT val.a, val.b, col1 FROM
> > TABLE (
> > MULTISET(
> > SELECT mywrapper(col0) AS val, col1 FROM mytable WHERE...
> > )
> > )> >
> > It is a good idea to hide this statement in a view.
> >
> > Hope this helps.
> >
> > Regards,
> > Dirk
> >
>
>
> This sounds exactly like what Im looking for. I created a custom data
> type with the required fields. Then tried to create a wrapper but I'm
> stuck trying to return the custom data type.
>
> Following my previous example,
>
> Created a data type
>
> create row type 'informix'.product_info_type (
> price MONEY(12,2),
> description VARCHAR(100))
>
> Then tried to create a wrapper around get_product_info()
>
> CREATE PROCEDURE get_product_info_wrapper(product_id INTEGER )
> RETURNING product_info_type;> DEFINE product_info_type type product_info_type;
Two syntax problems in the line above:
1. the variable has the same name as the type, and
2. there is an extraneous keyword 'type' there.
3. need the keyword 'ROW'
So, the declaration should be something like:
DEFINE product_info_var ROW product_info_type;
>
>
> CALL get_product_info(product_id)
> RETURNING product_info_type.price, product_info_type.description;
>
> RETURN product_info_type;
Which means this becomes:
CALL get_product_info( product_id )
RETURNING product_info_var.price, product_info_var.description;
RETURN product_info_var;
>
>
> END PROCEDURE;
>
> But I'm stuck trying to figure out the correct usage of row types. I
> haven't worked with custom data types/rows before and cant find the
> correct way of declaring and returning row types. This must be basic
> stuff but I cant find any examples on the documentation or anywhere on
> the internet.
>
> How do you define, assign and return row types ?
>
>
> Thanks
> _______________________________________________
> 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.
Thanks both for your responses. I go it working using the
recommendations you provided.
following the previous example, the wrapper became
CREATE PROCEDURE get_product_info_wrapper(product_id INTEGER )
RETURNING product_info_type;
--DEFINE product_info_var ROW product_info_type;
DEFINE price MONEY(12,2);
DEFINE description VARCHAR(100);
CALL get_product_info(product_id)
RETURNING price,description;
RETURN ROW(price,description)::product_info_type;
END PROCEDURE;
and running a test
select id,get_product_info_wrapper(id) from tmpproducts;id 4
(expression) ROW('$80.00','TOMATOES ')
id 3
(expression) ROW('$40.00','NUTS ')
id 1
(expression) ROW('$10.00','JUICE ')
id 2
(expression) ROW('$20.00','BEER ')
Tonight I'll start testing this on the real production functions. The
only weird thing is that running this from isql gives an error:
1820: Host variable type has been changed between fetches or puts.
dbaccess runs it without issue. I'll test later tonight using ODBC
and .NET to determine if a view will be needed.
Thanks all
Related threads
- Connection break during waiting for resultset - how to deal with?
- Oracle ?
- EGL Licensing
- Max Locks Forever
- Re: Function for nth bit set?