This is a deprecated version of the SnowConvert documentation, please visit the official site .
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 the value, a cast may be necessary to work properly.
Example Code
Input Code (Oracle):
CREATE TABLE TABLE1 (COL1 DATE);
CREATE TABLE TABLE2 (COL1 VARCHAR(25));
CREATE OR REPLACE PROCEDURE EXAMPLE
IS
CURSOR C1 IS SELECT * FROM TABLE1;
BEGIN
FOR REC1 IN C1 LOOP
insert into TABLE2 values (TO_CHAR(REC1.COL1, 'DD-MM-YYYY'));
END LOOP;
END;
Output Code:
CREATE OR REPLACE TABLE PUBLIC.TABLE1 ( COL1 DATE);
CREATE OR REPLACE TABLE PUBLIC.TABLE2 ( COL1 VARCHAR(25));
CREATE OR REPLACE PROCEDURE PUBLIC.EXAMPLE()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// Additional helpers here
let C1 = new CURSOR(`SELECT * FROM PUBLIC.TABLE1`,() => []);
C1.OPEN();
//** MSC-WARNING - MSCEWI1023 - PERFORMANCE REVIEW - THIS LOOP CONTAINS AN INSERT, DELETE OR UPDATE STATEMENT **
while ( C1.NEXT() ) {
let REC1 = C1.CURRENT;
EXEC(`insert into PUBLIC.TABLE2 values (TO_CHAR(/*** MSC-WARNING - MSCEWI1026 - 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 REPLACE TABLE PUBLIC.TABLE1 ( COL1 DATE);
CREATE OR REPLACE TABLE PUBLIC.TABLE2 ( COL1 VARCHAR(25));
CREATE OR REPLACE PROCEDURE PUBLIC.EXAMPLE()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// Additional helpers here
let C1 = new CURSOR(`SELECT * FROM PUBLIC.TABLE1`,() => []);
C1.OPEN();
//** MSC-WARNING - MSCEWI1023 - PERFORMANCE REVIEW - THIS LOOP CONTAINS AN INSERT, DELETE OR UPDATE STATEMENT **
while ( C1.NEXT() ) {
let REC1 = C1.CURRENT;
EXEC(`insert into PUBLIC.TABLE2 values (TO_CHAR(?::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.
