SSC-EWI-0026

Qualified variables may require a cast.

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

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:

OUT -> Oracle_01.sql

-- Additional Params: -t javascript
CREATE 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 REPLACE TABLE TABLE2 (COL1 VARCHAR(25))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE EXAMPLE ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	// 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, DELETE OR UPDATE STATEMENT **
	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 REPLACE TABLE TABLE1 (COL1 TIMESTAMP
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE TABLE TABLE2 (COL1 VARCHAR(25))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE EXAMPLE ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	// 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, DELETE OR UPDATE STATEMENT **
	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.

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