I am trying to use the the following statement in an oraoci 8 stage:
UPDATE VOUCHER_PAYMENT SET EDW_PAYMENT_ID=:2,SOURCE_SYSTEM_ID=:3,EDW_PAYMENT_APPLIED_DATE_ID=:4,PAYMENT_APPLIED_DATE=TO_DATE(:5, 'YYYY-MM-DD HH24:MI:SS'),CUSTOMER_NODE_ID=:6,EDW_PIVOT_SUBSCRIBER_DETAIL_ID=:7,AMOUNT=:8,CURRENCY_SYMBOL=:9,PAYMENT_MADE_LOCATION_CODE=:10,PAYMENT_RECEIVED_DATE=TO_DATE(:11, 'YYYY-MM-DD HH24:MI:SS'),PAYMENT_STATUS_CODE=:12,PAYMENT_TYPE_ID=:13,RECEIPT_NR=:14, SOURCE_UPDATE_DATE=TO_DATE(:15, 'YYYY-MM-DD HH24:MI:SS'),EDW_LOOKUP_DATE=TO_DATE(:15, 'YYYY-MM-DD HH24:MI:SS'), EDW_UPDATE_PROCESS_ID=:16,EDW_DELTA_LOAD_ID=:17 WHERE PAYMENT_ID=:1;
INSERT INTO VOUCHER_PAYMENT (PAYMENT_ID,EDW_PAYMENT_ID,SOURCE_SYSTEM_ID,EDW_PAYMENT_APPLIED_DATE_ID,PAYMENT_APPLIED_DATE,CUSTOMER_NODE_ID,EDW_PIVOT_SUBSCRIBER_DETAIL_ID,AMOUNT,CURRENCY_SYMBOL,PAYMENT_MADE_LOCATION_CODE,PAYMENT_RECEIVED_DATE,PAYMENT_STATUS_CODE,PAYMENT_TYPE_ID,RECEIPT_NR,SOURCE_CREATE_DATE,EDW_CREATE_PROCESS_ID, EDW_DELTA_LOAD_ID) VALUES (:1,:2,:3,:4,TO_DATE(:5, 'YYYY-MM-DD HH24:MI:SS'),:6,:7,:8,:9,:10,TO_DATE(:11, 'YYYY-MM-DD HH24:MI:SS'),:12,:13,:14,TO_DATE(:15, 'YYYY-MM-DD HH24:MI:SS'),:16,:17)
I have to pass the same value at position 15 received from transformer into SOURCE_UPDATE_DATE,EDW_LOOKUP_DATE. (see the update clause above).
However the job is failing with following error:
Abnormal termination of stage EDWLoadEDWPrePayVoucherPayment1..tSequence detected.
When I keep set clause for only one of these variables i.e :15 is used only once in update statement, then it works fine.
Can somebody guide me how to deal with this situation.
Thanks
Mukund Shastri
Problem using update insert in the oraoci 8 stage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 103
- Joined: Tue Oct 14, 2003 4:07 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Reset (not re-compile) the job after it aborts, and post the "from previous run..." message. This usually provides additional diagnostic information that helps to solve the problem.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.