MSCEWI1075

Current of clause is not supported in Snowflake

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

High

Description

Some statements like UPDATE and DELETE can use have a CURRENT OF clause inside the WHERE clause, this is not currently supported by Snowflake.

Example Code

Oracle:

CREATE OR REPLACE PROCEDURE proc_update_current_of
AS
  CURSOR C1
  IS
    SELECT * FROM F_EMPLOYEE FOR UPDATE OF SALARY nowait;
BEGIN
  FOR CREC IN C1
  LOOP
    UPDATE F_EMPLOYEE SET SALARY=SALARY+2000 WHERE CURRENT OF C1;
  END LOOP;
END;

Snowflake Scripting:

CREATE OR REPLACE PROCEDURE PUBLIC.proc_update_current_of ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      C1 CURSOR FOR
      SELECT * FROM PUBLIC.F_EMPLOYEE FOR UPDATE OF SALARY nowait;
   BEGIN
      FOR CREC IN C1 DO
-- ** MSC-ERROR - MSCEWI1075 - CURRENT OF CLAUSE IS NOT SUPPORTED IN SNOWFLAKE **
--         UPDATE F_EMPLOYEE SET SALARY= /*** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/
--SALARY+2000 WHERE CURRENT OF C1
                               ;
      END FOR;
   END;
$$;

Recommendations

• Redesign the query to normal UPDATE or DELETE specifying the columns in the WHERE clause, consider that if there are duplicate records in the table the query can affect them multiple times.

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