Temp table for user defined functions

Question

I am trying to use temp table in user defined function for DB2. I am trying to do this in data studio but the code below is not working. How can I make this work?

Thanks.

CREATE FUNCTION BELSIZE.TEST (aSTRING VARCHAR(50))
RETURNS TABLE(
column1 INTEGER
 )

F1: BEGIN ATOMIC

DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR stmt;

SET v_dynStmt = 'SELECT 1 column1 from sysibm.sysdummy1';

PREPARE stmt FROM v_dynStmt;
OPEN c1;

RETURN
END

Answer 1

You have syntax errors in your code, more details below. Apart from syntax errors, your title mentions temp table but your code does not, so your question is poor.

Never write "...is not working", instead write the exact SQLCODE and SQLSTATE and message that you see.

When asking for help with Db2, always write in the question the Db2-version and the operating-system (Z/OS, i-Series, Linux/Unix/Windows) on which the Db2-server runs, because the answer can depend on those facts. Different versions of Db2 for different operating systems have different capabilities and different syntax.

If you want to use cursors for result-sets then use SQL PL stored-procedures, because there are fewer restrictions.

SQL table functions are suitable when you don't need to declare a cursor for result-set.

Db2-LUW prevents you from declaring a cursor in an SQL table function when you use BEGIN ATOMIC.

If you are not using BEGIN ATOMIC, Db2-LUW (current versions, i.e. v11.1) lets you declare a cursor in an SQL UDF but you cannot use that cursor directly to return the result-set, as you can do inside SQL PL stored procedures.

For your example, the syntax below is valid and also useless, so consider using an SQL PL procedure instead:

--#SET TERMINATOR @

CREATE or replace FUNCTION BELSIZE.TEST (aSTRING VARCHAR(50))
RETURNS TABLE( column1 INTEGER)
language sql
specific belsize.test
BEGIN atomic
RETURN select 1 as column1 from sysibm.sysdummy1 ;
END
@

select * from table(belsize.test('a')) as t
@