469,326 Members | 1,538 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

Help with returning output of FOR loop in a DB2 function

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...
Expand|Select|Wrap|Line Numbers
  1.     CREATE OR ALTER PROCEDURE get_active_ay_classes ( academicYearId INTEGER )
  2.     RETURNS (CLASS_ID INTEGER,
  3.             CLASS_NAME    VARCHAR(200)
  4.         )
  5.     AS
  6.         DECLARE variable classId    integer;
  7.         DECLARE variable linkedClassId integer;
  8.         DECLARE variable className    varchar(200);
  9.         DECLARE variable linkedClassName varchar(200);
  10.         DECLARE variable linkedClassName2 varchar(200);
  11.     begin
  12.  
  13.         /*
  14.             Select only the classes that have students in them
  15.         */
  16.         FOR
  17.         SELECT distinct(c.class_id),
  18.                          COALESCE (c.class ||' ('|| COALESCE( co.preferred_name, co.firstname) ||' '|| co.surname||')', c.class) as class
  19.                          FROM      class c
  20.                          INNER JOIN class_enrollment ce ON ce.class_id = c.class_id
  21.                          LEFT JOIN class_teacher  ct ON c.class_id   = ct.class_id
  22.                          LEFT JOIN teacher        t  ON ct.teacher_id = t.teacher_id
  23.                          LEFT JOIN contact        co ON t.contact_id = co.contact_id
  24.                          WHERE     c.academic_year_id = :academicYearId AND ct.is_primary = 1
  25.         INTO
  26.             :classId, :className
  27.         DO
  28.             BEGIN
  29.                 /* now find the linked classes to this class as well */
  30.                 FOR    
  31.                     SELECT distinct (c2.class_id), 
  32.                     COALESCE (c2.class ||' ('|| COALESCE( co2.preferred_name, co2.firstname) ||' '|| co2.surname||')', c2.class) as class
  33.                     FROM class c2
  34.                     INNER JOIN class_link cl2  ON c2.class_id = cl2.class_id2
  35.                     LEFT JOIN class_teacher  ct2 ON cl2.class_id2   = ct2.class_id
  36.                     LEFT JOIN teacher        t2  ON ct2.teacher_id = t2.teacher_id
  37.                     LEFT JOIN contact        co2 ON t2.contact_id = co2.contact_id
  38.                     WHERE cl2.class_id1 = :classId
  39.                     INTO
  40.                         :linkedClassId, :linkedClassName
  41.                 DO
  42.                     BEGIN
  43.                         class_id = :linkedClassId;
  44.                         class_name = :linkedClassName;
  45.                         suspend;        
  46.                     END
  47.                 class_name = :className;
  48.                 class_id    = :classId;
  49.                 suspend;
  50.             END
  51.     end !!
  52.  
And here is the same procedure written as a DB2 table function...
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION GET_ACTIVE_AY_CLASSES( ACADEMICYEARID INTEGER )
  2.     RETURNS TABLE (
  3.        CLASS_ID INTEGER,
  4.        CLASS_NAME VARCHAR(200)
  5.     )
  6.     NO EXTERNAL ACTION
  7.     MODIFIES SQL DATA
  8.     LANGUAGE SQL
  9. ------------------------------------------------------------------------
  10. -- SQL UDF (Table)
  11. ------------------------------------------------------------------------
  12. F1: BEGIN ATOMIC
  13.     --Variable declaration
  14.     declare classId INTEGER;
  15.     declare linkedClassId INTEGER;
  16.     declare className VARCHAR(200);
  17.     declare linkedClassName VARCHAR(200);
  18.     declare linkedClassName2 VARCHAR(200);
  19.     declare CLASS_ID INTEGER;
  20.     declare CLASS_NAME VARCHAR(200);
  21.     declare v_TempTableInstance INTEGER;
  22.  
  23.     --Now we determine a unique number for the instance
  24.     set v_TempTableInstance = (select cast((rand()*1000000) as integer) from sysibm.sysdummy1);
  25.     DELETE from TMP_GETACTIVEAYCLASSES where INST_NUM = v_TempTableInstance;
  26.  
  27.     -- now find the linked classes to this class as well
  28.     FOR v_row1 AS  
  29.         SELECT distinct(C.CLASS_ID) FORLOOP1_CLASSID
  30.         ,COALESCE(C.CLASS || ' (' || COALESCE(CO.PREFERRED_NAME,CO.FIRSTNAME) || ' ' || CO.SURNAME || ')',C.CLASS) FORLOOP1_CLASS
  31.         FROM CLASS C
  32.         INNER JOIN CLASS_ENROLLMENT CE ON CE.CLASS_ID = C.CLASS_ID
  33.         LEFT JOIN CLASS_TEACHER  CT ON C.CLASS_ID= CT.CLASS_ID
  34.         LEFT JOIN TEACHER  T  ON CT.TEACHER_ID = T.TEACHER_ID
  35.         LEFT JOIN CONTACT  CO ON T.CONTACT_ID = CO.CONTACT_ID
  36.         WHERE C.ACADEMIC_YEAR_ID = ACADEMICYEARID AND CT.IS_PRIMARY = 1
  37.     DO
  38.         set classId = v_row1.FORLOOP1_CLASSID;
  39.         set className = v_row1.FORLOOP1_CLASS;    
  40.         FOR v_row2 AS  
  41.             SELECT distinct(C2.CLASS_ID) FORLOOP2_CLASSID
  42.             ,COALESCE(C2.CLASS || ' (' || COALESCE(CO2.PREFERRED_NAME,CO2.FIRSTNAME) || ' ' || CO2.SURNAME || ')',C2.CLASS) FORLOOP2_CLASS
  43.             FROM CLASS C2
  44.             INNER JOIN CLASS_LINK CL2  ON C2.CLASS_ID = CL2.CLASS_ID2
  45.             LEFT JOIN CLASS_TEACHER  CT2 ON CL2.CLASS_ID2= CT2.CLASS_ID
  46.             LEFT JOIN TEACHER  T2  ON CT2.TEACHER_ID = T2.TEACHER_ID
  47.             LEFT JOIN CONTACT  CO2 ON T2.CONTACT_ID = CO2.CONTACT_ID
  48.             WHERE CL2.CLASS_ID1 = classId
  49.         DO
  50.             set linkedClassId = v_row2.FORLOOP2_CLASSID;
  51.             set linkedClassName = v_row2.FORLOOP2_CLASS;
  52.             SET CLASS_ID = linkedClassId;
  53.             SET CLASS_NAME = linkedClassName;
  54.             INSERT INTO TMP_GETACTIVEAYCLASSES VALUES(v_TempTableInstance,CLASS_ID,CLASS_NAME);
  55.         END FOR;
  56.         SET CLASS_NAME = className;
  57.         SET CLASS_ID    = classId;
  58.         INSERT INTO TMP_GETACTIVEAYCLASSES VALUES(v_TempTableInstance,CLASS_ID,CLASS_NAME);
  59.     END FOR;
  60.  
  61.     RETURN SELECT CLASS_ID, CLASS_NAME FROM TMP_GETACTIVEAYCLASSES where INST_NUM = v_TempTableInstance; 
  62. END
  63.  
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.
Jan 10 '08 #1
0 2760

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Todd Shillam | last post: by
3 posts views Thread by Tim::.. | last post: by
1 post views Thread by Ron St-Pierre | last post: by
1 post views Thread by Joe Van Meer | last post: by
3 posts views Thread by Chris H | last post: by
Thekid
3 posts views Thread by Thekid | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.