This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.
Severity
Low
Description
There is no equivalent for FOR UPDATE clause in Snow Scripting so an EWI is added and the clause is commented out
Example Code
Input Code:
CREATE OR REPLACE PROCEDURE for_update_clause
AS
update_record f_employee%rowtype;
CURSOR c1 IS SELECT * FROM f_employee FOR UPDATE OF employee_number nowait;
BEGIN
FOR CREC IN C1 LOOP
UPDATE f_employee SET employee_number = employee_number + 1000 WHERE CURRENT OF c1;
IF crec.id = 2 THEN
DELETE FROM f_employee WHERE CURRENT OF c1;
EXIT;
END IF;
END LOOP;
END;
Output Code:
CREATE OR REPLACE PROCEDURE PUBLIC.for_update_clause ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
update_record VARIANT;
c1 CURSOR FOR SELECT * FROM PUBLIC.f_employee
-- ** MSC-WARNING - MSCEWI3110 - FOR UPDATE CLAUSE IS NOT SUPPORTED IN SNOWFLAKE **
-- FOR UPDATE OF employee_number nowait
;
BEGIN
FOR CREC IN C1 DO
-- ** MSC-ERROR - MSCEWI1075 - CURRENT OF CLAUSE IS NOT SUPPORTED IN SNOWFLAKE **
-- UPDATE f_employee SET employee_number = /*** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/
-- employee_number + 1000 WHERE CURRENT OF c1
;
IF ( crec.id = 2) THEN
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'CURRENT OF' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
-- DELETE FROM PUBLIC.f_employee WHERE CURRENT OF c1
;
EXIT;
END IF;
END FOR;
END;
$$;
Recommendations
Handle the column update in the UPDATE/DELETE query for more details check MSCEWI1075.
