SSC-FDM-OR0018

Merge statement may do not work as expected

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 [e-mail]