SSC-FDM-OR0018

Merge statement may do not work as expected

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

This warning is used to indicate that the Snowflake merge statement may have some functional differences compared to Oracle.

Example Code

Input Code:

IN -> Oracle_01.sql

MERGE INTO people_target pt 
USING people_source ps 
ON    (pt.person_id = ps.person_id) 
WHEN MATCHED THEN UPDATE 
  SET pt.first_name = ps.first_name, 
      pt.last_name = ps.last_name, 
      pt.title = ps.title 
  DELETE where pt.title  = 'Mrs.' 
WHEN NOT MATCHED THEN INSERT 
  (pt.person_id, pt.first_name, pt.last_name, pt.title) 
  VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title) 
  WHERE ps.title = 'Mr';

Output Code:

OUT -> Oracle_01.sql

--** SSC-FDM-OR0018 - SNOWFLAKE MERGE STATEMENT MAY HAVE SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE **
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "people_target", "people_source" **
MERGE INTO people_target pt
USING people_source ps
ON    (pt.person_id = ps.person_id)
      WHEN MATCHED AND pt.title  = 'Mrs.' THEN
        DELETE
      WHEN MATCHED THEN
        UPDATE SET
          pt.first_name = ps.first_name,
               pt.last_name = ps.last_name,
               pt.title = ps.title
      WHEN NOT MATCHED AND ps.title = 'Mr' THEN
        INSERT
        (pt.person_id, pt.first_name, pt.last_name, pt.title)
        VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);

Recommendations

• If you are getting different results compared to Oracle, consider the following:

  • For execution order prioritization, go to the next link to get more information.

    • Execute the skipped DML statements outside (before or after accordingly) the merge statement.

• If you need more support, you can email us at [email protected]