The following statement uses a variable/literal with an invalid query and it will not be executed.
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.
