I am not sure if having two RETURNs is supported.
You shouldn't have to have a global temporary table at all.
Check out following two links for couple of good examples how to create
UDTFs:
By Kent Milligan and David Andruchuk:
http://www-03.ibm.com/systems/i/software/db2/pdf/ExternalUDTF.pdf
By Scott Klement:
http://systeminetwork.com/article/udtf-ifs-directories
HTH, Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: SQL UDTF on iSeries - executing dynamic SQL or varying
selectstatements???
We're am working on a SQL UDTF on our iSeries and we're having trouble
executing dynamic SQL or varying select statements. The only method we've
been able to get to work is with the use of a global temporary table.
something like this:
BEGIN
IF var=x THEN
INSERT INTO SESSION.TEMP TABLE
SELECT * FROM blah blah blah;
ELSEIF var=y THEN
INSERT INTO SESSION.TEMP TABLE
SELECT * FROM blah blah blah;
END IF;
RETURN SELECT a, b, c FROM SESSION.TEMP TABLE;
END;
This works but I really don't want to use the temp table. You can't drop
the session table from the UDTF - you pretty much have to wait for the
connection to drop so the table will be dropped. Is there another way to do
this?
We've tried (without success) the statement below but the RETURN statement
has to be your last statement.
BEGIN
IF var=x THEN
RETURN SELECT * FROM blah blah blah;
ELSEIF var=y THEN
RETURN SELECT * FROM blah blah blah;
END IF;
END;
We've also tried building a SQL string, executing it, and passing it into a
cursor...but again, the last statement has to return a TABLE.
Anyone out there done this without a temp table?
midrange.com
