Error using old table() in UDF

[Arun Srinivasan]

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.

[Serge Rielau]

Re: Error using old table() in UDF

Arun Srinivasan wrote:

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