left outer join in trigger DB2 Z/OS

5 pts.
Tags:
DB2
LEFT OUTER JOIN statement
Triggers
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following