RE: SQL UDTF on iSeries - executing dynamic SQL orvaryingselectstatements???

RH,

I've looked at the UDTF documentation on InfoCenter. There appears to be a
limitation of one (1) RETURN statement in the SQL-routine-body.
This is what you're running into. Verbatim from the InfoCenter:

"If the SQL-routine-body is a compound statement, it must contain exactly
one RETURN statement and it must be executed when the function is called."

Based on that, I don't see how you could have two RETURNs other than doing
what you're already doing, or perhaps by using VIEWs instead.
I tried a simple sample with a view as intermediary:

CREATE FUNCTION sampleUDTF(var VARCHAR(1))
RETURNS TABLE (field1 CHAR(10), field2 integer)
LANGUAGE SQL
MODIFIES SQL DATA
DETERMINISTIC
NOT FENCED
EXTERNAL ACTION
DISALLOW PARALLEL
BEGIN
DECLARE VIEW_DOES_NOT_EXIST CONDITION FOR SQLSTATE '42704';
DECLARE CONTINUE HANDLER FOR VIEW_DOES_NOT_EXIST
BEGIN
-- do nothing
END;
DROP VIEW QTEMP.V1;

IF VAR = 'x' THEN
CREATE VIEW QTEMP.V1 (FIELD1,FIELD2) AS (SELECT 'Var = X', 1 FROM
SYSIBM.SYSDUMMY1);
ELSE
CREATE VIEW QTEMP.V1 (FIELD1,FIELD2) AS (
SELECT 'Var <> X', 2 FROM SYSIBM.SYSDUMMY1);
END IF;
RETURN SELECT * FROM QTEMP.V1;
END;

SELECT * FROM TABLE(sampleUDTF('x')) myudtf;
SELECT * FROM TABLE(sampleUDTF('y')) myudtf;

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: SQL UDTF on iSeries - executing dynamic SQL
orvaryingselectstatements???

Thanks for the response Elvis. However, neither article covers varying
SELECT statements. I've tried lots of things...kicking off IF THEN ELSE
control flow within the RETURN...doesn't work. I've also tried building
CTE's within IF..THEN..ELSE control flow and then always executing the same
RETURN SELECT statement but I can't seem to define CTE's that way.

Any other ideas?

Thanks. RH