In an existing SQL UDF, we have developed a sql statement. The statement is dynamic as tables can change based on the parameters that are passed in.



Question: Is there a way to return a table with the result set of the sql statement? The issue is that the sql statement is generated - so it exists as part of a string (which can be very complex). I am open to look at an external UDF in C.

Create Function GetPeerGroup
(IN_ID Integer,
IN_Period date)
Returns table (
OUT_ID Integer)
...

set squery = 'select col1 from table1 where col1 in (select x from y where a = b) and col1 in (select x1 from y1 where a1 = b1)';

-- squery can become very complex

-- the goal is to execute the query and return the result set. I am looking into calling a C UDF that has an in parameter of varchar for the squery and returns a table.

As I already told you in the DB2 newsgroup: your tables do not change dynamically. Instead, you are querying a fixed set of tables depending on the parameters. So you could simply stick to static SQL (and SQL-bodied functions) and apply some logic to exclude/include the data from certain tables.

As you should have noticed, I would be willing to help you. But I asked you several times for a verbal description of the semantics of your table function. Nothing relevant was forthcoming so far, which prevents any real help.

Knut,

This can be considered a different issue.
I am looking at learning external UDF functions for C. In the db2 group, you requested sample data which takes authority with others.