DB2 Temporary Table within Function

Question

I have got simple functions to work in DB2 but I am unable to get any functions to work which declare a global temporary table. I am aware the DB2 version and platform is vastly different but I thought this should be possible as it worked for this poster.

CREATE FUNCTION FuncTest1 ()
    RETURNS TABLE
    (
        USE_NAME VARCHAR(48),
        USE_PARTNER_LINK FLOAT
    )
        LANGUAGE SQL
        MODIFIES SQL DATA
        DETERMINISTIC
        BEGIN
        DECLARE GLOBAL TEMPORARY TABLE USE_TRUNC
        (
           USE_NAME VARCHAR(48) NULL,
           USE_PARTNER_LINK FLOAT NULL
        );
        INSERT INTO SESSION.USE_TRUNC
            (USE_NAME,USE_PARTNER_LINK)
        SELECT USE_NAME,USE_PARTNER_LINK FROM F_USERS;
        RETURN
        SELECT USE_NAME,USE_PARTNER_LINK FROM F_USERS;
        END

The errors vary widely as I try different things but this is the current output:

An unexpected token "USE_NAME" was found following " 
  RETURN 
  SELECT".  Expected tokens may include:  "(".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.9.78

Added fiddle

Answer 1

CREATE FUNCTION (SQL scalar, table, or row) statement, v9.7:

-| (3)| '-MODIFIES SQL DATA-----' ... |--+-RETURN----------------------+------------------------------| | (5) | +-Compound SQL (compiled)-----+ '-Compound SQL (inlined)------'
...
3. Valid if RETURNS specifies a table (that is, TABLE column-list). Also valid if RETURNS specifies a scalar result and the SQL-function-body is a compound SQL (compiled) statement.
...
5. The compound SQL (compiled) statement is supported only for an SQL-function-body in an SQL scalar function definition. It is not supported for SQL table function definitions.

CREATE FUNCTION (SQL scalar, table, or row) statement, v11.1:

-| (4) | '-MODIFIES SQL DATA-----'
...
4. Valid only for compiled scalar function definition and an inlined table function definition.

You can't use MODIFIED SQL DATA with Compound SQL (compiled) (BEGIN ... END) for a table function. And DECLARE GTT statement is not supported by Compound SQL (inlined) (BEGIN ATOMIC ... END).
So, rewrite your table function not using Declared GTTs & INSERTs. Or try Created GTT, but create them beforehand (not in the function body).

As for your case

You may use a single SELECT statement with Common Table Expression:

CREATE FUNCTION ...
...
RETURN
WITH 
SESSION_ALLREFERRALS AS 
(
SELECT
    REF.AL_NO AS AL_NO,        
    ENQ.E_KEY AS R_KEY,
    APP.A_2ND_NAME AS R_TRADING_NAME,
    CASE WHEN DOC1.DT_NAME is null THEN 'NONE' ELSE DOC1.DT_NAME END AS R_DROP1,
    REF.AL_DATE AS R_DATE,
    CASE WHEN DOC2.DT_NAME is null THEN 'Incomplete' ELSE DOC2.DT_NAME END AS R_DROP2,
    CAST(ENQ2.E_TOOLCOMM1 AS VARCHAR(48)) AS R_COMM1
FROM
    F_ENQUIRY ENQ INNER JOIN 
        F_APPLICANT_LINK REF ON REF.AL_KEY1 = ENQ.E_KEY INNER JOIN 
        F_APPLICANT APP ON APP.A_KEY = REF.AL_KEY2 AND REF.AL_TYPE1 = 2 AND REF.AL_TYPE2 = 1 LEFT JOIN 
        F_DOC_TYPES DOC1 ON DOC1.DT_NO = REF.AL_DROP1 LEFT JOIN 
        F_DOC_TYPES DOC2 ON DOC2.DT_NO = REF.AL_DROP2 INNER JOIN 
        F_ENQUIRY ENQ2 ON ENQ2.E_KEY = REF.AL_ENQ_LINK
WHERE
    ENQ.E_PRIORITY_LINK = 204 AND   
    ENQ.E_JOB_TYPE_LINK = 0
)
, 
SESSION_REFERRAL1 AS 
(
SELECT 
    MIN(AL_NO) AL_NO, 
    R_KEY
    FROM 
    SESSION_ALLREFERRALS 
    GROUP BY R_KEY
)
,
SESSION_REFERRAL2 AS 
(
SELECT 
    MIN(AL_NO) AL_NO, 
    R_KEY 
FROM 
    SESSION_ALLREFERRALS a2
WHERE
    NOT EXISTS (SELECT 1 FROM SESSION_REFERRAL1 R1 WHERE r1.AL_NO = a2.AL_NO fetch first 1 rows only)
GROUP BY 
    R_KEY
)
,
SESSION_REFERRAL3 AS 
(
SELECT 
    MIN(AL_NO) AL_NO, 
    R_KEY 
FROM 
    SESSION_ALLREFERRALS A3
WHERE
    NOT EXISTS (SELECT 1 FROM SESSION_REFERRAL1 R1 WHERE R1.AL_NO = A3.AL_NO fetch first 1 rows only) AND 
    NOT EXISTS (SELECT 1 FROM SESSION_REFERRAL2 R2 WHERE R2.AL_NO = A3.AL_NO fetch first 1 rows only)
GROUP BY 
    R_KEY
)
SELECT 
    *
FROM
    SESSION_REFERRAL1 R1 INNER JOIN
    SESSION_ALLREFERRALS A ON A.R_KEY = R1.R_KEY AND R1.AL_NO = A.AL_NO LEFT JOIN
    SESSION_REFERRAL2 R2 ON R2.R_KEY = R1.R_KEY LEFT JOIN
    SESSION_ALLREFERRALS A2 ON A2.R_KEY = R2.R_KEY AND R2.AL_NO = A2.AL_NO LEFT JOIN 
    SESSION_REFERRAL3 R3 ON R3.R_KEY = R1.R_KEY LEFT JOIN
    SESSION_ALLREFERRALS A3 ON A3.R_KEY = R3.R_KEY AND R3.AL_NO = A3.AL_NO
;

Answer 2

This would be a simpler way to write that function

CREATE FUNCTION FuncTest1 ()
RETURNS TABLE
(
    USE_NAME VARCHAR(48),
    USE_PARTNER_LINK FLOAT
)
    LANGUAGE SQL
    RETURN  SELECT USE_NAME,USE_PARTNER_LINK FROM F_USERS;