SQL0526N when referencing a DGTT (Declared global temporary table) in a FUNCTION

Question

I would like to return content from a pre-created DGTT as a result-set for a table function.

But when I reference it, I get SQL SQL0526N / SQLSTATE 42995 error.

I can't see why. Documentation says only 3 reasons for 42995 sqlstate error:

Restrictions on the use of declared temporary tables: Declared temporary tables cannot:
- Be specified in an ALTER, COMMENT, GRANT, LOCK, RENAME or REVOKE statement (SQLSTATE 42995).
- Be referenced in an AUDIT, CREATE ALIAS, or CREATE VIEW statement (SQLSTATE 42995).
- Be specified in referential constraints (SQLSTATE 42995).

But none of them are being used.. The function is pretty simple:

BEGIN  ATOMIC 

    CALL DBAREP.SP_ROW_CHECKSUM(sSchema, sTable, sColumnList, sWhere, iRows) ;     

    RETURN 
         (SELECT * FROM SESSION.DBAREP_ROW_CHECKSUM ) ;
END@

What rule am I violating this time?

The docs do state that inlined functions (compound SQL inlined) cannot use dynamic SQL features, which to me excludes DGTT use. — mao, Nov 29 '21 at 15:11

score 1 · Accepted Answer · answered Nov 29 '21 at 14:58

1

It's really not explicitly stated in docs, but Declared GTTs can't be used in inlined compound statements (with BEGIN ATOMIC ... END).
You may use Created GTT in an inlined compound statement instead.

answered Nov 29 '21 at 14:58

Mark Barinstein

11,456
2
8
16

Thanks @Mark. hope the doc could be more clear on this... – Samuel Pizarro Nov 29 '21 at 15:45

SQL0526N when referencing a DGTT (Declared global temporary table) in a FUNCTION

1 Answers1