MSCEWI1026

Qualified variables may require a cast.

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

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.

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