Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    11

    Unanswered: Error while defining UDF

    I am really new to DB2 and UDFs. I am facing compilation error on DB2 9.5 for windows at While statement in defining UDF.

    Code:
    	SET LIMIT = ( SELECT COUNT(*) FROM COLUMN_LIST);
    	set index = 1;
    
    	WHILE ( INDEX < LIMIT + 1) DO
    		SET ARRAY_REP[INDEX] = 0;
    		SET INDEX = INDEX + 1;
    	END WHILE;
    I read in other thread that suppoerted SQL statements in UDF are

    HTML Code:
    The following list of SQL-control-statements can be used within the dynamic compound statement:
    
    * FOR Statement
    * GET DIAGNOSTICS Statement
    * IF Statement
    * ITERATE Statement
    * LEAVE Statement
    * SIGNAL Statement
    * WHILE Statement 
    
    The SQL statements that can be issued are:
    
    * fullselect (A common-table-expression can precede the fullselect.)
    * Searched UPDATE
    * Searched DELETE
    * INSERT
    * SET variable statement
    Then What error I am doing in my code?
    Please advise.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What error message(code and text) did you get?
    You would find some more specific information in the message text.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can't use ARRAY in an UDF on DB2 9.5 for LUW,
    according to the manual "DB2 Version 9.5 for Linux, UNIX, and Windows SQL Reference, Volume 2 Updated March, 2008 SC23-5862-01".

    ARRAY in an UDF is supported on DB2 9.7 for LUW, with some restrictions.

    CREATE FUNCTION (SQL scalar, table, or row)
    .....
    .....
    .....
    Notes:
    .....
    4
    The compound SQL (compiled) statement is supported only for an
    SQL-function-body in an SQL scalar function definition in a non-partitioned
    database. It is not supported for SQL table function definitions or in
    partitioned database environments.

Posting Permissions

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