Toad World® Forums

Table function that modifies data

Hi,

I have a function that modifies data when i use the same function as a scalar function its gets created but when I am trying to create it as a table function its giving me the following error :

DB2 Database Error: ERROR [42613] [IBM][DB2/LINUXX8664] SQL0628N Multiple or conflicting keywords involving the “RETURNS” clause are present. LINE NUMBER=4. SQLSTATE=42613

Function :

CREATE

OR REPLACE FUNCTION ACHINST1.AGING (

X DATE,

Y INTEGER )

returns table(vivek DATE)

LANGUAGE SQL

MODIFIES SQL DATA

NO EXTERNAL ACTION

DETERMINISTIC

begin

declare

counter_insert int;

declare

counter_tat int;

declare

store int;

declare

i int;

declare

creation_day int;

declare

creation_month int;

declare

creation_year int;

declare

expairy_day int;

declare

tat int;

declare

actual_month int;

declare

tat_date varchar(20);

declare

month_days int;

declare

expairy_id int;

declare

return_value varchar(20);

declare

holiday_counter int;

declare

actual_return date;

declare

c1 cursor for

select

length(B.days|| B1.days) from ACHINST1.DMSBUSINESSCALENDAR as B inner join ACHINST1.DMSBUSINESSCALENDAR as B1

ON

B1.BC_MONTH = (CASE WHEN B.BC_MONTH < 11 THEN B.BC_MONTH+1 ELSE 0 END)

AND B1.BC_YEAR = (CASE WHEN B.BC_MONTH = 11 THEN B.BC_YEAR+1 ELSE B.BC_YEAR END)

where B.STATUS = ‘ACTIVE’ and B.calendarkey = ‘INR’ and B1.CALENDARKEY = ‘INR’ and B1.status = ‘ACTIVE’

and b.bc_month = actual_month and b.bc_year = creation_year;

set

counter_insert = 1;

set

creation_day = day(x)

I would recommend investigating all the conditions that could trigger the error. From a DB2 CLP you can type ? SQL0628N - to get a complete explanation list.

Also perhaps try changing your BEGIN block to BEGIN ATOMIC.

Reference:

www-01.ibm.com/…/c0053764.html;lang=en

and see the statement:

An SQL PL function with an atomic body is an inlined function. An SQL function is atomic if it contains a single RETURN statement, or consists of a compound statement that begins with the keywords BEGIN ATOMIC.