Oracle SQL MERGE into Query ORA-00905: missing keyword-CodePudding

I'm getting the below error using the Merge query

Error at Command Line : 13 Column : 1 Error report - SQL Error: ORA-00905: missing keyword

MERGE INTO RPTDetectionAndIsolation_GVT T     
USING (select (SELECT ReportOwner FROM REPORTS_GVT WHERE ID  = 5000) as repo,
(SELECT ALLOCATEDTEAMID FROM Tasks_GVT WHERE TaskCode = 298454) as alloc,  
(SELECT TaskStatus FROM Tasks_GVT WHERE TaskCode = 298454) as taskstatus, 
case when exists(SELECT 1 FROM RPTDetectionAndIsolation_GVT WHERE TaskCode =  298454) 
then 0 else 1 End isInsert, 
298454 as TaskCode ,
'431-20220317221227' as pk ,
'22:06' as estimatedTime
from dual) dum
ON (dum.repo = 1 AND dum.alloc = 431 AND dum.taskstatus = 3 AND dum.isInsert = 1)
WHEN MATCHED THEN 
INSERT (TaskCode, pk, estimatedTime) values(dum.TaskCode, dum.pk, dum.estimatedTime)
WHEN NOT MATCHED THEN 
Update SET T.estimatedTime= '22:06' WHERE dum.repo= 1;

CodePudding user response：

That doesn't make sense - you'd want to insert when there's a match, and update when there's no match? Apart from not making sense, it isn't even supported (check merge update and insert clauses in documentation).

Should be just the opposite:

MERGE INTO RPTDetectionAndIsolation_GVT T     
USING (select (SELECT ReportOwner FROM REPORTS_GVT WHERE ID  = 5000) as repo,
              (SELECT ALLOCATEDTEAMID FROM Tasks_GVT WHERE TaskCode = 298454) as alloc,  
              (SELECT TaskStatus FROM Tasks_GVT WHERE TaskCode = 298454) as taskstatus, 
               case when exists (SELECT 1 FROM RPTDetectionAndIsolation_GVT WHERE TaskCode =  298454) 
                    then 0 
                    else 1 
               End isInsert, 
               298454 as TaskCode ,
               '431-20220317221227' as pk ,
               '22:06' as estimatedTime
       from dual) dum
ON (    dum.repo = 1  
    AND dum.alloc = 431 
    AND dum.taskstatus = 3 
    AND dum.isInsert = 1)
WHEN MATCHED THEN                                         --> this is different from your code
  Update SET T.estimatedTime = '22:06' WHERE dum.repo= 1
WHEN NOT MATCHED THEN 
  INSERT (TaskCode, pk, estimatedTime) values 
    (dum.TaskCode, dum.pk, dum.estimatedTime);