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)