1. Merge into 问题
程序中merge into: 报ORA-30926: 无法在源表中获得一组稳定的行
原因分析:有一数据数源同一时间传输了两条数据,造成merge into update时不确定从那一条更新从而报错。
故障解决: 使用 row_number() over() 语句,根据row_num排序确定最后一条记录,避免重复
代码参考
MERGE INTO app_outpay.T_CITIC_PAYMENT_TMP t1
USING (SELECT a.contract_num,
a.cdate,
a.edate
FROM (SELECT tor.contract_num,
tot.cdate,
tot.edate,
row_number() over(PARTITION BY tor.contract_num ORDER BY tot.id DESC) AS row_idx --ADD AT 20161026 BY richie.ruan
FROM app_mo.t_order tor
JOIN app_mo.t_order_transaction tot ON tor.id = tot.id_order
WHERE /* tor.id_state IN ('8', '11') --comment the condition at 20160920 by richie.ruan