SEO

#1 (**permalink**) 02-26-2008, 06:56 PM

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

#2 (**permalink**) 02-26-2008, 06:56 PM

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

#3 (**permalink**) 02-26-2008, 06:56 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Cursor in UDF