SQL - Select Output of a Routine into Table-CodePudding

I am trying to select the output of a Function into a Table ( Informix Server 11.1, Database Cisco UCCX). The function is executing fine but when I select its results into a Table, I get an error.

1. Simple Execution of routine is okay i.e.

Execute function sp_details_agent('2022-08-16 21:00:00','2022-08-17 20:59:59')

// above execution gives 03 column output : node_id | resourcename | callshandled

2. Following Query fails:

   select node_id C1 , resourcename C2 , callshandled C3  FROM TABLE(function sp_details_agent('2022-08-16 21:00:00','2022-08-17 20:59:59') );

Error is: SQL Error (-217): Column (node_id) not found in any table in the query (or SLV is undefined).

Please advise what I am missing.

CodePudding user response：

You can simply treat the EXECUTE FUNCTION statement as if it was a SELECT statement:

prepare a statement,
declare a cursor for the statement,
open the cursor,
fetch the data (repeatedly),
close the cursor,
free the cursor,
free the statement.

Since you don't identify the context where you're running this, it's hard to know what syntax to use for the outlined operations.

If you must use a SELECT statement, then you need to modify your attempt:

SELECT node_id C1, resourcename C2, callshandled C3
  FROM TABLE(FUNCTION sp_details_agent('2022-08-16 21:00:00', '2022-08-17 20:59:59'))
          AS results(node_id, resourcename, callshandled);

See:

Tested using IDS 14.10.FC1 on RHEL 7.4 (and a different but equivalent procedure and a different data table).

CodePudding user response：

I believe it depends on what type of function it is.

If you can turn it into a Table-valued function, then you can select against it like:

select node_id C1 , resourcename C2 , callshandled C3 from sp_details_agent('2022-08-16 21:00:00','2022-08-17 20:59:59')

You would just need to make sure the function call returns a table. one example would be:

Create function [dbo].[sp_details_agent_table](@StartDate date, @EndDate date)
returns table
as 
return 
select
...