Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Question Unanswered: Simple function with Update and Insert

    Hi all

    I need a DB2 function to Update a table and Insert some records to another table

    Then return 1

    No IF statements..

    This will help me to further develop this function in future..

    I have done this..

    CREATE FUNCTION "DB2ADMIN"."DTV_SCHEDULE_PUBLISH1" ( "PERIOD" INTEGER )
    RETURNS INTEGER
    LANGUAGE SQL
    MODIFIES SQL DATA
    NO EXTERNAL ACTION
    DETERMINISTIC

    BEGIN ATOMIC
    UPDATE DB2ADMIN.PROGRAMME_SCHEDULE_HDR_TEST
    SET PUBLISHED_STATUS = 1 , INIT_PUBLISHED_DATE = CURRENT_DATE ,LAST_UPDATE_DATE = CURRENT_DATE
    WHERE PERIOD = TO_CHAR (SCHEDULE_DATE, 'YYYYMM');

    INSERT INTO DTV_SCHEDULE_PUBLISH_TEST (CHANNEL_ID,SCHEDULE_PERIOD,INIT_PUB_DATE,LAST_PUB _DATE,ACTIVE_STATUS,USER_ENTERED,DATE_ENTERED)
    SELECT P.CHANNEL_ID,PERIOD,CURRENT_DATE,CURRENT_DATE,'1', CURRENT_USER,CURRENT_DATE FROM PROGRAMME_SCHEDULE_HDR_TEST P WHERE PERIOD = TO_CHAR (SCHEDULE_DATE, 'YYYYMM');

    RETURN 1;

    END;

    But getting a error of..

    DB2 Database Error: ERROR [42613] [IBM][DB2/NT] SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL DATA" clause are present. LINE NUMBER=18. SQLSTATE=42613

    I was taken the help of this link [Example 4]

    DB2 Database for Linux, UNIX, and Windows

    Plz help me..

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The manual states that MODIFIES SQL DATA can only be used for a function that returns a TABLE.

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ai_zaviour View Post
    I was taken the help of this link [Example 4]

    DB2 Database for Linux, UNIX, and Windows
    Make sure you reading the correct InfoCenter webpage for the release of DB2 you have. The link you provided is for V9 (which is 9.1). There are separate InfoCenters for V9R5 and V9R7.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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