SSC-EWI-0027

The following statement uses a variable/literal with an invalid query and it will not be executed.

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.

Some parts in the output code are omitted for clarity reasons.

Severity

Medium

Description

This warning is used to report that a specific statement uses a variable or literal with an invalid query and for that reason, it will not be executed.

Example Code

Input Code:

IN -> Teradata_01.sql

REPLACE PROCEDURE TEST.COLLECT_STATS () 
BEGIN
  COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME);

  SET STATS_STATEMENT = 'COLLECT STATS ON ' || OUT_DB || '.' || OUT_TBL || ' COLUMN(' || C4.ColumnName || ');';

  EXECUTE IMMEDIATE STATS_STATEMENT;

  EXECUTE IMMEDIATE 'COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME);';

  SET STATS_STATEMENT_NOT_DYNAMIC = 'COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME);';

  EXECUTE IMMEDIATE STATS_STATEMENT_NOT_DYNAMIC;

END;
;

Output Code:

OUT -> Teradata_01.sql

CREATE OR REPLACE PROCEDURE TEST.COLLECT_STATS ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
  BEGIN
--    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. COLLECT **
--    COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME);
    STATS_STATEMENT := 'COLLECT STATS ON ' || OUT_DB || '.' || OUT_TBL || ' COLUMN(' || C4.ColumnName || ')';
    !!!RESOLVE EWI!!! /*** SSC-EWI-0027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!

    EXECUTE IMMEDIATE STATS_STATEMENT;
    !!!RESOLVE EWI!!! /*** SSC-EWI-0027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!

    EXECUTE IMMEDIATE 'COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME)';
    STATS_STATEMENT_NOT_DYNAMIC := 'COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME)';
    !!!RESOLVE EWI!!! /*** SSC-EWI-0027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!

    EXECUTE IMMEDIATE STATS_STATEMENT_NOT_DYNAMIC;
  END;
$$;

Recommendations

• Check if a cast to a Date, Time, or Timestamp is necessary for the binding. Some cases are not necessary because an implicit conversion is done to the value.

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