left outer join in trigger DB2 Z/OS
DB2, z/OS, Trigger, Left Outer Join
I'm having problems writing a trigger that contains a left outer join. Here is the trigger: CREATE TRIGGER H000.H0TRCAD2
AFTER DELETE ON H000.H0COO_TRANS_CALLC REFERENCING OLD AS OLD_ROW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
SELECT OLD_ROW.CLNT_ID
, OLD_ROW.ACCT_NBR
, OLD_ROW.PST_DT
, OLD_ROW.ITM_SEQ_NBR
, OLD_ROW.CREAT_TS
, OLD_ROW.ALLC_SEQ_NBR
, OLD_ROW.ACCTG_CDE
, OLD_ROW.ALLC_AMT
, OLD_ROW.CREAT_USR_ID
, OLD_ROW.UPDT_USR_ID
, OLD_ROW.UPDT_TS
, OLD_ROW.ALLC_ORGNR
, OLD_ROW.ALLC_TYP_CDE
, TA.SEL_AAC_NM
, TA.SEL_AAC_TYP
FROM H000.H0COO_TRANS_CALLC OLD_ROW
LEFT OUTER JOIN H000.H0COR_TRANS_AAC TA
ON (OLD_ROW.CLNT_ID = TA.CLNT_ID
AND OLD_ROW.ACCT_NBR = TA.ACCT_NBR
AND OLD_ROW.PST_DT = TA.PST_DT
AND OLD_ROW.ITM_SEQ_NBR = TA.ITM_SEQ_NBR)
INSERT
INTO H000.H0COO_TRAN_ALC_HIS
( CLNT_ID
, ACCT_NBR
, PST_DT
, ITM_SEQ_NBR
, ALLC_SEQ_NBR
, CREAT_TS
, ACCTG_CDE
, ALLC_AMT
, CREAT_USR_ID
, UPDT_USR_ID
, UPDT_TS
, ALLC_ORGNR
, ALLC_TYP_CDE
, SEL_AAC_NM
, SEL_AAC_TYP
)
VALUES
( OLD_ROW.CLNT_ID
, OLD_ROW.ACCT_NBR
, OLD_ROW.PST_DT
, OLD_ROW.ITM_SEQ_NBR
, OLD_ROW.CREAT_TS
, OLD_ROW.ALLC_SEQ_NBR
, OLD_ROW.ACCTG_CDE
, OLD_ROW.ALLC_AMT
, OLD_ROW.CREAT_USR_ID
, OLD_ROW.UPDT_USR_ID
, OLD_ROW.UPDT_TS
, OLD_ROW.ALLC_ORGNR
, OLD_ROW.ALLC_TYP_CDE
, TA.SEL_AAC_NM
, TA.SEL_AAC_TYP
) ;
END
Here is the error message when I try to do the create:
DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER : 2 SQLCODE -199, SQLSTATE 42601, AND MESSAGE TOKENS INSERT,FOR WITH FETCH ORDER UNION EXCEPT QUERYNO OPTIMIZ.
Any help would be appreciated.
0
