我正试图使用merge语句来更新oracle数据库中的一组列，但在执行merge语句时，oracle不断抛出
ORA-00918：列定义不明确的
即使在使用别名之后
MERGE
/*+ parallel(A) enable_parallel_dml*/
INTO
(
SELECT
AA_PERSON_NATURAL_KEY,
SCHEDULE_LINE_ID,
AA_PROJECT_NATURAL_KEY,
AA_PERSON_ASGN_NATURAL_KEY,
EDW_UPDATE_DATE_TIME,
EDW_UPDATE_NOTE,
SRC_CREATED_DATE,
SRC_LAST_UPDATE_DATE
FROM
EDWFIN.PSP_LABOR_SCHEDULE_DAY_F
)
A USING
(
SELECT
/*+ PARALLEL(8) */
AA_PERSON_NATURAL_KEY,
SCHEDULE_LINE_ID,
AA_PROJECT_NATURAL_KEY,
AA_PERSON_ASGN_NATURAL_KEY,
NULL,
NULL,
MAX(SRC_CREATED_DATE) AS SRC_CREATED_DATE,
MAX(SRC_LAST_UPDATE_DATE) AS SRC_LAST_UPDATE_DATE
FROM
EDWFIN.PSP_LABOR_SCHEDULE_DAY_TEMP
GROUP BY
AA_PERSON_NATURAL_KEY,
SCHEDULE_LINE_ID,
AA_PROJECT_NATURAL_KEY,
AA_PERSON_ASGN_NATURAL_KEY
)
B ON
(
A.AA_PERSON_NATURAL_KEY = B.AA_PERSON_NATURAL_KEY AND
A.SCHEDULE_LINE_ID = B.SCHEDULE_LINE_ID
AND A.AA_PROJECT_NATURAL_KEY = B.AA_PROJECT_NATURAL_KEY
AND A.AA_PERSON_ASGN_NATURAL_KEY = B.AA_PERSON_ASGN_NATURAL_KEY
)
WHEN MATCHED THEN
UPDATE
SET
A.SRC_CREATED_DATE = B.SRC_CREATED_DATE,
A.SRC_LAST_UPDATE_DATE = B.SRC_LAST_UPDATE_DATE,
A.EDW_UPDATE_DATE_TIME = SYSDATE,
A.EDW_UPDATE_NOTE = ' Manually updated as part of FRS-352 '
|| sysdate
WHERE
A.SRC_CREATED_DATE <> B.SRC_CREATED_DATE
OR A.SRC_LAST_UPDATE_DATE <> B.SRC_LAST_UPDATE_DATE;
COMMIT;
ERROR at line 44:
ORA-00918: column ambiguously defined
这两个NULL值不明确:
AA_PERSON_ASGN_NATURAL_KEY,
NULL, --> this
NULL, --> this
MAX(SRC_CREATED_DATE) AS SRC_CREATED_DATE,
要么删除它们(因为您显然不需要/使用它们(，要么为它们提供别名。
在Scott的EMP表中进行说明：
无别名：
SQL> merge into emp a
2 using (select 1 empno,
3 'Littlefoot' ename,
4 10 deptno,
5 null,
6 null
7 from dual
8 ) b
9 on (a.empno = b.empno)
10 when not matched then insert (deptno, empno, ename)
11 values (b.deptno, b.empno, b.ename);
when not matched then insert (deptno, empno, ename)
*
ERROR at line 10:
ORA-00918: column ambiguously defined
带别名：
SQL> merge into emp a
2 using (select 1 empno,
3 'Littlefoot' ename,
4 10 deptno,
5 null as job,
6 null as sal
7 from dual
8 ) b
9 on (a.empno = b.empno)
10 when not matched then insert (deptno, empno, ename)
11 values (b.deptno, b.empno, b.ename);
1 row merged.
SQL>