Cursor in UDF

[Thomas]

I am on: Database server = DB2/AIX64 8.1.5
It is also a partitioned database.

I am trying to create an UDF that uses cursors. The following is a
simplified version of my UDF and error given.

create function testFunction(inString varchar(4000))
returns varchar(4000)
deterministic
no external action
reads sql data

F1: begin atomic

declare newString, orgCName varchar(4000);
declare len int;

declare workString varchar(4000);

declare orgCursor cursor for
select organisation_translation
from thomas.organisation_translation
where code= workString for read only;

if inString is null
then
return null;
end if;

set workString = rtrim(ltrim(inString));

-- Check whether the name exists in the database.
open orgCursor;
fetch orgCursor into orgCName;
close orgCursor;

return orgCName;

end F1@

[IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "for" was
found following "are orgCursor cursor". Expected tokens may include:
"<SQL_variable_condition_declaration>". LINE NUMBER=1.
SQLSTATE=42601

Where am I going wrong on this. I have read every post pertaining to
UDFs and have not found the answer.

Thanks in advance,
Thomas

[Serge Rielau]

Who needs cursors? (if needed you can use FOR)

create function testFunction(inString varchar(4000))
returns varchar(4000)
deterministic
no external action
reads sql data

RETURN (select organisation_translation
from thomas.organisation_translation
where code= workString for read only)

Cheres
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Serge Rielau wrote:

Who needs cursors? (if needed you can use FOR)

create function testFunction(inString varchar(4000))
returns varchar(4000)
deterministic
no external action
reads sql data

RETURN (select organisation_translation
from thomas.organisation_translation
where code= workString for read only)

Cheres
Serge

Without the FOR RED ONLY of course

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab