SSC-FDM-PG0012

NOT NULL constraint has been removed. Assigning NULL to this variable will no longer cause a failure.

Description

In Redshift, specifying the NOT NULL constraint ensures that assigning a null value to a variable results in a runtime error. Since this clause does not exist in Snowflake, it is removed during transformation and assigning a NULL to this variable will no longer fail in execution.

Code Example

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE variable_Not_Null()
LANGUAGE plpgsql
AS $$
DECLARE
    v_notnull VARCHAR NOT NULL DEFAULT 'Test default';
BEGIN
    v_notnull := NULL;
    -- Procedure logic
END;
$$;

Result

[22004] ERROR: NULL cannot be assigned to variable "v_notnull" declared NOT NULL

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE variable_Not_Null ()
RETURNS VARCHAR
LANGUAGE SQL
AS $$
DECLARE
    --** SSC-FDM-PG0012 - NOT NULL CONSTRAINT HAS BEEN REMOVED. ASSIGNING NULL TO THIS VARIABLE WILL NO LONGER CAUSE A FAILURE. **
    v_notnull VARCHAR DEFAULT 'Test default';
BEGIN
    v_notnull := NULL;
    -- Procedure logic
END;
$$;

Result

This assignment will not fail in Snowflake.

Recommendations

• Review the procedure logic to ensure this variable is not assigned a NULL value.

• If you need more support, you can email us at [e-mail]