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 thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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 [javascript protected email address].