|
View previous topic :: View next topic
|
| Author |
Message |
krisprems
Active Member
Joined: 27 Nov 2006
Posts: 649
Location: India
|
|
|
|
Hi, I am attempting to setup a new trigger and been hitting some issues. Any help/suggestion on resolving the same would be greatly appreciated.
| Code: |
CREATE TRIGGER CTRC_FNCL_TACN_EXT_INS
AFTER INSERT ON SYSXDB2.CTRC_FNCL_TACN_EXT
REFERENCING NEW AS NEWREC
FOR EACH ROW MODE DB2SQL
WHEN(NEWREC.TLC_PROD_CD = 'MNT'
AND EXISTS(SELECT PMT_TYP_CD, CLM_PD_AMT
FROM SYSXDB2.TLC_PMT_T
WHERE CTRCT_NBR = NEWREC.CTRCT_NBR
AND (PMT_TYP_CD = 'C'
OR PMT_TYP_CD = 'P'
OR PMT_TYP_CD = 'S')
AND TLC_PROD_CD = NEWREC.TLC_PROD_CD))
UPDATE SYSXDB2.CTRC_FNCL_TACN_EXT
SET NEWREC.TOT_CLMS_PD_AMT=SUM(CLM_PD_AMT)
WHERE NEWREC.CTRCT_NBR = CTRCT_NBR
AND NEWREC.TLC_PROD_CD = TLC_PROD_CD;
---------+---------+---------+---------+---------+---------+---------+--------
DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED
SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER : 2 SQLCODE
-206, SQLSTATE 42703, AND MESSAGE TOKENS NEWREC.TOT_CLMS_PD_AMT
DSNT418I SQLSTATE = 56059 SQLSTATE RETURN CODE |
|
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 2589
Location: NYC,USA
|
|
|
|
| There is no such column TOT_CLMS_PD_AMT. It should be NEWREC.CLMS_PD_AMT |
|
| Back to top |
|
|
krisprems
Active Member
Joined: 27 Nov 2006
Posts: 649
Location: India
|
|
|
|
Hi Rohit, thanks for the response. I did first suspect that the column as the issue. However, the column is available in the table. below is the screen shot:
| Code: |
DB2 Admin -- TDB2 Columns in Table SYSXDB2.CTRC_FNC > ---- Row 82 to 91 of 91
Command ===> Scroll ===> CSR
Line commands:
T - Tables X - Indexes A - Auth GR - Grant H - Homonyms I - Interpret
UR - Update runstats LAB - Label COM - Comment DI - Distribution stats
? - Show all line commands
Select Column Name Col No Col Type Length Scale Null Def FP Col Card
* * * * * * * * *
------ ------------------ ------ -------- ------ ------ ---- --- -- -----------
VEH_STTG_MLG_NB 82 DECIMAL 6 0 Y Y N -1
CNCN_MLG_NB 83 DECIMAL 6 0 Y Y N -1
RNSM_ADJM_TYPE_CD 84 CHAR 1 0 Y Y N -1
SLS_INVL_CD 85 CHAR 1 0 Y Y N -1
DLR_TYPE_CD 86 CHAR 1 0 Y Y N -1
ZCBK_FLG 87 CHAR 1 0 N Y N -1
JMA_INCV 88 DECIMAL 4 0 N Y N -1
NEW_PRC_IN 89 CHAR 1 0 N Y N -1
LOCN_CD 90 CHAR 4 0 Y Y N -1
TOT_CLMS_PD_AMT 91 DECIMAL 9 2 N Y N -1
******************************* END OF DB2 DATA ******************************* |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
I don't think you can do SET NEWREC.TOT_CLMS_PD_ANT because the table you update has no column NEWREC.
| Code: |
UPDATE MyTable
SET some_other_table.MyColumn = MyColumn |
|
|
| Back to top |
|
|
krisprems
Active Member
Joined: 27 Nov 2006
Posts: 649
Location: India
|
|
|
|
Guyc, thanks for input. Based on your last input, I modified it to
| Code: |
| CTRC_FNCL_TACN_EXT.TOT_CLMS_PD_AMT |
and it worked. Thanks All! |
|
| Back to top |
|
|
|
|
