In Oracle, when I use SUBSTR() in merge, there's an error saying "missing comma". I can't find out, where do I need a comma, or do I use the wrong clause?

WHEN NOT MATCHED THEN
        INSERT INTO A      
        (
        A.EMP_ID,
        A.HOLI_DATE,
        A.HOLI_HOURS,
        A.HOLI_REASON,
        A.START_TIME,
        A.END_TIME,
        A.AGENT_EMP_ID,
        A.VERSION,
        A.CREATETIME,
        --,TG.CREATOR
        --,TG.MODIFIER
        A.LASTUPDATE)
VALUES
 substr(B.EMP_ID,3,6)as EMP_ID,--this is the line with error"missing 
 comma"
        B.HOLI_DATE,
        B.HOLI_HOURS,
        B.HOLI_REASON,
        SUBSTR(B.HOLI_TIME,1,2)||':'||SUBSTR(B.HOLI_TIME,3,2) AS 
START_TIME,
        SUBSTR(B.HOLI_TIME,6,2)||':'||SUBSTR(B.HOLI_TIME,8,2) AS 
END_TIME,
        SUBSTR(B.AGENT_EMP_ID,3,6) AS AGENT_EMP_ID,
        1,
        sysdate,
        --,$$PLSQL_UNIT
        --,$$PLSQL_UNIT
        sysdate
FROM B

1 Answers

0
hotfix On

you have to remove INTO A after INSERT the Syntax in a merge statement is:

merge into A
  using(select col1, col2, col3... from B)
  on (A.col1 = B.value1)
WHEN NOT MATCHED THEN
    INSERT (col1,col2...)
    VALUES (B.value1, B.value2...)
WHEN MATCHED THEN
    UPDATE SET col2= B.value2
...

Don't forget parenthesis around value list