Help Center/ Data Warehouse Service/ Standard Data Warehouse (9.1.0.x)/ DWS Performance Tuning/ SQL Tuning Examples/ Cases: Modifying Statements That Cannot Be Pushed Down/ Case: MERGE INTO Can't Be Shipped in the Sence Temporarily
Updated on 2026-05-29 GMT+08:00
View PDF

Case: MERGE INTO Can't Be Shipped in the Sence Temporarily

Possible Cause

The USING clause of the MERGE INTO statement contains non-list expressions.

Solution: Rewrite the SQL statement to remove non-list expressions.

Case 1: Using AND 1 = 2 in the WHERE condition of Subquery d

Original statement

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
MERGE INTO dwifin.dwi_bill_pay_interface_line l
USING (SELECT to_number(ss_id || invoice_id) as ap_invoice_id,
              to_number(ss_id || invoice_line_id) as credit_invoice_line_id,
              line_number,
              nvl(deleted_flag, 'N') AS src_sys_del_flag
         FROM sdifin.apwf_pay_interface_line_t_6600 t
        WHERE t.adjust_type = 'Y' AND 1 = 2) d
ON (l.ap_invoice_id = d.ap_invoice_id AND l.credit_invoice_line_id = d.credit_invoice_line_id AND l.line_number = d.line_number AND l.src_sys_del_flag = d.src_sys_del_flag AND l.ss_id = 6600
)
WHEN MATCHED THEN UPDATE SET l.del_flag = 'Y', last_upd_cycle_id = 20250301000000;

Solution: Remove AND 1 = 2.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
MERGE INTO dwifin.dwi_bill_pay_interface_line l
USING (SELECT to_number(ss_id || invoice_id) as ap_invoice_id,
              to_number(ss_id || invoice_line_id) as credit_invoice_line_id,
              line_number,
              nvl(deleted_flag, 'N') AS src_sys_del_flag
         FROM sdifin.apwf_pay_interface_line_t_6600 t
        WHERE t.adjust_type = 'Y' ) d
ON (l.ap_invoice_id = d.ap_invoice_id AND l.credit_invoice_line_id = d.credit_invoice_line_id AND l.line_number = d.line_number AND l.src_sys_del_flag = d.src_sys_del_flag AND l.ss_id = 6600
)
WHEN MATCHED THEN UPDATE SET l.del_flag = 'Y', last_upd_cycle_id = 20250301000000;

Modification comparison

Parent Topic: Cases: Modifying Statements That Cannot Be Pushed Down