hi,

While creating following UDF I am getting an error (I have removed the
some statements).

CREATE FUNCTION CONVERTROW(VAR1 VARCHAR(50))
RETURNS VARCHAR(205)
DETERMINISTIC NO EXTERNAL ACTION
CONTAINS SQL
LANGUAGE SQL
BEGIN ATOMIC

DECLARE C1 CURSOR FOR
SELECT COL FROM SURI;

RETURN RESULT;
END
@


error messages is :
SQL0104N An unexpected token "FOR" was found following "C DECLARE C1
CURSOR". Expected tokens may include: "<SQL_variable_declarations>".
LINE
NUMBER=8. SQLSTATE=42601

thanks in advance,
Suresh D

Suresh D wrote:
> hi,
>
> While creating following UDF I am getting an error (I have removed the
> some statements).
>
> CREATE FUNCTION CONVERTROW(VAR1 VARCHAR(50))
> RETURNS VARCHAR(205)
> DETERMINISTIC NO EXTERNAL ACTION
> CONTAINS SQL
> LANGUAGE SQL
> BEGIN ATOMIC
>
> DECLARE C1 CURSOR FOR
> SELECT COL FROM SURI;
>
> RETURN RESULT;
> END
> @
>
>
> error messages is :
> SQL0104N An unexpected token "FOR" was found following "C DECLARE C1
> CURSOR". Expected tokens may include: "<SQL_variable_declarations>".
> LINE
> NUMBER=8. SQLSTATE=42601
>
> thanks in advance,
> Suresh D
inline SQL PL does not support cursors (see the compound statement
(dynamic) definition in teh SQL Ref.
Instead use a FOR loop (which I find much nicer, btw).

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Looks like the cursor declaration in functions is not supported(
correct me if I am wrong). Instead u can update your function by the
following statements or u can use procedure to perform similar task.

CREATE FUNCTION CONVERTROW(VAR1 VARCHAR(50))
RETURNS VARCHAR(205)
DETERMINISTIC NO EXTERNAL ACTION
CONTAINS SQL
LANGUAGE SQL
BEGIN ATOMIC
FOR row as SELECT COL FROM SURI;
DO
END FOR;
RETURN RESULT;
END @