By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,160 Members | 1,983 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,160 IT Pros & Developers. It's quick & easy.

DB2 trigger error

P: 1
I am getting following error for a trigger, please let me know if anyone knows how to fix the problem. Thks in advance for the help.

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

--#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 COUNT (*)
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 ;

Dec 22 '09 #1
Share this Question
Share on Google+
1 Reply


P: 16
Hello Amijai,

you dont have properly set the terminator for the statement ...

you can run it with
db2 -td terminator

and put the terminator after each section which needs to be run before continuing ...
Jan 13 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.