Re: SQL UDTF on iSeries - executing dynamic SQL or varyingselectstatements???

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

"Elvis Budimlic"
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote in message news:007601c87d51$3878e850$6e01a8c0@xxxxxxxx

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?

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email:

MIDRANGE-L@xxxxxxxxxxxx

To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.