The EWI is only generated when Javascript is the target language for Stored Procedures. This is a deprecated translation feature, as Snowflake Scripting is the recommended target language for Stored Procedures.
Some parts in the output code are omitted for clarity reasons.
Severity
Low
Description
This warning is added when there is a query with a variable with a qualified member like an Oracle record or a Teradata for loop variable. Depending on where the variable is being used and the type of value, a cast may be necessary to work properly.
Example Code
Input Code:
IN -> Oracle_01.sql
CREATETABLETABLE1 (COL1 DATE);CREATETABLETABLE2 (COL1 VARCHAR(25));CREATEORREPLACEPROCEDURE EXAMPLEISCURSOR C1 ISSELECT*FROM TABLE1;BEGINFOR REC1 IN C1 LOOPinsert into TABLE2 values (TO_CHAR(REC1.COL1, 'DD-MM-YYYY'));ENDLOOP;END;
Output Code:
OUT -> Oracle_01.sql
-- Additional Params: -t javascriptCREATE OR REPLACE TABLE TABLE1 (COL1 TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
)COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;CREATE OR REPLACETABLETABLE2 (COL1 VARCHAR(25))COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;CREATEORREPLACEPROCEDURE EXAMPLE ()RETURNS STRINGLANGUAGE JAVASCRIPTCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTEASCALLERAS$$// SnowConvert Helpers Code section is omitted. let C1 = new CURSOR(`SELECT * FROM TABLE1`,() => []); C1.OPEN();//** SSC-EWI-0023- PERFORMANCE REVIEW - THIS LOOP CONTAINS AN INSERT, DELETEORUPDATESTATEMENT**while ( C1.NEXT() ) { let REC1 = C1.CURRENT;EXEC(`insert into TABLE2 values (TO_CHAR( !!!RESOLVE EWI!!! /*** SSC-EWI-0026 - THE VARIABLE REC1.COL1 MAY REQUIRE A CAST TO DATE, TIME OR TIMESTAMP ***/!!!
?, 'DD-MM-YYYY'))`,[REC1.COL1]); } C1.CLOSE();$$;
Output Code with adjustments:
CREATE OR REPLACETABLETABLE1 (COL1 TIMESTAMP)COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;CREATE OR REPLACETABLETABLE2 (COL1 VARCHAR(25))COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;CREATEORREPLACEPROCEDURE EXAMPLE ()RETURNS STRINGLANGUAGE JAVASCRIPTCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTEASCALLERAS$$// SnowConvert Helpers Code section is omitted. let C1 = new CURSOR(`SELECT * FROM TABLE1`,() => []); C1.OPEN();//** SSC-EWI-0023- PERFORMANCE REVIEW - THIS LOOP CONTAINS AN INSERT, DELETEORUPDATESTATEMENT**while ( C1.NEXT() ) { let REC1 = C1.CURRENT;EXEC(`insert into TABLE2 values (TO_CHAR(REC1.COL1::DATE, 'DD-MM-YYYY'))`,[REC1.COL1]); } C1.CLOSE();$$;
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.