SPL Procedures in DB-access

Hi, I have been having trouble trying to incorporate a SPL procedure into SQL. I have an SPL procedure that returns a set of rows and would like to use as a subselect ex: select id from temp where id in (mysplfunction()); 686: Function (informix.mysplfunction) hasreturned more than one row. Error in line 1Near character position 1 executing the SPL procedure gives the following results execute procedure mysplfunction();(expression) 2 8 7 4 6 5 9 1 8 row(s) retrieved. The Informix documentation states that "You can use a Stored Procedure anywhere in an SQL statement where a sub query is allowed ...." Any suggestions will be much appreciated. Sent via Deja.com http://www.deja.com/ Before you buy.

gauthamkrishnamurti@my-deja.com wrote: > I have been having trouble trying to incorporate a > SPL procedure into SQL. I have an SPL procedure > that returns a set of rows and would like to use > as a subselect > > ex: > select id from temp where id in (mysplfunction());> > 686: Function (informix.mysplfunction) has> returned more than one row. > Error in line 1> Near character position 1 > > [...] > > The Informix documentation states that "You can > use a Stored Procedure anywhere in an SQL > statement where a sub query is allowed ...." > > Any suggestions will be much appreciated. Arguably, a documentation error; possibly a version-dependent error. Which version of which manual contains the quote you use? Which version of which database server are you using? On which platform? In general, in an SQL statement, you can only use stored procedures which return a single value - one row of data containing one column, at least in the versions I'm familiar with, meaning those prior to the IDS.2000 and Foundation.2000 (9.20) releases, and possibly 7.3x. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>

In article <388EA0DF.B488F557@earthlink.net>, Jonathan Leffler <jleffler@earthlink.net> wrote: > > > gauthamkrishnamurti@my-deja.com wrote: > > > I have been having trouble trying to incorporate a > > SPL procedure into SQL. I have an SPL procedure > > that returns a set of rows and would like to use > > as a subselect > > > > ex: > > select id from temp where id in (mysplfunction());> > > > 686: Function (informix.mysplfunction) has> > returned more than one row. > > Error in line 1> > Near character position 1 > > > > [...] > > > > The Informix documentation states that "You can > > use a Stored Procedure anywhere in an SQL > > statement where a sub query is allowed ...." > > > > Any suggestions will be much appreciated. > > Arguably, a documentation error; possibly a version-dependent error. > > Which version of which manual contains the quote you use? > Which version of which database server are you using? > On which platform? > > In general, in an SQL statement, you can only use stored procedures > which return a single value - one row of data containing one column, > at least in the versions I'm familiar with, meaning those prior to > the IDS.2000 and Foundation.2000 (9.20) releases, and possibly 7.3x. > > -- > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN > #include <disclaimer.h> > > The manual that I was quoting is the Informix Training Manual - "Incorporating Stored Procedures and Triggers" - version 03-95, in chapter 2 on page 37. I'm not exactly sure what version number to give you but this is what I get when I run onstat -u INFORMIX-Universal Server Version 9.14.UC5.... So, in a nutshell, I cannot incorporate SPs that return sets, in SQL. Would you have any suggestions as to how I might do it or something on those lines. My current work around is to create another SP that does this foreach execute procedure mysplfunction() into id foreach select uid into userid from user, group where group.id = id; return userid with resume; end foreach; end foreach; To me, this seems inefficient - is there a better or more efficient way. Thanks for the help. Gauth Sent via Deja.com http://www.deja.com/ Before you buy.