Merge Statement Throws ORA-00918: column ambiguously defined Even after using Alias name

Question

I am trying to use a merge statement to update a set of columns in the oracle database but when executing the Merge statement oracle keeps throwing

ORA-00918: column ambiguously defined

even after using Alias name

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         
                                                                                                                                                                                                                                                                                     

Answer 1

Those two NULL values are ambiguous:

 AA_PERSON_ASGN_NATURAL_KEY,
 NULL,                               --> this
 NULL,                               --> this
 MAX(SRC_CREATED_DATE)     AS SRC_CREATED_DATE,

Either remove them (as you, obviously, don't need/use them), or provide aliases for them.

---

To illustrate it on Scott's EMP table:

No aliases:

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

With aliases:

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>