5 pts.
 left outer join in trigger DB2 Z/OS
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.

Software/Hardware used:
ASKED: February 7, 2008  3:34 PM
UPDATED: July 21, 2008  3:38 PM

Answer Wiki:
Your problem is not the left outer join. Your syntax is wrong. Try the following: <pre> 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 ) 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) ; </pre> This should do the job.
Last Wiki Answer Submitted:  July 21, 2008  3:38 pm  by  RodneyKrick   265 pts.
All Answer Wiki Contributors:  RodneyKrick   265 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _