Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2009
    Posts
    5

    Unanswered: DB2 trigger error

    I am getting following error while executing the trigger, pls let me know if anyone knows how to resolve the error.


    SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED SQL STATEMENT. INFORMATION RETURNED: SECTION
    NUMBER : 2 SQLCODE -104, SQLSTATE 42601, AND MESSAGE TOKENS -,TABLE STATEMENT , . SCROLL INSENSITIVE SENSITIVE
    ASENSITIVE NO

    Here is the trigger,

    --#SET TERMINATOR ~

    CREATE TRIGGER CWSRCAL0
    AFTER INSERT ON CLAIM_TRANSACTION
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL
    WHEN (N.CTX_TRS_TYPE_CD < 90)
    BEGIN ATOMIC
    DECLARE WS-COUNT INTEGER;
    SET WS-COUNT =
    (SELECT (*)
    FROM CLAIM_SUPPORT_1_V1 CS01_COV_DESC
    WHERE CS01_COV_DESC.CS01_TABLE_ID = 'C280'
    AND CS01_COV_DESC.CS01_CODE =
    (SELECT COVDESC.CCP_COV_MOD_CD
    FROM CLM_COV_PKG_V1 COVDESC
    WHERE COVDESC.CCP_CLAIM_NBR = N.CTX_CLAIM_NBR
    AND COVDESC.CCP_PKG_TYPE_CD = N.CTX_COV_TYPE_CD));
    IF WS-COUNT = 0 THEN
    INSERT INTO CLM_ACTIVITY_LOG_V
    (CAL_CLAIM_NBR
    ,CAL_ACY_TYPE_CD
    ,CAL_SORT_TS
    ,CAL_OBJECT_ID
    ,CAL_ORT_CLT_ID
    ,CAL_ACTIVITY_DES
    ,CAL_ACTIVITY_AMT
    ,CAL_CLN_CLT_ID)
    SELECT
    N.CTX_CLAIM_NBR
    ,'CTX'
    ,N.CTX_SORT_TS
    ,CLAIM.CLM_CSR_CLAIM_NBR
    ,ORIGINATOR.SEC_USR_CLT_ID
    ,RTRIM(CHAR(( 'Added ' ||
    RTRIM(CS01_FIN_TYPE.CS01_CODE_DES )||' '||
    RTRIM(CS01_TRANS_TYPE.CS01_CODE_DES ) ||' '||
    RTRIM(CS01_COVERAGE_TYPE.CS01_CODE_DES ) ), 100))
    ,N.CTX_TRS_AMT
    ,N.CTX_CLIENT_ID
    FROM
    CLAIM_TAB_V1 CLAIM
    ,SEC_USRS_V ORIGINATOR
    ,CLAIM_SUPPORT_1_V1 CS01_COVERAGE_TYPE
    ,CLAIM_SUPPORT_1_V1 CS01_FIN_TYPE
    ,CLAIM_SUPPORT_1_V1 CS01_TRANS_TYPE
    WHERE
    (N.CTX_CLAIM_NBR = CLAIM.CLM_CLAIM_NBR)
    AND (N.CTX_COV_TYPE_CD = CS01_COVERAGE_TYPE.CS01_CODE
    AND CS01_COVERAGE_TYPE.CS01_TABLE_ID IN ('C039','C169'))
    AND (N.CTX_FIN_TYPE_CD = CS01_FIN_TYPE.CS01_CODE
    AND CS01_FIN_TYPE.CS01_TABLE_ID='C046')
    AND (N.CTX_TRS_TYPE_CD = INTEGER(CS01_TRANS_TYPE.CS01_CODE)
    AND CS01_TRANS_TYPE.CS01_TABLE_ID='C044')
    AND (N.CTX_ENTRY_OPR_ID = ORIGINATOR.SEC_USR_ID)
    ELSE
    INSERT INTO CLM_ACTIVITY_LOG_V
    (CAL_CLAIM_NBR
    ,CAL_ACY_TYPE_CD
    ,CAL_SORT_TS
    ,CAL_OBJECT_ID
    ,CAL_ORT_CLT_ID
    ,CAL_ACTIVITY_DES
    ,CAL_ACTIVITY_AMT
    ,CAL_CLN_CLT_ID)
    SELECT
    N.CTX_CLAIM_NBR
    ,'CTX'
    ,N.CTX_SORT_TS
    ,CLAIM.CLM_CSR_CLAIM_NBR
    ,ORIGINATOR.SEC_USR_CLT_ID
    ,RTRIM(CHAR(( 'Added ' ||
    RTRIM(CS01_FIN_TYPE.CS01_CODE_DES )||' '||
    RTRIM(CS01_TRANS_TYPE.CS01_CODE_DES ) ||' '||
    RTRIM(CS01_COVERAGE_TYPE.CS01_CODE_DES ) ||' '||
    RTRIM(CS01_COV_DESC.CS01_CODE_DES ) ), 100))
    ,N.CTX_TRS_AMT
    ,N.CTX_CLIENT_ID
    FROM
    CLAIM_TAB_V1 CLAIM
    ,SEC_USRS_V ORIGINATOR
    ,CLAIM_SUPPORT_1_V1 CS01_COVERAGE_TYPE
    ,CLAIM_SUPPORT_1_V1 CS01_FIN_TYPE
    ,CLAIM_SUPPORT_1_V1 CS01_TRANS_TYPE
    ,CLAIM_SUPPORT_1_V1 CS01_COV_DESC
    WHERE
    (N.CTX_CLAIM_NBR = CLAIM.CLM_CLAIM_NBR)
    AND (N.CTX_COV_TYPE_CD = CS01_COVERAGE_TYPE.CS01_CODE
    AND CS01_COVERAGE_TYPE.CS01_TABLE_ID IN ('C039','C169'))
    AND (N.CTX_FIN_TYPE_CD = CS01_FIN_TYPE.CS01_CODE
    AND CS01_FIN_TYPE.CS01_TABLE_ID='C046')
    AND (N.CTX_TRS_TYPE_CD = INTEGER(CS01_TRANS_TYPE.CS01_CODE)
    AND CS01_TRANS_TYPE.CS01_TABLE_ID='C044')
    AND (N.CTX_ENTRY_OPR_ID = ORIGINATOR.SEC_USR_ID)
    AND (CS01_COV_DESC.CS01_TABLE_ID ='C280'
    AND CS01_COV_DESC.CS01_CODE =
    (SELECT COVDESC.CCP_COV_MOD_CD
    FROM CLM_COV_PKG_V1 COVDESC
    WHERE COVDESC.CCP_CLAIM_NBR = CLAIM.CLM_CLAIM_NBR
    AND COVDESC.CCP_PKG_TYPE_CD = N.CTX_COV_TYPE_CD))
    END-IF;
    END

    ~

    --#SET TERMINATOR ;

    COMMIT ;
    Last edited by amijai_11; 12-22-09 at 07:25.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    May be COUNT was lost...
    SET WS-COUNT =
    (SELECT COUNT(*)

  3. #3
    Join Date
    Dec 2009
    Posts
    5
    Thanks for the suggestion. But still getting the same error.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    And (n.ctx_entry_opr_id = originator.sec_usr_id);
    else

    and covdesc.ccp_pkg_type_cd = n.ctx_cov_type_cd));
    end-if;

  5. #5
    Join Date
    Dec 2009
    Posts
    5
    Still the same error. Is it the way the variable is declared?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Ah! WS-COUNT ==> WS_COUNT

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I realized
    ... MESSAGE TOKENS -, ....

  8. #8
    Join Date
    Dec 2009
    Posts
    5
    Should it be like given below?

    DECLARE WS_COUNT INTEGER;
    SET WS_COUNT =

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please study (DB2) SQL basics.

    First of all, read the manual "DB2 SQL Reference".

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    And build such a trigger step-by-step, i.e. start with an empty body, then add 1 simple statement, then expand. After each step, test the trigger - until the full functionality is in place.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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