Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    78

    Question Unanswered: MODIFYS SQL DATA in create function problem, Help!

    Hello,

    This is the function I created:

    CREATE FUNCTION ReqTranNum()
    returns bigint
    language sql
    modifies sql data

    ------------------------------------------------------------------------
    -- SQL UDF (Scalar)
    ------------------------------------------------------------------------
    F1: BEGIN ATOMIC
    declare iTran bigint;

    set iTran = (select rTranNum from requestTrn);
    set iTran = iTran + 1;
    update requestTrn set rTranNum = iTran;

    return iTran;
    END

    The following error when build the function:
    Create user-defined function returns -628.
    ReqTranNum: 20: [IBM][CLI Driver][DB2/NT] SQL0628N Multiple or conflicting keywords involving the "LANGUAGE SQL" clause are present. LINE NUMBER=20. SQLSTATE=42613

    Thanks in advance for any suggestions.

  2. #2
    Join Date
    Jul 2003
    Location
    China
    Posts
    9

    Re: MODIFYS SQL DATA in create function problem, Help!

    "set iTran = (select rTranNum from requestTrn);" ??

    what's the value of iTran if result of "select rTranNum from requestTrn"
    includes more than 1 row??

    Originally posted by christine2003
    Hello,

    This is the function I created:

    CREATE FUNCTION ReqTranNum()
    returns bigint
    language sql
    modifies sql data

    ------------------------------------------------------------------------
    -- SQL UDF (Scalar)
    ------------------------------------------------------------------------
    F1: BEGIN ATOMIC
    declare iTran bigint;

    set iTran = (select rTranNum from requestTrn);
    set iTran = iTran + 1;
    update requestTrn set rTranNum = iTran;

    return iTran;
    END

    The following error when build the function:
    Create user-defined function returns -628.
    ReqTranNum: 20: [IBM][CLI Driver][DB2/NT] SQL0628N Multiple or conflicting keywords involving the "LANGUAGE SQL" clause are present. LINE NUMBER=20. SQLSTATE=42613

    Thanks in advance for any suggestions.

  3. #3
    Join Date
    Jul 2003
    Posts
    78

    Re: MODIFYS SQL DATA in create function problem, Help!

    Thanks for your reply. Actually the table does have only one row.


    Originally posted by rijkaard
    "set iTran = (select rTranNum from requestTrn);" ??

    what's the value of iTran if result of "select rTranNum from requestTrn"
    includes more than 1 row??

Posting Permissions

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