Results 1 to 5 of 5

Thread: Trigger trouble

  1. #1
    Join Date
    Sep 2013
    Posts
    46

    Unanswered: Trigger trouble

    Originally I was trying this:

    CREATE OR REPLACE TRIGGER DEMO.JS_SKILL_DETAIL_REP_ADR_TR
    AFTER DELETE
    ON DEMO.JS_SKILL_DETAIL_REP
    REFERENCING
    OLD AS OLD
    FOR EACH ROW
    BEGIN

    IF EXISTS(SELECT MAX(INT(X.SCORE_VAL))
    FROM DEMO.JS_SKILL_DETAIL_CLN X
    WHERE PERSON_ID=OLD.PERSON_ID
    AND PERSON_REGION_ID=OLD.PERSON_REGION_ID
    AND SKILL_ID=OLD.SKILL_ID
    AND X.SCORE_VAL < INT(OLD.SCORE_VAL))
    THEN UPDATE DEMO.JS_SKILL_DETAIL_CLN SET SCORE_VAL = X.SCORE_VAL;
    ELSE
    END IF;

    END;

    Error—
    SQL0206N "X.SCORE_VAL" is not valid in the context where it is used.



    Then I made a small change but still no luck:

    CREATE OR REPLACE TRIGGER DEMO.JS_SKILL_DETAIL_REP_ADR_TR
    AFTER DELETE
    ON DEMO.JS_SKILL_DETAIL_REP
    REFERENCING
    OLD AS OLD
    FOR EACH ROW
    BEGIN

    IF EXISTS(SELECT MAX(INT(X.SCORE_VAL)) AS "MAX_SCORE_VAL"
    FROM DEMO.JS_SKILL_DETAIL_REP X
    WHERE X.PERSON_ID=OLD.PERSON_ID
    AND X.PERSON_REGION_ID=OLD.PERSON_REGION_ID
    AND X.SKILL_ID=OLD.SKILL_ID
    AND X.MAX_SCORE_VAL < INT(OLD.SCORE_VAL))
    THEN UPDATE DEMO.JS_SKILL_DETAIL_CLN SET SCORE_VAL = (SELECT MAX(INT(X.SCORE_VAL)) AS "MAX_SCORE_VAL" FROM DEMO.JS_SKILL_DETAIL_REP X WHERE X.PERSON_ID=OLD.PERSON_ID AND X.PERSON_REGION_ID=OLD.PERSON_REGION_ID AND X.SKILL_ID=OLD.SKILL_ID AND X.MAX_SCORE_VAL < INT(OLD.SCORE_VAL));
    ELSE
    END IF;

    END;

    Got same error:
    SQL0206N "X.MAX_SCORE_VAL" is not valid in the context where it is used.

    Notes:
    DB2 9.7 luw fp 8
    Data type of score_val is VARCHAR – not sure why integer was not used here.
    Essentially I would like to update the SCORE_VAL in CLN to the next highest score_val for person_id,person_region_id, and skill_id.
    If the last record of person_id,person_region_id, and skill_id was deleted then delete from CLN as well. Only update CLN score_val when the highest value from REP was deleted and also if there is more than one set of that group (person_id,person_region_id, and skill_id).

    If you have any suggestions, kindly help.

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by jigmod01 View Post
    Originally I was trying this:

    CREATE OR REPLACE TRIGGER DEMO.JS_SKILL_DETAIL_REP_ADR_TR
    AFTER DELETE
    ON DEMO.JS_SKILL_DETAIL_REP
    REFERENCING
    OLD AS OLD
    FOR EACH ROW
    BEGIN

    IF EXISTS(SELECT MAX(INT(X.SCORE_VAL))
    FROM DEMO.JS_SKILL_DETAIL_CLN X
    WHERE PERSON_ID=OLD.PERSON_ID
    AND PERSON_REGION_ID=OLD.PERSON_REGION_ID
    AND SKILL_ID=OLD.SKILL_ID
    AND X.SCORE_VAL < INT(OLD.SCORE_VAL))
    THEN UPDATE DEMO.JS_SKILL_DETAIL_CLN SET SCORE_VAL = X.SCORE_VAL;
    ELSE
    END IF;

    END;

    Error—
    SQL0206N "X.SCORE_VAL" is not valid in the context where it is used.



    Then I made a small change but still no luck:

    CREATE OR REPLACE TRIGGER DEMO.JS_SKILL_DETAIL_REP_ADR_TR
    AFTER DELETE
    ON DEMO.JS_SKILL_DETAIL_REP
    REFERENCING
    OLD AS OLD
    FOR EACH ROW
    BEGIN

    IF EXISTS(SELECT MAX(INT(X.SCORE_VAL)) AS "MAX_SCORE_VAL"
    FROM DEMO.JS_SKILL_DETAIL_REP X
    WHERE X.PERSON_ID=OLD.PERSON_ID
    AND X.PERSON_REGION_ID=OLD.PERSON_REGION_ID
    AND X.SKILL_ID=OLD.SKILL_ID
    AND X.MAX_SCORE_VAL < INT(OLD.SCORE_VAL))
    THEN UPDATE DEMO.JS_SKILL_DETAIL_CLN SET SCORE_VAL = (SELECT MAX(INT(X.SCORE_VAL)) AS "MAX_SCORE_VAL" FROM DEMO.JS_SKILL_DETAIL_REP X WHERE X.PERSON_ID=OLD.PERSON_ID AND X.PERSON_REGION_ID=OLD.PERSON_REGION_ID AND X.SKILL_ID=OLD.SKILL_ID AND X.MAX_SCORE_VAL < INT(OLD.SCORE_VAL));
    ELSE
    END IF;

    END;

    Got same error:
    SQL0206N "X.MAX_SCORE_VAL" is not valid in the context where it is used.

    Notes:
    DB2 9.7 luw fp 8
    Data type of score_val is VARCHAR – not sure why integer was not used here.
    Essentially I would like to update the SCORE_VAL in CLN to the next highest score_val for person_id,person_region_id, and skill_id.
    If the last record of person_id,person_region_id, and skill_id was deleted then delete from CLN as well. Only update CLN score_val when the highest value from REP was deleted and also if there is more than one set of that group (person_id,person_region_id, and skill_id).

    If you have any suggestions, kindly help.
    Have a look at the MERGE statement
    --
    Lennart

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    The problem is that you are trying to use an aggreagte column in WHERE clause.
    If I understood correctly, you may use something like:
    ...
    SET SCORE_VAL = (
    SELECT MAX(A.INT_SCORE_VAL)
    FROM (
    SELECT INT(X.SCORE_VAL) AS INT_SCORE_VAL
    FROM ...
    WHERE INT(X.SCORE_VAL) < INT(OLD.SCORE_VAL)
    ) A
    )
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Sep 2013
    Posts
    46
    Quote Originally Posted by aflorin27 View Post
    The problem is that you are trying to use an aggreagte column in WHERE clause.
    If I understood correctly, you may use something like:
    ...
    SET SCORE_VAL = (
    SELECT MAX(A.INT_SCORE_VAL)
    FROM (
    SELECT INT(X.SCORE_VAL) AS INT_SCORE_VAL
    FROM ...
    WHERE INT(X.SCORE_VAL) < INT(OLD.SCORE_VAL)
    ) A
    )
    Could you please show me that inside my trigger?

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    This is your code:
    SET SCORE_VAL = (SELECT MAX(INT(X.SCORE_VAL)) AS "MAX_SCORE_VAL" FROM DEMO.JS_SKILL_DETAIL_REP X WHERE X.PERSON_ID=OLD.PERSON_ID AND X.PERSON_REGION_ID=OLD.PERSON_REGION_ID AND X.SKILL_ID=OLD.SKILL_ID AND X.MAX_SCORE_VAL < INT(OLD.SCORE_VAL));
    Replace that with my code (of course, you should add join conditions to my WHERE clause)
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Tags for this Thread

Posting Permissions

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