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)
MODIFIES SQL DATA
DECLARE VIEW_DOES_NOT_EXIST CONDITION FOR SQLSTATE '42704';
DECLARE CONTINUE HANDLER FOR VIEW_DOES_NOT_EXIST
-- do nothing
DROP VIEW QTEMP.V1;
IF VAR = 'x' THEN
CREATE VIEW QTEMP.V1 (FIELD1,FIELD2) AS (SELECT 'Var = X', 1 FROM
CREATE VIEW QTEMP.V1 (FIELD1,FIELD2) AS (
SELECT 'Var <> X', 2 FROM SYSIBM.SYSDUMMY1);
RETURN SELECT * FROM QTEMP.V1;
SELECT * FROM TABLE(sampleUDTF('x')) myudtf;
SELECT * FROM TABLE(sampleUDTF('y')) myudtf;
Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
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.
This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact