Results 1 to 5 of 5
  1. #1
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221

    Unanswered: Create Table Giving Problems In Udf

    Code:
    CREATE FUNCTION DB2USER.GETPLACEHOLDER( MSG_TEMPLATE VARGRAPHIC(1000) )
        RETURNS TABLE
            (   IDCOL   INTEGER,
                COLNAME VARCHAR(1000)
            )
        SPECIFIC DB2USER.GETPLACEHOLDER
    ------------------------------------------------------------------------
    -- SQL UDF (Table)
    ------------------------------------------------------------------------
    F1: BEGIN ATOMIC
    
        --DECLARE NEWS_TEMPLATE VARCHAR(100);
    
        CREATE TABLE TEMP_TABLE
            (
                IDCOL INTEGER
                NOT NULL
                    GENERATED ALWAYS AS IDENTITY
                    (
                        START WITH +1
    	    	    INCREMENT BY +1
    	    	    MINVALUE +1
    	    	    MAXVALUE +100
    	    	    NO CYCLE
    	    	    NO CACHE
                    ),
                COLNAME VARCHAR(1000)
            );
    
        WHILE (LOCATE('$',MSG_TEMPLATE) != 0)
        DO
            --logic statements
        END WHILE;
    
        RETURN SELECT IDCOL, COLNAME FROM TEMP_TABLE;
    
        DELETE TABLE TEMP_TABLE;
        
    END F1
    I have written the following UDF but it is giving me the following error...

    DB2USER.GETPLACEHOLDER: 14: [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "TEMP_TABLE" was found following " CREATE TABLE". Expected tokens may include: "<space>". LINE NUMBER=14. SQLSTATE=42601


    can i not create a table in UDF???

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    while creating the function the table is not present in the DB and so it is giving this error
    try creating table in dynamic statements at run time.
    plus this function can only be used only once as on second time it would fail saying table already exists.
    Dont know if DGTT are supported in functions , if supported use them
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    i tried creating the table dynamically.... but it also gives me an error when i try to define a cursor...

    my basic requirement is that i require return type as a table and values for the table i get one at a time....which i thought of inserting into the temp table....and then returning it....how should i go about it then now......

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use a COMMON TABLE EXPRESSION. You will not have to use a DECLARED GLOBAL TEMP TABLE (DGTT), or try to create a persisted table, both which can lead to problems (pre-existing objects).

    Andy

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Functions are not allowed to issue data modification statements (much less DDL statements). The only exception are table functions, which can execute INSERT/UPDATE/DELETE - but still no DDL.

    Besides Andy's suggestion to use a CTE, you could pack the same logic into some subselects, or call a procedure and do things in the procedure. However, you cannot (yet) return a result set of a stored procedure as results of a SQL-bodied table function. This may work with external table functions, but I haven't tried that yet.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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