Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2014
    Posts
    6

    Unanswered: Table function that modifies data

    Hi,

    I have the following function in DB2 but while creating its giving 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 :



    SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ACHINST1" ;

    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);
    set creation_month = month(x);
    set actual_month = creation_month -1;
    set creation_year = year(x);
    set expairy_day = creation_day;
    set expairy_id=creation_day;

    set tat = y;
    set counter_tat = 1;


    if actual_month = 0
    then set month_days = 31;

    elseif actual_month = 1
    then
    if ((mod(creation_year,4) = 0) AND ((mod(creation_year,100) != 0) OR (mod(creation_year,400) = 0)))
    then set month_days = 29;
    else
    set month_days = 28;
    end if;

    elseif actual_month = 2
    then set month_days = 31;

    elseif actual_month = 3
    then set month_days = 30;

    elseif actual_month = 4
    then set month_days = 31;

    elseif actual_month = 5
    then set month_days = 30;

    elseif actual_month = 6
    then set month_days = 31;

    elseif actual_month = 7
    then set month_days = 31;

    elseif actual_month = 8
    then set month_days = 30;

    elseif actual_month = 9
    then set month_days = 31;

    elseif actual_month = 10
    then set month_days = 30;

    elseif actual_month = 11
    then set month_days = 31;
    end if;






    open c1;
    fetch c1 into store;
    close c1;



    while (counter_insert <= store) do

    insert into achinst1.calender values (counter_insert, (select substr((B.days|| B1.days),counter_insert,1) 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 = counter_insert+1;
    end while;


    while (counter_tat <= tat) do


    if exists (select value from calender where id = expairy_id and value = 1)
    then
    set counter_tat = counter_tat+1;
    end if;
    if expairy_day = month_days
    then
    if creation_month = 12
    then
    set expairy_day = 1;
    set creation_month = 1;
    set creation_year = creation_year+1;
    else
    set expairy_day = 1;
    set creation_month = creation_month + 1;
    end if;
    else
    set expairy_day = expairy_day+1;
    end if;
    set expairy_id = expairy_id+1;

    end while;

    set holiday_counter = counter_tat + 1;

    while exists (select value from calender where id = expairy_id and value = 0) do


    if expairy_day = month_days
    then
    if creation_month = 12
    then
    set expairy_day = 1;
    set creation_month = 1;
    set creation_year = creation_year+1;
    else
    set expairy_day = 1;
    set creation_month = creation_month + 1;
    end if;
    else
    set expairy_day = expairy_day+1;
    end if;
    set expairy_id = expairy_id+1;
    end while;

    set return_value = (creation_year || '-' ||creation_month || '-' || expairy_day);
    delete from achinst1.CALENDER;
    insert into datastore values (return_value);


    set actual_return = date(to_date(return_value,'DD-MM-YYYY'));


    return (select rtr from achinst1.datastore where rtr = return_value);

    end;



    Can some one help me???

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    way overcomplicated. In simple terms what do you want from the function(your input, your output and what should be done in between)?
    Dave

  3. #3
    Join Date
    Jun 2014
    Posts
    6
    Input :
    X date
    Y integer
    Returns table (vivek date)

    Fun body

    Insert into achinst1.datastore values (return_value);
    Return (select rtr from achinst1.datastore where rtr = return_value);
    End;

    Its a table function that modifies data and returns a date. I have tried the same as scalar function and returned only one scalar value "date". It got created but I dint kno hw to call a scalar function that modifies data from a select statement so am tryin to have a table function..

    If u want more info plz ask me

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    1. You have to return a table instead of value.
    Try:
    return select rtr from achinst1.datastore where rtr = return_value;
    instead of:
    return (select rtr from achinst1.datastore where rtr = return_value);

    2. If you want to have a table function which MODIFIES SQL DATA, you have to use Compound SQL (inlined) statement (begin atomic ... end).
    See Note 4 for CREATE FUNCTION (SQL scalar, table, or row) statement
    This means that you have to modify your code. For example, you are not able to use the DECLARE CURSOR statement, and you have to use FOR loop instead.
    In the same Note 4 you can see the restriction on modifies sql data scalar udf use.
    Regards,
    Mark.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    VIvek,
    I had asked what you want done. I know you receive a date and a number. What do you want done based on that date and that number and what do you want to come back?
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •