Filter Stored Procedure Result (Informix)

IBM Informix Dynamic Server Version 11.50.UC5XA.

I have a Informix database. This database contain a stored procedure with name of 'sp_agent_details' that get two DateTime Parameter. This stored procedure results about 27 columns and I want only few columns of it for reporting. I try this syntax:

select AGENT_NAME, AGENT_LOGIN_ID from 
       TABLE(sp_agent_detail('2014-02-04 04:00:00', '2014-02-04 23:00:00'))

This Result such Error:

java.sql.SQLException: Illegal SQL statement in SPL routine.

Next I try this syntax:

select AGENT_NAME, AGENT_LOGIN_ID from 
       TABLE(MULTISET{sp_agent_call_summary 
    ('2014-02-04 04:00:00', '2014-02-04 23:00:00')})

Result:

java.sql.SQLException: Function (informix.sp_agent_call_summary) 
returns too many values.

then I tried this:

select AGENT_NAME, AGENT_LOGIN_ID from 
   TABLE(sp_agent_detail('2014-02-04 04:00:00', '2014-02-04 23:00:00'))
   (AGENT_NAME, AGENT_LOGIN_ID, other columns that are returned by SP)

Result:

java.sql.SQLException: Illegal SQL statement in SPL routine.

Here is a illustration how works..

This was executed with dbaccess at Informix 11.50 FC9.
You can name your return parameters, but they are valid only when executed the procedure as procedure by self... not into select statement. There you need to name each column manually... check below.

References at IBM Informix 11.50 manual:

• select from syntax...

• procedure named return parameters

create procedure dummy ( param integer )
returning int as id , char(10) as name, date as created
define vid int;;
define vname char(10);;
define vcreate date;;

foreach c1 for 
  select first 10 tabid, tabname, created 
     into vid, vname, vcreate 
     from systables where tabid >= param

  return vid , vname, vcreate with resume ;;
end foreach ;;

end procedure
;
Routine created.

;

execute procedure dummy(10);

         id name       created

         10 syssyntabl 20/02/2014
         11 sysconstra 20/02/2014
         12 sysreferen 20/02/2014
         13 syschecks  20/02/2014
         14 sysdefault 20/02/2014
         15 syscoldepe 20/02/2014
         16 sysprocedu 20/02/2014
         17 sysprocbod 20/02/2014
         18 sysprocpla 20/02/2014
         19 sysprocaut 20/02/2014

10 row(s) retrieved.

select  * from table(dummy(10));

unnamed_col_1 unnamed_col_2 unnamed_col_3

           10 syssyntabl    20/02/2014
           11 sysconstra    20/02/2014
           12 sysreferen    20/02/2014
           13 syschecks     20/02/2014
           14 sysdefault    20/02/2014
           15 syscoldepe    20/02/2014
           16 sysprocedu    20/02/2014
           17 sysprocbod    20/02/2014
           18 sysprocpla    20/02/2014
           19 sysprocaut    20/02/2014

10 row(s) retrieved.

select  * from table(dummy(10)) as x(id,name,created);

         id name       created

         10 syssyntabl 20/02/2014
         11 sysconstra 20/02/2014
         12 sysreferen 20/02/2014
         13 syschecks  20/02/2014
         14 sysdefault 20/02/2014
         15 syscoldepe 20/02/2014
         16 sysprocedu 20/02/2014
         17 sysprocbod 20/02/2014
         18 sysprocpla 20/02/2014
         19 sysprocaut 20/02/2014

10 row(s) retrieved.

select  x.id,name from table(dummy(10)) x(id,name,created);

         id name

         10 syssyntabl
         11 sysconstra
         12 sysreferen
         13 syschecks
         14 sysdefault
         15 syscoldepe
         16 sysprocedu
         17 sysprocbod
         18 sysprocpla
         19 sysprocaut

10 row(s) retrieved.

Database closed.

StackExchange Database Administrators Q#60471, answer score: 5