Help with returning output of FOR loop in a DB2 function

[davidsavill]

Hi All,

I am migrating a database from Firebird/Interbase to DB2 and have having issues with the stored procedures/functions.

I have a number of functions that loop over a FOR loop, each pass it performs some logic on the row and inserts the output data into a temp table. The only point of the insert is so that I can select the data in a RETURN statement at the end of the UDF.

In Firebird we could put these results directly into variables and then "SUSPEND" to return the table of results (which I then use in a selects, joins etc.). There was no need to use a temp table to store the data.

I am trying to not have to create an extra 100 static temp tables, and then have issues with locking and old data cleanups. Since the RETURN has to be the last thing in the UDF - I cant delete the temp data from the table in the UDF which means I have to write nightly cleanup scripts - feels like a bit of a dirty hack.

Here is the procedure in Firebird...

    CREATE OR ALTER PROCEDURE get_active_ay_classes ( academicYearId INTEGER )
    RETURNS (CLASS_ID INTEGER,
            CLASS_NAME    VARCHAR(200)
        )
    AS
        DECLARE variable classId    integer;
        DECLARE variable linkedClassId integer;
        DECLARE variable className    varchar(200);
        DECLARE variable linkedClassName varchar(200);
        DECLARE variable linkedClassName2 varchar(200);
    begin
 
        /*
            Select only the classes that have students in them
        */
        FOR
        SELECT distinct(c.class_id),
                         COALESCE (c.class ||' ('|| COALESCE( co.preferred_name, co.firstname) ||' '|| co.surname||')', c.class) as class
                         FROM      class c
                         INNER JOIN class_enrollment ce ON ce.class_id = c.class_id
                         LEFT JOIN class_teacher  ct ON c.class_id   = ct.class_id
                         LEFT JOIN teacher        t  ON ct.teacher_id = t.teacher_id
                         LEFT JOIN contact        co ON t.contact_id = co.contact_id
                         WHERE     c.academic_year_id = :academicYearId AND ct.is_primary = 1
        INTO
            :classId, :className
        DO
            BEGIN
                /* now find the linked classes to this class as well */
                FOR    
                    SELECT distinct (c2.class_id), 
                    COALESCE (c2.class ||' ('|| COALESCE( co2.preferred_name, co2.firstname) ||' '|| co2.surname||')', c2.class) as class
                    FROM class c2
                    INNER JOIN class_link cl2  ON c2.class_id = cl2.class_id2
                    LEFT JOIN class_teacher  ct2 ON cl2.class_id2   = ct2.class_id
                    LEFT JOIN teacher        t2  ON ct2.teacher_id = t2.teacher_id
                    LEFT JOIN contact        co2 ON t2.contact_id = co2.contact_id
                    WHERE cl2.class_id1 = :classId
                    INTO
                        :linkedClassId, :linkedClassName
                DO
                    BEGIN
                        class_id = :linkedClassId;
                        class_name = :linkedClassName;
                        suspend;        
                    END
                class_name = :className;
                class_id    = :classId;
                suspend;
            END
    end !!
 

And here is the same procedure written as a DB2 table function...

CREATE FUNCTION GET_ACTIVE_AY_CLASSES( ACADEMICYEARID INTEGER )
    RETURNS TABLE (
       CLASS_ID INTEGER,
       CLASS_NAME VARCHAR(200)
    )
    NO EXTERNAL ACTION
    MODIFIES SQL DATA
    LANGUAGE SQL
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
    --Variable declaration
    declare classId INTEGER;
    declare linkedClassId INTEGER;
    declare className VARCHAR(200);
    declare linkedClassName VARCHAR(200);
    declare linkedClassName2 VARCHAR(200);
    declare CLASS_ID INTEGER;
    declare CLASS_NAME VARCHAR(200);
    declare v_TempTableInstance INTEGER;
 
    --Now we determine a unique number for the instance
    set v_TempTableInstance = (select cast((rand()*1000000) as integer) from sysibm.sysdummy1);
    DELETE from TMP_GETACTIVEAYCLASSES where INST_NUM = v_TempTableInstance;
 
    -- now find the linked classes to this class as well
    FOR v_row1 AS  
        SELECT distinct(C.CLASS_ID) FORLOOP1_CLASSID
        ,COALESCE(C.CLASS || ' (' || COALESCE(CO.PREFERRED_NAME,CO.FIRSTNAME) || ' ' || CO.SURNAME || ')',C.CLASS) FORLOOP1_CLASS
        FROM CLASS C
        INNER JOIN CLASS_ENROLLMENT CE ON CE.CLASS_ID = C.CLASS_ID
        LEFT JOIN CLASS_TEACHER  CT ON C.CLASS_ID= CT.CLASS_ID
        LEFT JOIN TEACHER  T  ON CT.TEACHER_ID = T.TEACHER_ID
        LEFT JOIN CONTACT  CO ON T.CONTACT_ID = CO.CONTACT_ID
        WHERE C.ACADEMIC_YEAR_ID = ACADEMICYEARID AND CT.IS_PRIMARY = 1
    DO
        set classId = v_row1.FORLOOP1_CLASSID;
        set className = v_row1.FORLOOP1_CLASS;    
        FOR v_row2 AS  
            SELECT distinct(C2.CLASS_ID) FORLOOP2_CLASSID
            ,COALESCE(C2.CLASS || ' (' || COALESCE(CO2.PREFERRED_NAME,CO2.FIRSTNAME) || ' ' || CO2.SURNAME || ')',C2.CLASS) FORLOOP2_CLASS
            FROM CLASS C2
            INNER JOIN CLASS_LINK CL2  ON C2.CLASS_ID = CL2.CLASS_ID2
            LEFT JOIN CLASS_TEACHER  CT2 ON CL2.CLASS_ID2= CT2.CLASS_ID
            LEFT JOIN TEACHER  T2  ON CT2.TEACHER_ID = T2.TEACHER_ID
            LEFT JOIN CONTACT  CO2 ON T2.CONTACT_ID = CO2.CONTACT_ID
            WHERE CL2.CLASS_ID1 = classId
        DO
            set linkedClassId = v_row2.FORLOOP2_CLASSID;
            set linkedClassName = v_row2.FORLOOP2_CLASS;
            SET CLASS_ID = linkedClassId;
            SET CLASS_NAME = linkedClassName;
            INSERT INTO TMP_GETACTIVEAYCLASSES VALUES(v_TempTableInstance,CLASS_ID,CLASS_NAME);
        END FOR;
        SET CLASS_NAME = className;
        SET CLASS_ID    = classId;
        INSERT INTO TMP_GETACTIVEAYCLASSES VALUES(v_TempTableInstance,CLASS_ID,CLASS_NAME);
    END FOR;
 
    RETURN SELECT CLASS_ID, CLASS_NAME FROM TMP_GETACTIVEAYCLASSES where INST_NUM = v_TempTableInstance; 
END
 

I am looking for some help to re-write the above procedure so that the temp table is not necessary (as in Firebird).

Thanks everyone for your help, this one has me stumped!
D.