MSCEWI1027

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

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

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:

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:

The warning message appears every time an Execute uses a statement (variable or literal) with an invalid query. In the example, those lines are 15, 18, and 22.

CREATE OR REPLACE PROCEDURE TEST.PUBLIC.COLLECT_STATS
(
)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
 	// REGION SnowConvert Helpers Code
...
	// END REGION
	
	STATS_STATEMENT = `COLLECT STATS ON ${OUT_DB}.${OUT_TBL} COLUMN(${C4.ColumnName})`;
	/* ** MSC-ERROR - MSCEWI1027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ** */
	/* EXECUTE IMMEDIATE STATS_STATEMENT */
	;
	/* ** MSC-ERROR - MSCEWI1027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ** */
	/* EXECUTE IMMEDIATE 'COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME);' */
	;
	STATS_STATEMENT_NOT_DYNAMIC = `COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME)`;
	/* ** MSC-ERROR - MSCEWI1027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ** */
	/* EXECUTE IMMEDIATE STATS_STATEMENT_NOT_DYNAMIC */
	;
 
$$;

Recommendations

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