Please find the code for creating a temporary table and access it in UDF functions.
While we do this we obtain a compile time error stating "A declared temporary table cannot be used in the given
context."

CODE:

DECLARE GLOBAL TEMPORARY TABLE

SESSION.TEMP (id INT, data VARCHAR(10))

ON COMMIT PRESERVE ROWS;

----------------------------- Commands Entered ------------------------------

CREATE FUNCTION fn_DocumentCount2 (v_GroupID CHAR(16) FOR BIT DATA )

RETURNS INTEGER

LANGUAGE SQL

BEGIN ATOMIC

DECLARE v_documentcount INTEGER;



delete from SESSION.TEMP;

RETURN 10;

END!

ERROR:

SQL0526N The requested function does not apply to declared temporary tables.


Explanation:

A declared temporary table cannot be used in the given context

could you please tell me how do we resolve this error?

As the error indicates, you cannot use a global temporary table in a UDF.

Andy

As a work-around, you can put the DGTT statement into a stored procedure along with all processing on the DGTT and then call this procedure from a UDF.

Hi Folks,

Thanks for your timely response, could you please tell me how do this with a sample code snippet, because am not a db guy and trying my level best to solve this problem.

It would be a great favor if you could help me out on this.

Thanks,
kamal