Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    2

    Unanswered: creating a temporary table and access it in UDF functions

    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?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    As the error indicates, you cannot use a global temporary table in a UDF.

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Apr 2008
    Posts
    2
    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
    Last edited by kamal_001; 04-12-08 at 08:57.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •