ERROR 3119: DML query with a predicate that could not be pushed below

After migration from 5.0 to 7.1, the BODS job failed with below message: SQLSTATE 0A000 Description 

ERRCODE_FEATURE_NOT_SUPPORTED

 

resulted in error <ERROR 3119:  DML query with a predicate that could not

                                                       be pushed below joins and does not refer solely to the target table is not supported

                                                       HINT:  Try putting the joins from the UPDATE's from clause into a subquery

                                                       >. The SQL submitted is <UPDATE DIMENSION.DIM_PP_OFFER

                                                       SET RECORD_END_DATE = '5-JUN-2015',

                                                       CURRENT_RECORD_FLAG = FALSE

                                                       FROM STAGING.STG_CM9_VOUCHER_OFFER_MAP A

                                                       LEFT JOIN (SELECT DISTINCT PARAMETER_NAME VOUCHER_TYPE, PARAMETER_VALUE CHARGE_CODE

                                                       FROM STAGING.STG_AR1_PROPERTIES WHERE TYPE = 'L9_VOUCHER_TYPE') B ON A.VOUCHER_TYPE = B.VOUCHER_TYPE

                                                       LEFT JOIN STAGING.STG_AR1_CHARGE_CODE C ON B.CHARGE_CODE = C.CHARGE_CODE

                                                       WHERE A.VOUCHER_TYPE = DIM_PP_OFFER.VOUCHER_TYPE

                                                       AND A.PACKAGE_ID = DIM_PP_OFFER.PACKAGE_ID

                                                       AND (UPPER(A.PACKAGE_DESC) <> DIM_PP_OFFER.PACKAGE_DESC

                                                       OR UPPER(TRIM(B.CHARGE_CODE)) <> DIM_PP_OFFER.CHARGE_CODE

                                                       OR UPPER(C.CHARGE_CODE_DESC) <> DIM_PP_OFFER.CHARGE_DESC

                                                       OR NVL(A.DURATION,0) <> NVL(DIM_PP_OFFER.DURATION,0)

                                                       OR NVL(A.CUST_ELIGIBLE,'') <> NVL(DIM_PP_OFFER.CUST_ELIGIBLE,'')

                                                       OR NVL(A.PRICE,0) <> NVL(DIM_PP_OFFER.PRICE,0))>

 

 

This same job was running perfactly fine with older version 5.0

Comments