在ORACLE 11.2.0.3上执行merge into 里面sql 入下形式:
merge into DCDW.t1 Target
using (select Org_code Org_code,
BUSS_DATE,
BUSS_NO,
...
from (SELECT E.BUSS_NO BUSS_NO,
E.Org_code Org_code,
..
FROM DCDW.T E
WHERE
E.MODIFIED_DATE >= IN_BEGIN_DATE
AND E.MODIFIED_DATE <= IN_END_DATE)
group by BUSS_NO,Org_code,BUSS_DATE) Source
ON (Target.ORG_CODE = Source.Org_code and target.BUSS_TYPE = '1' AND target.BUSS_DATE = source.BUSS_DATE AND target.BUSS_NO = source.BUSS_NO)
WHEN NOT MATCHED THEN
INSERT
..
WHEN MATCHED THEN
UPDATE
SET ..;
咋都会错误,在ORACLE 11.2.0.1上执行正常,修改以下sql,在各个版本执行都正常,唯一区别是max(org_code)
erge into DCDW.t1 Target
using (select max(Org_code) Org_code,
BUSS_DATE,
BUSS_NO,
...
from (SELECT E.BUSS_NO BUSS_NO,
E.Org_code Org_code,
..
FROM DCDW.T E
WHERE
E.MODIFIED_DATE >= IN_BEGIN_DATE
AND E.MODIFIED_DATE <= IN_END_DATE)
group by BUSS_NO,BUSS_DATE) Source
ON (Target.ORG_CODE = Source.Org_code and target.BUSS_TYPE = '1' AND target.BUSS_DATE = source.BUSS_DATE AND target.BUSS_NO = source.BUSS_NO)
WHEN NOT MATCHED THEN
INSERT
..
WHEN MATCHED THEN
UPDATE
SET ..;
奇怪得很!make下!