文档首页/ 数据仓库服务 DWS/ 更多文档/ 开发指南（联盟区域）/ DWS性能调优/ SQL调优案例/ 案例：典型不下推语句整改案例集/ 案例：Merge Into can't be shipped in the sence temporarily
更新时间：2025-12-12 GMT+08:00
查看PDF
分享

案例：Merge Into can't be shipped in the sence temporarily

问题根因

MERGE INTO语句的USING子句部分还有非列表达式。

处理方案：改写SQL，去除非列表达式。

案例1：子查询d中的WHERE条件含有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' 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;

规避方案：这种情况一般属于SQL语句书写错误，去掉条件“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;

修改点对比