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.
SET LIMIT = ( SELECT COUNT(*) FROM COLUMN_LIST);
set index = 1;
WHILE ( INDEX < LIMIT + 1) DO
SET ARRAY_REP[INDEX] = 0;
SET INDEX = INDEX + 1;
I read in other thread that suppoerted SQL statements in UDF are
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
* SET variable statement
Then What error I am doing in my code?
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)
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.