Re: 2 parameters from spl in a select statement

Your MULTISET statement doesn't work, Jonathon, but this does in IDS 9.3: CREATE PROCEDURE multiset_test(table_id INT) RETURNING MULTISET(ROW(table CHAR(18), items INT) NOT NULL); DEFINE data MULTISET(ROW(table CHAR(18), items INT) NOT NULL); INSERT INTO TABLE(data) SELECT tabname, nrows FROM systables WHERE tabid = table_id; RETURN data; END PROCEDURE; SELECT * FROM TABLE(multiset_test(2)); It's pretty difficult trying to use such a procedure joined to other tables in an efficient way! Regards, Doug Lawry www.douglawry.webhop.org "Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:chfxb.16267$n56.13453@newsread1.news.pas.earthlink.net... > > > Francisco Amauri wrote: > > > >>How can i do this query > >> > >>select spl_name("10") from table > >> > >>to return 2 parameters from this procedure (spl_name) > > Jean Sagi wrote: > > in the spl return 2 variables... > > That does not work, I'm afraid. That is, you can make the SP return > multiple values in each call, but you cannot then use it in a SELECT > statement. You have to use EXECUTE PROCEDURE spl_name("10"). That > will, if treated the same as a SELECT statement, return multiple > values in each row. > > Inside a SELECT statement, you cannot usually use 'cursory' procedures > which have RETURN ... WITH RESUME, nor can you usually use > procedures which return multiple values per call. > > The 'usually' is there because you may be able to do something with > > FROM TABLE(MULTISET(EXECUTE PROCEDURE cursory_multivalue_spl())) > > but the names of the table columns are problematic, etc. This is IDS > 9.x only, if at all. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ >