SQL - Select Output of a Routine into Table

user_7007254 0
Viewed 68

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.

cisco informix sql database
2 Answers

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).

user_151680

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
...
user_197200730
Related