Error using old table() in UDF

I wrote an udf which can be outlined as
create <>
modifies sql data
returns select * from old table (update <set <>);
end;

The error I get is
SQL0278N [IBM][CLI Driver][DB2/NT64] SQL0628N Multiple or conflicting
keywords involving the "MODIFIES SQL DATA" clause are present. LINE
NUMBER=15. SQLSTATE=42613

If I don't specify, it says it needs it. Is there a work around, I am
supposing db2 considers select statement as the only operation and it
wont need the modifies sql data. Please help.

Arun Srinivasan wrote:

Quote:

I wrote an udf which can be outlined as
create <>
modifies sql data
returns select * from old table (update <set <>);
end;
>
The error I get is
SQL0278N [IBM][CLI Driver][DB2/NT64] SQL0628N Multiple or conflicting
keywords involving the "MODIFIES SQL DATA" clause are present. LINE
NUMBER=15. SQLSTATE=42613
>
If I don't specify, it says it needs it. Is there a work around, I am
supposing db2 considers select statement as the only operation and it
wont need the modifies sql data. Please help.

You likely created a SCALAR UDF. Scalar UDF cannot modify SQL data.
Imagine such a function in a WHERE clause. *shudder*

Make is a TABLE function and you;re all set as long as you are careful
where to use it.

Btw you need: RETURNS TABLE(....)
and RETURN (no "s") (SELECT ....)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Error using old table() in UDF

re: Error using old table() in UDF

Similar DB2 Database bytes

/bytes/development

/bytes/it

/bytes/about