Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    107

    Unanswered: How to access the rows in the cursor?

    hi friends,

    My trigger is as follows:
    -----------------------------------------------------------------------------------------
    CREATE TRIGGER AFTER_SALARY
    AFTER UPDATE OF SALARY ON EMPLOYEE
    REFERENCING NEW_TABLE AS NEWSALARY OLD_TABLE AS OLDSALARY
    FOR EACH STATEMENT MODE DB2SQL
    BEGIN ATOMIC
    DECLARE NSAL DECIMAL(9,2) DEFAULT 0.0;
    DECLARE OSAL DECIMAL(9,2) DEFAULT 0.0;

    FOR SALARY_CUR AS
    Select N.SALARY NS, O.SALARY OS
    from NEWSALARY N INNER JOIN OLDSALARY O ON (N.EMPNO=O.EMPNO)
    DO
    SET SALARY_CUR.NS = SALARY_CUR.OS;
    END FOR;
    END
    @
    -----------------------------------------------------------------------------------------
    The error produced is:
    -----------------------------------------------------------------------------------------
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0206N "NS" is not valid in the context where it is used. LINE NUMBER=13.
    SQLSTATE=42703
    -----------------------------------------------------------------------------------------

    I want to access the column values inside the for loop. How to do this?

    Jake

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: How to access the rows in the cursor?

    The problem maybe that you are trying to set the retrieved cursor column with the other.

    Shouldn't it be SET NSAL = SALARY_CUR.NS;

    Originally posted by Jake K
    hi friends,

    My trigger is as follows:
    -----------------------------------------------------------------------------------------
    CREATE TRIGGER AFTER_SALARY
    AFTER UPDATE OF SALARY ON EMPLOYEE
    REFERENCING NEW_TABLE AS NEWSALARY OLD_TABLE AS OLDSALARY
    FOR EACH STATEMENT MODE DB2SQL
    BEGIN ATOMIC
    DECLARE NSAL DECIMAL(9,2) DEFAULT 0.0;
    DECLARE OSAL DECIMAL(9,2) DEFAULT 0.0;

    FOR SALARY_CUR AS
    Select N.SALARY NS, O.SALARY OS
    from NEWSALARY N INNER JOIN OLDSALARY O ON (N.EMPNO=O.EMPNO)
    DO
    SET SALARY_CUR.NS = SALARY_CUR.OS;
    END FOR;
    END
    @
    -----------------------------------------------------------------------------------------
    The error produced is:
    -----------------------------------------------------------------------------------------
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0206N "NS" is not valid in the context where it is used. LINE NUMBER=13.
    SQLSTATE=42703
    -----------------------------------------------------------------------------------------

    I want to access the column values inside the for loop. How to do this?

    Jake

Posting Permissions

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