title: How to use ORACLE MERGE statement to INSERT values using DECODE?
body: <p>I'm trying to use MERGE for an UPSERT. The update code works well (I haven't posted here) with this MERGE statement but INSERT is giving the following errors:</p>
  
  <ul>
  <li>ORA-00963 ---Missing expression (when SELECT is mentioned in INSERT-VALUES)</li>
  <li>ORA-00917 ---Missing comma (when SELECT is removed)</li>
  <li>When I format this code, it calls for a syntax check near VALUES &amp; points towards the AS clause.</li>
  </ul>
  
  <p>Basically I want to write the MERGE-INSERT statement that accommodates the DECODE statement as well.
  Please help me as I'm new to Oracle.</p>
  
  <pre><code>MERGE INTO Table1 t1
     USING(SELECT DISTINCT A_cd, F_str, a_pm, a_type 
          FROM Table2) t2
       ON(t1.C_Name=t2.A_cd)
  WHEN NOT MATCHED 
  THEN
   INSERT(C_type, C_name, C_value)
    VALUES (SELECT t3.C_type, t4.A_cd as C_name,
          DECODE(t3.c_type,
                 'A COMP', t4.f_str,
                 'A_PM', t4.a_pm,
                 'A_TYPE' t4.a_type) as c_value)
   FROM(SELECT 'A_COMP' as c_type FROM DUAL
      UNION ALL
      SELECT 'A_PM' as c_type FROM DUAL
      UNION ALL
      SELECT 'A_TYPE' as c_type FROM DUAL)t3,
     (SELECT DISTINCT A_cd, F_str, a_pm, a_type
      FROM Table2 t2
      WHERE A_cd || F_str || a_pm || a_type
      IS NOT NULL
      AND NOT EXISTS
      (SELECT NULL
      FROM Table1 t1
      WHERE t2.A_cd=t1.c_name))t4;
  </code></pre>