Oracle SQL MERGE query missing keyword issue-CodePudding

Not sure what keyword I am missing here.. From various examples on the internet and the docs it looks right.. Any help is much appreciated. Could it be because Oracle does not support nested MERGE statements?

The inputs are:

workflowId='CHILD1'
processName='TEST4'
sectionId=''
dependsOnWorkflowId='PARENT1'
dependsOnProcessName='TEST4'
dependsOnSectionId=''

The query is

  MERGE INTO WF_CMN_A_PROCESS_DEPENDENCY_TREE_TEST3 USING DUAL 
  ON (    "WORKFLOW_ID"             = :workflowId
      AND "PROCESS_NAME"            = :processName
      AND NVL("SECTION_ID", '-')    = NVL(:sectionId, '-')
    )
  -- Root node exists, create a parent-child link and delete the root node
  WHEN MATCHED THEN UPDATE SET 
      "DEPENDS_ON_WORKFLOW_ID" = :dependsOnWorkflowId,
      "DEPENDS_ON_PROCESS_NAME"= :dependsOnProcessName,
      "DEPENDS_ON_SECTION_ID"  = :dependsOnSectionId,
      "LAST_UPDT_DT_TM"        = SYSDATE
    WHERE 
            "WORKFLOW_ID"             = :workflowId
        AND "PROCESS_NAME"            = :processName
        AND NVL("SECTION_ID", '-')    = NVL(:sectionId, '-')
        AND "DEPENDS_ON_WORKFLOW_ID"  IS NULL 
        AND "DEPENDS_ON_PROCESS_NAME" IS NULL 
        AND "DEPENDS_ON_SECTION_ID"   IS NULL 
        
  WHEN NOT MATCHED THEN 
      MERGE INTO WF_CMN_A_PROCESS_DEPENDENCY_TREE_TEST3 USING DUAL 
      ON (  "WORKFLOW_ID"                      = :workflowId
        AND "PROCESS_NAME"                     = :processName
        AND NVL("SECTION_ID", '-')             = NVL(:sectionId, '-')
        AND NVL("DEPENDS_ON_WORKFLOW_ID" ,'-') = NVL(:dependsOnWorkflowId , '-')
        AND NVL("DEPENDS_ON_PROCESS_NAME",'-') = NVL(:dependsOnProcessName, '-')
        AND NVL("DEPENDS_ON_SECTION_ID"  ,'-') = NVL(:dependsOnSectionId  , '-')
      )
      WHEN NOT MATCHED THEN 
      INSERT (
        "WORKFLOW_ID",
        "PROCESS_NAME",
        "SECTION_ID",
        "DEPENDS_ON_WORKFLOW_ID",
        "DEPENDS_ON_PROCESS_NAME",
        "DEPENDS_ON_SECTION_ID",
        "LAST_UPDT_DT_TM"
      ) 
      VALUES (        
        :workflowId,
        :processName,
        :sectionId,
        :dependsOnWorkflowId,
        :dependsOnProcessName,
        :dependsOnSectionId,
        SYSDATE
      )

Here's the error:

      org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [905] [42000]: ORA-00905: missing keyword

at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:544)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:451)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:458)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:847)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3515)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4852)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00905: missing keyword

CodePudding user response：

You can't have a MERGE inside a MERGE statement. Your only options are WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT. I suspect that you want something like:

  MERGE INTO WF_CMN_A_PROCESS_DEPENDENCY_TREE_TEST3 USING DUAL 
  ON (    "WORKFLOW_ID"             = :workflowId
      AND "PROCESS_NAME"            = :processName
      AND NVL("SECTION_ID", '-')    = NVL(:sectionId, '-')
    )
  -- Root node exists, create a parent-child link and delete the root node
  WHEN MATCHED THEN UPDATE SET 
      "DEPENDS_ON_WORKFLOW_ID" = :dependsOnWorkflowId,
      "DEPENDS_ON_PROCESS_NAME"= :dependsOnProcessName,
      "DEPENDS_ON_SECTION_ID"  = :dependsOnSectionId,
      "LAST_UPDT_DT_TM"        = SYSDATE
    WHERE 
            "WORKFLOW_ID"             = :workflowId
        AND "PROCESS_NAME"            = :processName
        AND NVL("SECTION_ID", '-')    = NVL(:sectionId, '-')
        AND "DEPENDS_ON_WORKFLOW_ID"  IS NULL 
        AND "DEPENDS_ON_PROCESS_NAME" IS NULL 
        AND "DEPENDS_ON_SECTION_ID"   IS NULL 
        
  WHEN NOT MATCHED THEN INSERT (
        "WORKFLOW_ID",
        "PROCESS_NAME",
        "SECTION_ID",
        "DEPENDS_ON_WORKFLOW_ID",
        "DEPENDS_ON_PROCESS_NAME",
        "DEPENDS_ON_SECTION_ID",
        "LAST_UPDT_DT_TM"
      ) 
      VALUES (        
        :workflowId,
        :processName,
        :sectionId,
        :dependsOnWorkflowId,
        :dependsOnProcessName,
        :dependsOnSectionId,
        SYSDATE
      )

although I'm sure I've missed some nuances of what you're doing.

MERGE statement docs here