SnowConvert: General Issues¶
SSC-EWI-0003¶
Severity¶
Critical
Description¶
This issue occurs when there is an error while executing a conversion rule for a specific SQL node in the source code.
Recommendations¶
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0052¶
Severity¶
Medium
Description¶
This error occurs when the conversion tool cannot recognize or does not support a parameter or variable in the source code.
Example code¶
Input Code (Oracle):¶
-- Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE PROCEDURE_PARAMETERS(PARAM SDO_GEOMETRY)
AS
VARIABLE SDO_GEOMETRY;
BEGIN
VARIABLE := PARAM;
END;
Output Code:¶
CREATE OR REPLACE PROCEDURE PROCEDURE_PARAMETERS (PARAM GEOMETRY)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
var fixBind = function (arg) {
arg = arg instanceof Date ? formatDate(arg) : IS_NULL(arg) ? null : arg;
return arg;
};
var SQL = {
FOUND : false,
NOTFOUND : false,
ROWCOUNT : 0,
ISOPEN : false
};
var _RS, _ROWS, SQLERRM = "normal, successful completion", SQLCODE = 0;
var getObj = (_rs) => Object.assign(new Object(),_rs);
var getRow = (_rs) => (values = Object.values(_rs)) && (values = values.splice(-1 * _rs.getColumnCount())) && values;
var fetch = (_RS,_ROWS,fmode) => _RS.getRowCount() && _ROWS.next() && (fmode ? getObj : getRow)(_ROWS) || (fmode ? new Object() : []);
var EXEC = function (stmt,binds,opts) {
try {
binds = !(arguments[1] instanceof Array) && ((opts = arguments[1]) && []) || (binds || []);
opts = opts || new Object();
binds = binds ? binds.map(fixBind) : binds;
_RS = snowflake.createStatement({
sqlText : stmt,
binds : binds
});
_ROWS = _RS.execute();
if (opts.sql !== 0) {
var isSelect = stmt.toUpperCase().trimStart().startsWith("SELECT");
var affectedRows = isSelect ? _RS.getRowCount() : _RS.getNumRowsAffected();
SQL.FOUND = affectedRows != 0;
SQL.NOTFOUND = affectedRows == 0;
SQL.ROWCOUNT = affectedRows;
}
if (opts.row === 2) {
return _ROWS;
}
var INTO = function (opts) {
if (opts.vars == 1 && _RS.getColumnCount() == 1 && _ROWS.next()) {
return _ROWS.getColumnValue(1);
}
if (opts.rec instanceof Object && _ROWS.next()) {
var recordKeys = Object.keys(opts.rec);
Object.assign(opts.rec,Object.fromEntries(new Map(getRow(_ROWS).map((element,Index) => [recordKeys[Index],element]))))
return opts.rec;
}
return fetch(_RS,_ROWS,opts.row);
};
var BULK_INTO_COLLECTION = function (into) {
for(let i = 0;i < _RS.getRowCount();i++) {
FETCH_INTO_COLLECTIONS(into,fetch(_RS,_ROWS,opts.row));
}
return into;
};
if (_ROWS.getRowCount() > 0) {
return _ROWS.getRowCount() == 1 ? INTO(opts) : BULK_INTO_COLLECTION(opts);
}
} catch(error) {
RAISE(error.code,error.name,error.message)
}
};
var RAISE = function (code,name,message) {
message === undefined && ([name,message] = [message,name])
var error = new Error(message);
error.name = name
SQLERRM = `${(SQLCODE = (error.code = code))}: ${message}`
throw error;
};
var FETCH_INTO_COLLECTIONS = function (collections,fetchValues) {
for(let i = 0;i < collections.length;i++) {
collections[i].push(fetchValues[i]);
}
};
var IS_NULL = (arg) => !(arg || arg === 0);
// END REGION
let VARIABLE = new SDO_GEOMETRY();
VARIABLE =
!!!RESOLVE EWI!!! /*** SSC-EWI-0052 - UNUSABLE OBJECT PARAM, ITS DATATYPE WAS NOT TRANSFORMED ***/!!!
PARAM;
$$;
Recommendations¶
Consider using a different data type that better suits your needs.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0066¶
Severity ¶
High
Description ¶
This error indicates that a particular expression is not compatible with or supported by Snowflake.
Example Code ¶
Input Code:¶
SELECT * from T1 where (cast('2016-03-17' as DATE),
cast('2016-03-21' as DATE)) OVERLAPS
(cast('2016-03-20' as DATE), cast('2016-03-22' as DATE));
Output Code:¶
SELECT * from
T1
where
!!!RESOLVE EWI!!! /*** SSC-EWI-0066 - EXPRESSION 'OVERLAPS' IS NOT SUPPORTED IN SNOWFLAKE. ***/!!! (cast('2016-03-17' as DATE),
cast('2016-03-21' as DATE)) OVERLAPS
(cast('2016-03-20' as DATE), cast('2016-03-22' as DATE));
Recommendations ¶
For additional support, please contact our support team at snowconvert-support@snowflake.com
SSC-EWI-0027¶
Parts of the output code have been removed to make the example clearer and easier to follow.
Severity¶
Medium
Description¶
This warning indicates that a statement cannot be executed because it contains an invalid query due to an incorrect variable or literal value.
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:¶
CREATE OR REPLACE PROCEDURE TEST.COLLECT_STATS ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
-- --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. COLLECT **
-- COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME);
STATS_STATEMENT := 'COLLECT STATS ON ' || OUT_DB || '.' || OUT_TBL || ' COLUMN(' || C4.ColumnName || ')';
!!!RESOLVE EWI!!! /*** SSC-EWI-0027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE STATS_STATEMENT;
!!!RESOLVE EWI!!! /*** SSC-EWI-0027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE 'COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME)';
STATS_STATEMENT_NOT_DYNAMIC := 'COLLECT STATS ON DBC.AccessRights COLUMN(COLNAME)';
!!!RESOLVE EWI!!! /*** SSC-EWI-0027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE STATS_STATEMENT_NOT_DYNAMIC;
END;
$$;
Recommendations¶
Verify if you need to explicitly convert values to Date, Time, or Timestamp formats. In many cases, Snowflake automatically converts these values, making explicit conversion unnecessary.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0102¶
Severity¶
Low
Description¶
When Snowflake statements are processed by conversion rules, certain options may be omitted in the output code. However, the resulting functionality remains the same as the original code.
Example code¶
Input Code (PostgreSQL):¶
-- Case 1:
TRUNCATE ONLY table_base2 RESTART IDENTITY CASCADE;
-- Case 2:
TRUNCATE TABLE table_inherit_and_generated RESTART IDENTITY CASCADE;
Output Code:¶
-- Case 1:
!!!RESOLVE EWI!!! /*** SSC-EWI-0102 - REMOVED ONLY OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0102 - REMOVED CASCADE OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0102 - REMOVED RESTART IDENTITY OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE ***/!!!
TRUNCATE table_base2;
-- Case 2:
!!!RESOLVE EWI!!! /*** SSC-EWI-0102 - REMOVED CASCADE OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0102 - REMOVED RESTART IDENTITY OPTION FROM CODE, ALREADY HANDLED IN CONVERSION TO SNOWFLAKE ***/!!!
TRUNCATE TABLE table_inherit_and_generated;
Recommendations¶
No action is required from users.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0013¶
Severity¶
Critical
Description¶
This error occurs when the system encounters an issue while converting code from the source format.
Recommendation¶
For additional support, please contact our support team at snowconvert-support@snowflake.com
SSC-EWI-0033¶
Parts of the output code have been removed to make it easier to understand.
Severity¶
Low
Description¶
This warning appears when the source code does not contain a column that is being converted using a CAST function with a specified output format.
Example Code¶
Input Code (Teradata):¶
CREATE VIEW SampleView AS
SELECT
DAY_DATE(FORMAT 'MMM-YYYY')(CHAR(8))
FROM
SampleTable;
Output Code:¶
// SnowConvert Helpers Code section is omitted.
CREATE OR REPLACE VIEW SampleView
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
AS
SELECT
CAST(RPAD(TO_VARCHAR(
DAY_DATE !!!RESOLVE EWI!!! /*** SSC-EWI-0033 - FORMAT 'MMM-YYYY' REMOVED, SEMANTIC INFORMATION NOT FOUND. ***/!!!), 8) AS CHAR(8))
FROM
SampleTable;
Recommendations¶
Verify that all required tables and views referenced in the procedure statement have been successfully migrated.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0062¶
Severity¶
Low
Description¶
This message appears when a Custom Type is referenced and later modified to use a variant data type.
This message is closely connected to error code SSC-EWI-0056.
Example code¶
Input Code (Oracle):¶
CREATE TYPE type1 AS OBJECT(type1_column1 INT);
CREATE TABLE table1
(
column1 type1
);
Output Code:¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO VARIANT ***/!!!
CREATE TYPE type1 AS OBJECT(type1_column1 INT)
;
CREATE OR REPLACE TABLE table1
(
column1 VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'type1' USAGE CHANGED TO VARIANT ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
CREATE OR REPLACE VIEW PUBLIC.table1_view
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "" }}'
AS
SELECT
column1:type1_column1 :: INTEGER AS type1_column1
FROM
table1;
Recommendations¶
Make sure to convert all input data into a Variant-compatible data type.
For additional assistance, please contact us at snowconvert-support@snowflake.com.
SSC-EWI-0056¶
Severity¶
Low
Description¶
This message appears when a Custom Type that can be transformed is detected. It provides feedback about the Custom Types that will be commented.
The type definition has a comment but it is still used to resolve references. For more details, please refer to SSC-EWI-0062.
Example code¶
Input Code (Oracle):¶
CREATE TYPE type1 AS OBJECT (column1 INT);
CREATE OR REPLACE PROCEDURE record_procedure
IS
TYPE record_typ IS RECORD(col1 INTEGER, col2 FLOAT);
BEGIN
NULL;
END;
Output Code:¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO VARIANT ***/!!!
CREATE TYPE type1 AS OBJECT (column1 INT)
;
CREATE OR REPLACE PROCEDURE record_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
!!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!
TYPE record_typ IS RECORD(col1 INTEGER, col2 FLOAT);
BEGIN
NULL;
END;
$$;
Recommendations¶
No action is required from end users.
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0007¶
Severity¶
Critical
Description¶
This error occurs when there is a problem writing to the output file.
Recommendations¶
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0046¶
Severity¶
Critical
Description¶
Snowflake does not allow you to create functions or procedures inside other functions or procedures (nested declarations). When SnowConvert detects nested declarations in a CREATE FUNCTION or CREATE PROCEDURE statement, it will generate a warning message.
Code example¶
Input¶
CREATE OR REPLACE FUNCTION myFunction
RETURN INTEGER
IS
total_count INTEGER;
-- Function Declaration
FUNCTION function_declaration(param1 VARCHAR) RETURN INTEGER;
FUNCTION function_definition
RETURN INTEGER
IS
count INTEGER;
PROCEDURE procedure_declaration(param1 INTEGER)
IS
BEGIN
NULL;
END;
BEGIN
RETURN count;
end;
BEGIN
-- Your logic to calculate the total employee count goes here
RETURN total_count;
END;
Output¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0046 - NESTED FUNCTION/PROCEDURE DECLARATIONS ARE NOT SUPPORTED IN SNOWFLAKE. ***/!!!
CREATE OR REPLACE FUNCTION myFunction ()
RETURNS FLOAT
LANGUAGE JAVASCRIPT
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "12/16/2024", "domain": "test" }}'
AS
$$
let TOTAL_COUNT;
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0057 - TRANSFORMATION FOR NESTED PROCEDURE OR FUNCTION IS NOT SUPPORTED ***/!!!
/* -- Function Declaration
FUNCTION function_declaration(param1 VARCHAR) RETURN INTEGER; */
// Function Declaration
;
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0057 - TRANSFORMATION FOR NESTED PROCEDURE OR FUNCTION IS NOT SUPPORTED ***/!!!
/* FUNCTION function_definition
RETURN INTEGER
IS
count INTEGER;
PROCEDURE procedure_declaration(param1 INTEGER)
IS
BEGIN
NULL;
END;
BEGIN
RETURN count;
end; */
;
// Your logic to calculate the total employee count goes here
return TOTAL_COUNT;
$$;
Recommendations¶
Move any nested declarations outside of the function or procedure.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0023¶
The External Web Interface (EWI) is generated exclusively when translating Stored Procedures to JavaScript. However, this feature is no longer recommended, as Snowflake Scripting is now the preferred language for Stored Procedures.
Parts of the output code have been removed to make the example clearer and easier to follow.
Severity¶
Low
Description¶
This warning highlights potential performance implications that users should consider.
Example Code¶
Input Code:¶
-- Additional Params: -t javascript
REPLACE PROCEDURE Database1.Proc1()
BEGIN
DECLARE lNumber INTEGER DEFAULT 1;
FOR class1 AS class2 CURSOR FOR
SELECT COL0,
TRIM(COL1) AS COL1ALIAS,
TRIM(COL2),
COL3
FROM someDb.prefixCol
DO
INSERT INTO TempDB.Table1 (:lgNumber, :lNumber, (',' || :class1.ClassCD || '_Ind CHAR(1) NOT NULL'));
SET lNumber = lNumber + 1;
END FOR;
END;
Output Code:¶
CREATE OR REPLACE PROCEDURE Database1.Proc1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
var LNUMBER = 1;
/*** SSC-EWI-0023 - PERFORMANCE REVIEW - THIS LOOP CONTAINS AN INSERT, DELETE OR UPDATE STATEMENT ***/
for(var CLASS2 = new CURSOR(`SELECT
COL0,
TRIM(COL1) AS COL1ALIAS,
TRIM(COL2),
COL3
FROM
someDb.prefixCol`,[],false).OPEN();CLASS2.NEXT();) {
let CLASS1 = CLASS2.CURRENT;
EXEC(`INSERT INTO TempDB.Table1
VALUES (:lgNumber, :1, (',' || :
!!!RESOLVE EWI!!! /*** SSC-EWI-0026 - THE VARIABLE class1.ClassCD MAY REQUIRE A CAST TO DATE, TIME OR TIMESTAMP ***/!!!
:2 || '_Ind CHAR(1) NOT NULL'))`,[LNUMBER,CLASS1.CLASSCD]);
LNUMBER = LNUMBER + 1;
}
CLASS2.CLOSE();
$$;
Recommendations¶
For additional support, please contact our support team at snowconvert-support@snowflake.com
SSC-EWI-0107¶
Severity¶
High
Description¶
Intervals in Snowflake can only be used for mathematical calculations. Using intervals for any other purpose is not supported.
Example Code ¶
Input Code:
SELECT INTERVAL '1-5' YEAR TO MONTH FROM DUAL;
Output Code:
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-0107 - INTERVAL LITERAL IS NOT SUPPORTED BY SNOWFLAKE IN THIS SCENARIO ***/!!!
INTERVAL '1-5' YEAR TO MONTH FROM DUAL;
Recommendations ¶
No action is required from users.
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0073¶
Severity¶
Medium
Description¶
This warning appears when SnowConvert encounters a grammar structure in the source platform that hasn’t been evaluated by our development team for Snowflake compatibility. The code remains untransformed, which means it may require manual review and modification to work properly in Snowflake.
Example Code¶
SQLServer:¶
CREATE OR ALTER PROC SampleProcedure
AS
BEGIN
INSERT INTO aTable (columnA = 'varcharValue', columnB = 1);
INSERT exampleTable VALUES ('Hello', 23);
INSERT INTO exampleTable DEFAULT VALUES;
END
CREATE OR REPLACE PROCEDURE SampleProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
INSERT INTO aTable (columnA = 'varcharValue', columnB = 1);
INSERT INTO exampleTable VALUES ('Hello', 23);
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INSERT WITH DEFAULT VALUES' NODE ***/!!!
INSERT INTO exampleTable DEFAULT VALUES;
END;
$$;
In line 6 of the input code, there is an INSERT
statement using DEFAULT VALUES
. This syntax is not currently supported by SnowConvert, which is why an EWI (Error, Warning, Information) message appears in lines 11 and 12.
Recommendations¶
For additional support, please contact our support team at snowconvert-support@snowflake.com
SSC-EWI-0022¶
The External Web Interface (EWI) is generated exclusively when translating Stored Procedures to JavaScript. However, this feature is no longer recommended, as Snowflake Scripting is now the preferred language for Stored Procedures.
Parts of the output code have been removed to make the example clearer and easier to follow.
Severity¶
Medium
Description¶
This error indicates that certain identifiers within a statement are automatically treated as parameters by the system.
Example Code¶
Input Code:¶
-- Additional Params: -t javascript
CREATE MACRO SAME_MACRO_COLUMN_AND_PARAMATERS (
LOAD_USER_ID (VARCHAR (32), CHARACTER SET LATIN),
UPDATE_USER_ID (VARCHAR (32), CHARACTER SET LATIN)
) AS (
UPDATE TABLE1 SET LOAD_USER_ID = :LOAD_USER_ID, UPDATE_USER_ID = :UPDATE_USER_ID;
INSERT INTO TABLE1 (LOAD_USER_ID, UPDATE_USER_ID) VALUES (:LOAD_USER_ID, :UPDATE_USER_ID);
DELETE FROM TABLE1 WHERE :LOAD_USER_ID = LOAD_USER_ID;
);
Output Code:¶
CREATE OR REPLACE PROCEDURE SAME_MACRO_COLUMN_AND_PARAMATERS (LOAD_USER_ID VARCHAR (32), UPDATE_USER_ID VARCHAR (32))
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "teradata", "convertedOn": "12/16/2024", "domain": "test" }}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
EXEC(`UPDATE TABLE1
SET
LOAD_USER_ID = :1,
UPDATE_USER_ID = :2`,[LOAD_USER_ID,UPDATE_USER_ID]);
// ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
EXEC(`INSERT INTO TABLE1 (LOAD_USER_ID, UPDATE_USER_ID)
VALUES (:1, :2)`,[LOAD_USER_ID,UPDATE_USER_ID]);
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Delete' NODE ***/!!!
//DELETE FROM
// TABLE1
//WHERE
// RTRIM(:LOAD_USER_ID) = RTRIM(LOAD_USER_ID)
null
$$;
Recommendations¶
Verify that all required tables and views referenced in the procedure statement have been successfully migrated.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0080¶
Severity ¶
Low
Description ¶
This warning appears when your source code contains a BINARY data type with a default value, which Snowflake SQL does not support.
Example Code ¶
Input Code (SQL Server):
create table test1345
(
key1 binary default 0
);
Output Code:
CREATE OR REPLACE TABLE test1345
(
key1 BINARY
!!!RESOLVE EWI!!! /*** SSC-EWI-0080 - DEFAULT VALUE IS NOT ALLOWED ON BINARY COLUMNS ***/!!!
default 0
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
Recommendations ¶
For additional support, please contact our support team at snowconvert-support@snowflake.com
SSC-EWI-0002¶
Severity¶
Moderate
Description¶
Default parameters must be placed at the end of the parameter list in Snowflake. You will need to reorder any parameters with default values to ensure they appear last in the parameter declarations.
Example Code¶
Input Code:¶
CREATE PROCEDURE MySampleProc
@Param1 NVARCHAR(50) = NULL,
@Param2 NVARCHAR(10),
@Param3 NVARCHAR(10) = NULL,
@Param4 NVARCHAR(10)
AS
SELECT 1;
Output Code:¶
CREATE OR REPLACE PROCEDURE MySampleProc
!!!RESOLVE EWI!!! /*** SSC-EWI-0002 - DEFAULT PARAMETERS MAY NEED TO BE REORDERED. SNOWFLAKE ONLY SUPPORTS DEFAULT PARAMETERS AT THE END OF THE PARAMETERS DECLARATIONS ***/!!!
(PARAM1 STRING DEFAULT NULL, PARAM2 STRING, PARAM3 STRING DEFAULT NULL, PARAM4 STRING)
RETURNS TABLE()
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
ProcedureResultSet RESULTSET;
BEGIN
ProcedureResultSet := (
SELECT 1);
RETURN TABLE(ProcedureResultSet);
END;
$$;
Recommendations¶
For additional support, please contact our support team at snowconvert-support@snowflake.com
SSC-EWI-0053¶
Severity¶
Low
Description¶
This error occurs when the conversion tool is unable to identify a variable’s data type. This typically happens when a variable has not been properly declared in the code.
Example code¶
Input Code (Oracle):¶
-- Additional Params: -t javascript
CREATE OR REPLACE PROCEDURE PROCEDURE_VARIABLES
AS
VARIABLE INTEGER;
BEGIN
VARIABLE := ANOTHER_VARIABLE;
END;
Output Code:¶
CREATE OR REPLACE PROCEDURE PROCEDURE_VARIABLES ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
var fixBind = function (arg) {
arg = arg instanceof Date ? formatDate(arg) : IS_NULL(arg) ? null : arg;
return arg;
};
var SQL = {
FOUND : false,
NOTFOUND : false,
ROWCOUNT : 0,
ISOPEN : false
};
var _RS, _ROWS, SQLERRM = "normal, successful completion", SQLCODE = 0;
var getObj = (_rs) => Object.assign(new Object(),_rs);
var getRow = (_rs) => (values = Object.values(_rs)) && (values = values.splice(-1 * _rs.getColumnCount())) && values;
var fetch = (_RS,_ROWS,fmode) => _RS.getRowCount() && _ROWS.next() && (fmode ? getObj : getRow)(_ROWS) || (fmode ? new Object() : []);
var EXEC = function (stmt,binds,opts) {
try {
binds = !(arguments[1] instanceof Array) && ((opts = arguments[1]) && []) || (binds || []);
opts = opts || new Object();
binds = binds ? binds.map(fixBind) : binds;
_RS = snowflake.createStatement({
sqlText : stmt,
binds : binds
});
_ROWS = _RS.execute();
if (opts.sql !== 0) {
var isSelect = stmt.toUpperCase().trimStart().startsWith("SELECT");
var affectedRows = isSelect ? _RS.getRowCount() : _RS.getNumRowsAffected();
SQL.FOUND = affectedRows != 0;
SQL.NOTFOUND = affectedRows == 0;
SQL.ROWCOUNT = affectedRows;
}
if (opts.row === 2) {
return _ROWS;
}
var INTO = function (opts) {
if (opts.vars == 1 && _RS.getColumnCount() == 1 && _ROWS.next()) {
return _ROWS.getColumnValue(1);
}
if (opts.rec instanceof Object && _ROWS.next()) {
var recordKeys = Object.keys(opts.rec);
Object.assign(opts.rec,Object.fromEntries(new Map(getRow(_ROWS).map((element,Index) => [recordKeys[Index],element]))))
return opts.rec;
}
return fetch(_RS,_ROWS,opts.row);
};
var BULK_INTO_COLLECTION = function (into) {
for(let i = 0;i < _RS.getRowCount();i++) {
FETCH_INTO_COLLECTIONS(into,fetch(_RS,_ROWS,opts.row));
}
return into;
};
if (_ROWS.getRowCount() > 0) {
return _ROWS.getRowCount() == 1 ? INTO(opts) : BULK_INTO_COLLECTION(opts);
}
} catch(error) {
RAISE(error.code,error.name,error.message)
}
};
var RAISE = function (code,name,message) {
message === undefined && ([name,message] = [message,name])
var error = new Error(message);
error.name = name
SQLERRM = `${(SQLCODE = (error.code = code))}: ${message}`
throw error;
};
var FETCH_INTO_COLLECTIONS = function (collections,fetchValues) {
for(let i = 0;i < collections.length;i++) {
collections[i].push(fetchValues[i]);
}
};
var IS_NULL = (arg) => !(arg || arg === 0);
// END REGION
let VARIABLE;
VARIABLE =
!!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT ANOTHER_VARIABLE MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
ANOTHER_VARIABLE;
$$;
Recommendations¶
Verify that all variables are properly declared in your code.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0094¶
Severity¶
Low
Description¶
Currently, Snow Scripting does not support label declarations. The system will add an Error, Warning, or Information (EWI) message and comment out the label.
Example Code¶
Input Code (Oracle):¶
CREATE OR REPLACE PROCEDURE Example ( grade NUMBER )
IS
BEGIN
<<CASE1>><<CASE2>>
CASE grade
WHEN 10 THEN NULL;
ELSE NULL;
END CASE CASE1;
END;
Output Code:¶
CREATE OR REPLACE PROCEDURE Example (grade NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
!!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<CASE1>><<CASE2>> ***/!!!
CASE :grade
WHEN 10 THEN
NULL;
ELSE NULL;
END CASE;
END;
$$;
Recommendations¶
No action is required from users.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0067¶
Severity¶
High
Description¶
This error occurs when a User-Defined Function (UDF) is detected within a query. Oracle UDFs, UDFs within packages, and certain SQL Server UDFs are converted to Snowflake Stored Procedures. However, Snowflake Stored Procedures cannot be called directly from within a query.
The original function is converted into a stored procedure to maintain the same functionality. A corresponding empty Snowflake User-Defined Function (UDF) is created to handle the function call.
This Early Warning Indicator (EWI) is directly connected to SSC-EWI-0068
Example Code¶
SQL Server:¶
CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS INT
AS
BEGIN
DECLARE @i int = 0, @p int;
Select @p = COUNT(*) FROM PURCHASING.VENDOR
WHILE (@p < 1000)
BEGIN
SET @i = @i + 1
SET @p = @p + @i
END
IF (@i = 6)
RETURN 1
RETURN @p
END;
GO
SELECT PURCHASING.FOO() AS RESULT;
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.FOO ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
I INT := 0;
P INT;
BEGIN
Select
COUNT(*)
INTO
:P
FROM
PURCHASING.VENDOR;
WHILE (:P < 1000) LOOP
I := :I + 1;
P := :P + :I;
END LOOP;
IF ((:I = 6)) THEN
RETURN 1;
END IF;
RETURN :P;
END;
$$;
SELECT
PURCHASING.FOO() !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! AS RESULT;
Oracle:¶
CREATE FUNCTION employee_function (param1 in NUMBER) RETURN NUMBER is
var1 employees.employee_ID%TYPE;
var2 employees.manager_ID%TYPE;
var3 employees.title%TYPE;
BEGIN
SELECT employee_ID, manager_ID, title
INTO var1, var2, var3
FROM employees
START WITH manager_ID = param1
CONNECT BY manager_ID = PRIOR employee_id;
RETURN var1;
EXCEPTION
WHEN no_data_found THEN RETURN param1;
END employee_function;
SELECT employee_function(2) FROM employees;
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE employee_function (param1 NUMBER(38, 18))
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "11/14/2024", "domain": "test" }}'
EXECUTE AS CALLER
AS
$$
DECLARE
var1 employees.employee_ID%TYPE;
var2 employees.manager_ID%TYPE;
var3 employees.title%TYPE;
BEGIN
SELECT employee_ID, manager_ID, title
INTO
:var1,
:var2,
:var3
FROM
employees
START WITH manager_ID = :param1
CONNECT BY
manager_ID = PRIOR employee_id;
RETURN :var1;
EXCEPTION
WHEN no_data_found THEN
RETURN :param1;
END;
$$;
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! employee_function(2) FROM
employees;
Recommendations¶
Your source code might require restructuring to align with Snowflake’s user-defined functions (UDFs). Learn more about UDFs in the Snowflake documentation.
For additional assistance, please contact our support team at snowconvert-support@snowflake.com
SSC-EWI-0036¶
Severity¶
Low
Description¶
This warning appears when one data type is converted to a different data type.
Example Code¶
Source Code:¶
CREATE TABLE SampleTable (
SampleYear INTERVAL YEAR(2),
SampleMonth INTERVAL MONTH(2)
);
Converted Code:¶
CREATE OR REPLACE TABLE SampleTable (
SampleYear VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL YEAR(2) DATA TYPE CONVERTED TO VARCHAR ***/!!!,
SampleMonth VARCHAR(21) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL MONTH(2) DATA TYPE CONVERTED TO VARCHAR ***/!!!
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "teradata", "convertedOn": "07/23/2024" }}'
;
Recommendations¶
For additional support, please contact our support team at snowconvert-support@snowflake.com
SSC-EWI-0026¶
The External Web Interface (EWI) is generated exclusively when translating Stored Procedures to JavaScript. However, this feature is no longer recommended, as Snowflake Scripting is now the preferred language for Stored Procedures.
Parts of the output code have been removed to make it easier to understand.
Severity¶
Low
Description¶
This warning appears when a query contains a variable with a qualified member, such as an Oracle record or a Teradata loop variable. You may need to add a type cast to ensure the variable works correctly, depending on its usage and value type.
Example Code¶
Input Code:¶
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:¶
-- 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¶
Before applying a cast to Date, Time, or Timestamp data types, verify if it’s actually needed. Snowflake often performs automatic (implicit) conversions for these data types.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0077¶
Severity¶
Low
Description¶
This warning appears when a query contains multiple Common Table Expressions (CTEs) with circular references. When CTEs reference each other in a way that creates a cycle, it becomes impossible to determine the correct execution order. As a result, the CTEs cannot be properly ordered and the query will remain unchanged from its original form.
Example Code¶
Input Code (Teradata):¶
WITH t1(c1) as (SELECT c1 FROM t2),
t2(c2) as (SELECT c2 FROM t3),
RECURSIVE t3(c3) as (SELECT c3, someOtherColumn FROM t1, t3)
SELECT * FROM t1;
Output Code:¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0077 - CYCLE FOUND BETWEEN CTE REFERENCE CALLS, CTES CANNOT BE ORDERED AND THE QUERY WILL REMAIN AS ORIGINAL ***/!!!
WITH RECURSIVE t1(c1) AS
(
SELECT
c1 FROM t2
),
t2(c2) AS
(
SELECT
c2 FROM t3
),
t3(c3) AS
(
SELECT
c3,
someOtherColumn FROM t1, t3
)
SELECT
* FROM t1;
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0084¶
Severity¶
High
Description¶
The XMLTABLE function is currently not supported in this version.
Example Code¶
Input Code (DB2):¶
SELECT
*
FROM
XMLTABLE(
'stringValue' PASSING BY REF passingExpr AS AliasName
) AS XMLTABLENAME
Output Code:¶
SELECT
*
FROM
!!!RESOLVE EWI!!! /*** SSC-EWI-0084 - XMLTABLE IS NOT SUPPORTED BY SNOWFLAKE ***/!!!
XMLTABLE(
'stringValue' PASSING BY REF passingExpr AS AliasName
) AS XMLTABLENAME
Recommendations¶
For guidance on XML transformations in Snowflake, please refer to this blog.
For additional assistance, contact our support team at snowconvert-support@snowflake.com
SSC-EWI-0012¶
Severity¶
High
Description¶
This error occurs when a statement in the source code is incomplete or improperly terminated.
Example Code¶
Input Code:¶
CREATE VOLATILE SET TABLE VOLATILETABLE
(
COL1 INTEGER,
COL2 INTEGER,
COL3 INTEGER
)
ON COMMIT PRESERVE ROWS;
UPDATE TABLE2 as T2
SET T2.COL1 + VOLATILETABLE.COL1
WHERE T2.COL2 = VOLATILETABLE.COL2
AND T2.COL3 = VOLATILETABLE.COL3
AND T2.COL4 = ( SELECT MAX(T3.COL1)
FROM
TABLE3 T3
WHERE T3.COL1 = T2.COL1);
Output Code:¶
--** SSC-FDM-TD0024 - SET TABLE FUNCTIONALITY NOT SUPPORTED. TABLE MIGHT HAVE DUPLICATE ROWS **
CREATE OR REPLACE TEMPORARY TABLE VOLATILETABLE
(
COL1 INTEGER,
COL2 INTEGER,
COL3 INTEGER
)
-- --** SSC-FDM-0008 - ON COMMIT NOT SUPPORTED **
--ON COMMIT PRESERVE ROWS
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "TABLE2", "TABLE3" **
UPDATE TABLE2 AS T2
SET
--** SSC-FDM-0025 - UNEXPECTED END OF STATEMENT. PLEASE CHECK THE LINE 9 OF ORIGINAL SOURCE CODE. **
T2.COL1 + VOLATILETABLE.COL1
FROM
VOLATILETABLE
WHERE T2.COL2 = _VOLATILETABLE.COL2
AND T2.COL3 = _VOLATILETABLE.COL3
AND T2.COL4 = (
SELECT
MAX(T3.COL1)
FROM
TABLE3 T3
WHERE T3.COL1 = T2.COL1);
Recommendation¶
Verify that your source code is complete and that all statements end properly.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0058¶
Severity¶
Medium
Description¶
This error occurs when you try to use an unsupported statement within a CREATE PROCEDURE command in Snowflake Scripting.
Example code¶
Input Code (Oracle):¶
CREATE OR REPLACE PROCEDURE PROC01
IS
number_variable INTEGER;
BEGIN
EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL' INTO number_variable;
END;
Output Code:¶
CREATE OR REPLACE PROCEDURE PROC01 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
number_variable INTEGER;
BEGIN
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL'
!!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'EXECUTE IMMEDIATE RETURNING CLAUSE' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
INTO number_variable;
END;
$$;
Recommendations¶
No action is required from end users.
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0108¶
Severity¶
High
Description¶
In Snowflake, some subquery patterns and elements cannot be executed due to lack of support. The Snowflake documentation on subqueries lists the following supported subquery types:
Scalar subqueries that don’t reference outer queries can be used anywhere a value expression is allowed.
Scalar subqueries that reference outer queries can be used in WHERE clauses.
WHERE clauses support EXISTS, ANY / ALL, and IN subqueries. These subqueries can reference outer queries (correlated) or not (uncorrelated).
Please note that there may be other valid types of subqueries not included in this list.
To prevent errors, SnowConvert identifies common subquery patterns that can cause compilation issues in Snowflake. When such a pattern is detected, an Early Warning Indicator (EWI) is generated to alert users about potential compilation errors.
Example Code¶
Input Code:¶
CREATE TABLE tableA
(
col1 INTEGER,
col2 VARCHAR(20)
);
CREATE TABLE tableB
(
col3 INTEGER,
col4 VARCHAR(20)
);
INSERT INTO tableA VALUES (50, 'Hey');
INSERT INTO tableB VALUES (50, 'Hey');
INSERT INTO tableB VALUES (50, 'Example');
INSERT INTO tableB VALUES (10, 'Bye');
-- Snowflake only allows the usage of FETCH in subqueries that are uncorrelated scalar, this subquery execution will fail
SELECT col2
FROM tableA
WHERE col2 = (SELECT col4 FROM tableB WHERE col3 = col1 FETCH FIRST ROW ONLY);
-- This subquery is uncorrelated scalar so FETCH is valid to use
SELECT col2
FROM tableA
WHERE col2 = (SELECT col4 FROM tableB FETCH FIRST ROW ONLY);
Output Code:¶
CREATE OR REPLACE TABLE tableA
(
col1 INTEGER,
col2 VARCHAR(20)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "12/05/2024", "domain": "test" }}'
;
CREATE OR REPLACE TABLE tableB
(
col3 INTEGER,
col4 VARCHAR(20)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "12/05/2024", "domain": "test" }}'
;
INSERT INTO tableA
VALUES (50, 'Hey');
INSERT INTO tableB
VALUES (50, 'Hey');
INSERT INTO tableB
VALUES (50, 'Example');
INSERT INTO tableB
VALUES (10, 'Bye');
-- Snowflake only allows the usage of FETCH in subqueries that are uncorrelated scalar, this subquery execution will fail
SELECT col2
FROM
tableA
WHERE col2 =
--** SSC-FDM-0002 - CORRELATED SUBQUERIES MAY HAVE SOME FUNCTIONAL DIFFERENCES. **
!!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!! (SELECT
ANY_VALUE( col4) FROM
tableB
WHERE col3 = col1
FETCH FIRST 1 ROW ONLY);
-- This subquery is uncorrelated scalar so FETCH is valid to use
SELECT col2
FROM
tableA
WHERE col2 = (SELECT col4 FROM
tableB
FETCH FIRST 1 ROW ONLY);
Recommendations¶
Verify if the subquery runs successfully in Snowflake. If it does, you can ignore this EWI message.
Review the “Complex Patterns” section in your assessment report. This section lists patterns that typically cause subquery issues and where they occur. Use this information to understand why certain subqueries were flagged as invalid.
For additional assistance, contact us at snowconvert-support@snowflake.com
SSC-EWI-0068¶
This Early Warning Indicator (EWI) has been deprecated. Please refer to the SSC-FDM-0029 documentation for current information.
Snowflake’s user-defined functions have different capabilities compared to Oracle or SQL Server. When migrating, these functions are converted into Snowflake stored procedures to maintain the same functionality. This conversion will change how you use them in your queries.
Example Code¶
SQL Server:¶
CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS INT
AS
BEGIN
DECLARE @i int = 0, @p int;
Select @p = COUNT(*) FROM PURCHASING.VENDOR
WHILE (@p < 1000)
BEGIN
SET @i = @i + 1
SET @p = @p + @i
END
IF (@i = 6)
RETURN 1
RETURN @p
END;
!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE PURCHASING.FOO ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
I INT := 0;
P INT;
BEGIN
Select
COUNT(*)
INTO
:P
FROM
PURCHASING.VENDOR;
WHILE (:P < 1000) LOOP
I := :I + 1;
P := :P + :I;
END LOOP;
IF ((:I = 6)) THEN
RETURN 1;
END IF;
RETURN :P;
END;
$$;
Oracle:¶
CREATE OR REPLACE FUNCTION FUN1(PAR1 VARCHAR)
RETURN VARCHAR
IS
VAR1 VARCHAR(20);
VAR2 VARCHAR(20);
BEGIN
SELECT COL1 INTO VAR1 FROM TABLE1 where col1 = 1;
VAR2 := PAR1 || VAR1;
RETURN VAR2;
END;
!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FUN1(PAR1 VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
VAR1 VARCHAR(20);
VAR2 VARCHAR(20);
BEGIN
SELECT COL1 INTO
:VAR1
FROM
TABLE1
where col1 = 1;
VAR2 := NVL(:PAR1 :: STRING, '') || NVL(:VAR1 :: STRING, '');
RETURN :VAR2;
END;
$$;
Recommendations¶
Break down complex queries into smaller, logically equivalent parts.
Restructure the source code to align with Snowflake’s user-defined functions (UDF) approach.
For additional assistance, contact us at snowconvert-support@snowflake.com
SSC-EWI-0028¶
Severity¶
Medium
Description¶
This error occurs when you attempt to use a data type that is not compatible with Snowflake.
Example¶
Input Code (Oracle):¶
CREATE TABLE MYTABLE
(
COL1 SYS.ANYDATASET
);
Output Code:¶
CREATE OR REPLACE TABLE MYTABLE
(
!!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
COL1 SYS.ANYDATASET
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
Recommendations¶
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0008¶
Severity¶
Medium
Description¶
This warning appears when a collate clause is used as a column option. While Snowflake supports collation, the behavior might differ from the source database. To ensure compatibility, please review the supported collation specifiers in the collate documentation.
Example Code¶
Input Code:¶
CREATE TABLE TABLE01 (
col1 text COLLATE "C"
);
Output Code:¶
CREATE TABLE TABLE01 (
col1 text
!!!RESOLVE EWI!!! /*** SSC-EWI-0008 - COLLATE CLAUSE MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!! COLLATE "C"
);
Recommendations¶
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0049¶
The EWI
feature has been deprecated. For current information, please see the SSC-FDM-0023 documentation.
Severity¶
Medium
Description¶
SnowConvert converts Global Temporary tables into standard Create Table statements. Please note that any references to these transformed tables may not function as originally intended.
Code example¶
Input¶
create global temporary table t1
(col1 varchar);
create view view1 as
select col1 from t1;
Output¶
--** SSC-FDM-0009 - GLOBAL TEMPORARY TABLE FUNCTIONALITY NOT SUPPORTED. **
CREATE OR REPLACE TABLE t1
(col1 varchar)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
CREATE OR REPLACE VIEW view1
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
select col1 from
!!!RESOLVE EWI!!! /*** SSC-EWI-0049 - A Global Temporary Table is being referenced ***/!!!
t1;
Recommendations¶
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0109¶
Severity¶
Medium
Description¶
The message appears when the Alter Table syntax in your code is not compatible with Snowflake’s syntax requirements.
Example Code:¶
Input Code:¶
ALTER TABLE SOMENAME DEFAULT COLLATION SOMENAME;
ALTER TABLE SOMENAME ROW ARCHIVAL;
ALTER TABLE SOMENAME MODIFY CLUSTERING;
ALTER TABLE SOMENAME DROP CLUSTERING;
ALTER TABLE SOMENAME SHRINK SPACE COMPACT CASCADE;
Output Code:¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0109 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE ***/!!!
ALTER TABLE SOMENAME
DEFAULT COLLATION SOMENAME;
!!!RESOLVE EWI!!! /*** SSC-EWI-0109 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE ***/!!!
ALTER TABLE SOMENAME
ROW ARCHIVAL;
!!!RESOLVE EWI!!! /*** SSC-EWI-0109 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE ***/!!!
ALTER TABLE SOMENAME
MODIFY CLUSTERING;
!!!RESOLVE EWI!!! /*** SSC-EWI-0109 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE ***/!!!
ALTER TABLE SOMENAME
DROP CLUSTERING;
!!!RESOLVE EWI!!! /*** SSC-EWI-0109 - ALTER TABLE SYNTAX NOT APPLICABLE IN SNOWFLAKE ***/!!!
ALTER TABLE SOMENAME
SHRINK SPACE COMPACT CASCADE;
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0009¶
Severity¶
Low
Description¶
Currently, Snowflake only supports basic regular expressions that follow the POSIX Basic Regular Expression syntax. Advanced regular expression features are not available.
This warning appears whenever a function call to REGEX_SUBSTR
, REGEX_REPLACE
, or REGEX_INSTR
is converted to Snowflake. It alerts users that certain regular expression features may not be supported in Snowflake. Important unsupported features include lookahead, lookbehind, and non-capturing groups.
Example Code¶
Teradata:¶
SELECT REGEXP_SUBSTR('qaqequ','q(?=u)', 1, 1);
Snowflake Scripting:¶
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0020 - REGEXP_SUBSTR FUNCTION ONLY SUPPORTS POSIX REGULAR EXPRESSIONS ***/!!!
REGEXP_SUBSTR('qaqequ','q(?=u)', 1, 1);
Recommendations¶
Review each regular expression pattern to identify if manual changes are required. For more details about Snowflake’s regex capabilities and alternative options, see here.
For additional assistance, contact us at snowconvert-support@snowflake.com
SSC-EWI-0031¶
Parts of the output code have been removed to make the example clearer and easier to follow.
Severity ¶
Medium
Description ¶
This warning indicates that a specific built-in function from Teradata, Oracle, or SQL Server cannot be converted because it is not supported in the target platform.
Example Code ¶
Input Code (Oracle):¶
SELECT VALUE(ST) FROM SampleTable ST;
Output Code:¶
// SnowConvert Helpers Code section is omitted.
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-0031 - VALUE FUNCTION NOT SUPPORTED ***/!!!
VALUE(ST) FROM
SampleTable ST;
Recommendations ¶
To check how specific functions are transformed, please consult these reference guides:
For additional assistance, contact our support team at snowconvert-support@snowflake.com
SSC-EWI-0005¶
Severity¶
Critical
Description¶
This error occurs when the source code cannot be converted due to an unexpected transformation error, preventing the generation of the output code file.
Recommendations¶
Review the error log file to find detailed information about the problem.
For additional assistance, please contact our support team at snowconvert-support@snowflake.com
SSC-EWI-0054¶
Parts of the output code have been removed to make the example clearer and easier to follow.
Severity¶
Low
Description¶
This error occurs when a correlated subquery is detected inside an OR condition within an OUTER JOIN (Left, Right, or Full) statement. This situation can lead to either inconsistent query results or trigger an error message.
SQL compilation error: The query contains a subquery type that is not supported and cannot be processed.
Snowflake has documented these subquery limitations in their official documentation. Additional details about these limitations can be found in the Snowflake community forums.
Example code¶
Input Code (Teradata):¶
SELECT a.Column1, b.Column2
FROM
TableA a
LEFT JOIN TableB b ON (a.Column1 = b.Column1)
AND (
a.Column2 = b.Column2
OR EXISTS(
SELECT * FROM Table3 c
WHERE c.Column1 = a.Column1
)
);
Output Code:¶
// SnowConvert Helpers Code section is omitted.
SELECT
a.Column1,
b.Column2
FROM
TableA a
LEFT JOIN
TableB b ON (a.Column1 = b.Column1)
AND (
a.Column2 = b.Column2
OR EXISTS
!!!RESOLVE EWI!!! /*** SSC-EWI-0054 - CORRELATED SUBQUERIES WITHIN AN OR EXPRESSION OF AN OUTER JOIN COULD CAUSE COMPILATION ERRORS ***/!!!(
SELECT
* FROM
Table3 c
WHERE c.Column1 = a.Column1
)
);
Recommendations¶
Check that the converted code compiles without errors.
Ensure the converted code functions exactly like the original code.
For additional assistance, contact us at snowconvert-support@snowflake.com
SSC-EWI-0015¶
Parts of the code have been omitted to keep the example clear and concise.
Severity¶
Medium
Description¶
This section explains potential problems that may occur when using PIVOT and UNPIVOT clauses. The table below shows scenarios that are not supported.
PIVOT |
UNPIVOT |
ORACLE |
TERADATA |
|
---|---|---|---|---|
MULTIPLE COLUMN |
X |
X |
X |
X |
RENAME COLUMN |
X |
X |
X |
X |
MULTIPLE FUNCTION |
X |
X |
X |
|
WITH CLAUSE |
X |
X |
||
XML OUTPUT FORMAT |
X |
X |
||
IN CLAUSE SUBQUERY |
X |
X |
X |
|
IN CLAUSE ANY SEQUENCE |
X |
X |
||
INCLUDE/EXCLUDE NULLS |
X |
X |
X |
MULTIPLE COLUMN¶
PIVOT and UNPIVOT operations can only be performed on a single column at a time.
Example Code¶
Input Code:¶
SELECT * FROM star1p UNPIVOT ((sales,cogs) FOR yr_qtr
IN ((Q101Sales, Q101Cogs) AS 'Q101A',
(Q201Sales, Q201Cogs) AS 'Q201A',
(Q301Sales, Q301Cogs) AS 'Q301A')) AS Tmp;
Output Code:¶
// SnowConvert Helpers Code section is omitted.
SELECT * FROM
star1p
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT MULTIPLE COLUMN NOT SUPPORTED ***/!!!
UNPIVOT ((sales,cogs) FOR yr_qtr
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT MULTIPLE COLUMN NOT SUPPORTED ***/!!!
IN ((Q101Sales, Q101Cogs) AS 'Q101A',
(Q201Sales, Q201Cogs) AS 'Q201A',
(Q301Sales, Q301Cogs) AS 'Q301A')) AS Tmp;
RENAME COLUMN¶
When using UNPIVOT clauses in Snowflake, column aliases are not supported. To ensure compatibility, SnowConvert automatically removes any aliases from functions or columns to create a valid query. The tool verifies that removing these aliases does not affect the original query’s functionality.
PIVOT operations in SnowConvert for Teradata require two specific conditions for column aliases to work properly:
Every expression within the IN clause must have an assigned alias
SnowConvert must know which columns will be created. This information can come from either:
A provided table definition
A subquery that explicitly lists the columns as input to the clause
Example Code¶
Input Code:¶
CREATE TABLE star1(
country VARCHAR(20),
state VARCHAR(10),
yr INTEGER,
qtr VARCHAR(3),
sales INTEGER,
cogs INTEGER
);
--SAMPLE 1
SELECT * FROM db1.star1p UNPIVOT (column1 FOR for_column
IN (col1 AS 'as_col1', col2 AS 'as_col2')) Tmp;
--SAMPLE 2
SELECT *
FROM star1 PIVOT (
SUM(sales) as ss1 FOR qtr
IN ('Q1' AS Quarter1,
'Q2' AS Quarter2,
'Q3' AS Quarter3)
)Tmp;
--SAMPLE 3
SELECT
*
FROM (
SELECT
country,
state,
yr,
qtr,
sales,
cogs
FROM star1 ) A
PIVOT (
SUM(sales) as ss1 FOR qtr
IN ('Q1' AS Quarter1,
'Q2' AS Quarter2,
'Q3' AS Quarter3)
)Tmp;
Output Code:¶
CREATE OR REPLACE TABLE star1 (
country VARCHAR(20),
state VARCHAR(10),
yr INTEGER,
qtr VARCHAR(3),
sales INTEGER,
cogs INTEGER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "teradata", "convertedOn": "08/14/2024" }}'
;
--SAMPLE 1
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "db1.star1p" **
SELECT
* FROM db1.star1p UNPIVOT (column1 FOR for_column
IN (col1 AS 'as_col1', col2 AS 'as_col2')) Tmp !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PivotUnpivotTableReference' NODE ***/!!!;
--SAMPLE 2
SELECT
*
FROM
star1 PIVOT (
SUM(sales) FOR qtr IN ('Q1',
'Q2',
'Q3')) Tmp (
country,
state,
yr,
cogs,
Quarter1_ss1,
Quarter2_ss1,
Quarter3_ss1
);
--SAMPLE 3
SELECT
*
FROM (
SELECT
country,
state,
yr,
qtr,
sales,
cogs
FROM
star1
) A
PIVOT (
SUM(sales) FOR qtr IN ('Q1',
'Q2',
'Q3')) Tmp (
country,
state,
yr,
cogs,
Quarter1_ss1,
Quarter2_ss1,
Quarter3_ss1
);
MULTIPLE FUNCTION¶
PIVOT clauses do not support multiple functions. However, you can often rewrite these queries using CASE statements. For more details and examples, refer to the Teradata documentation: https://docs.teradata.com/r/756LNiPSFdY~4JcCCcR5Cw/L0kKSOrOeu_68mcW3o8ilw
Example Code¶
Input Code:¶
SELECT *
FROM STAR1 PIVOT(SUM(COL1), SUM(COL2) FOR YR IN ('Y1', 'Y2', 'Y3'))TMP;
Output Code:¶
// SnowConvert Helpers Code section is omitted.
SELECT
*
FROM
STAR1
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT MULTIPLE FUNCTION NOT SUPPORTED ***/!!!
PIVOT(SUM(COL1), SUM(COL2) FOR YR IN ('Y1', 'Y2', 'Y3'))TMP;
WITH CLAUSE¶
The PIVOT clause in Teradata includes an optional WITH statement, which is not supported in Snowflake’s PIVOT implementation.
Example Code¶
Input Code:¶
SELECT *
FROM STAR1 PIVOT(SUM(COL1) FOR YR IN ('Y1', 'Y2', 'Y3') WITH SUM(*) AS withalias)TMP;
Output Code:¶
// SnowConvert Helpers Code section is omitted.
SELECT
*
FROM
STAR1 PIVOT(SUM(COL1) FOR YR IN ('Y1', 'Y2', 'Y3')
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT WITH CLAUSE NOT SUPPORTED ***/!!!
WITH SUM(*) AS withalias)TMP;
XML OUTPUT FORMAT¶
XML output from PIVOT operations is not supported in Snowflake.
Example Code¶
Input Code:¶
SELECT * FROM (SELECT product_code, quantity FROM pivot_test)
PIVOT XML (SUM(quantity)
FOR (product_code) IN ('A','B','C'));
Output Code:¶
// SnowConvert Helpers Code section is omitted.
SELECT * FROM
(
SELECT product_code, quantity FROM
pivot_test)
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT XML OUTPUT FORMAT NOT SUPPORTED ***/!!!
PIVOT (SUM(quantity) FOR product_code IN ( 'A', 'B', 'C'));
IN CLAUSE SUBQUERY¶
Snowpark Migration Accelerator does not support using subqueries within IN clauses.
Example Code¶
Input Code:¶
SELECT * FROM s1 PIVOT(SUM(COL1) FOR FORCOL IN (SELECT SELCOL FROM S2))DT;
Output Code:¶
// SnowConvert Helpers Code section is omitted.
SELECT * FROM
s1 PIVOT (SUM(COL1) FOR FORCOL
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT IN CLAUSE SUBQUERY NOT SUPPORTED ***/!!! IN (SELECT SELCOL FROM
S2));
IN CLAUSE ANY SEQUENCE¶
This error occurs when the ANY keyword is used within an IN clause. Currently, this combination is not supported in the system.
Example Code¶
Input Code:¶
SELECT * FROM (SELECT product_code, quantity FROM pivot_test)
PIVOT (SUM(quantity)
FOR product_code IN (ANY, ANY, ANY));
Output Code:¶
// SnowConvert Helpers Code section is omitted.
SELECT * FROM (SELECT product_code, quantity FROM
pivot_test)
PIVOT (SUM(quantity)
FOR product_code
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT IN CLAUSE ANY SEQUENCE NOT SUPPORTED ***/!!!
IN (ANY, ANY, ANY));
INCLUDE/EXCLUDE NULLS¶
UNPIVOT clauses in Snowflake do not support the INCLUDE NULLS or EXCLUDE NULLS options.
Example Code¶
Input Code:¶
SELECT * FROM db1.star1p UNPIVOT INCLUDE NULLS (column1 FOR for_column IN (col1, col2)) Tmp;
Output Code:¶
// SnowConvert Helpers Code section is omitted.
SELECT * FROM
db1.star1p
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT INCLUDE NULLS NOT SUPPORTED ***/!!!
UNPIVOT ( column1 FOR for_column IN (
col1,
col2)) Tmp;
Recommendations¶
If possible, rewrite the query. If not, you don’t need to take any further action.
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0021¶
Severity¶
Medium
Description¶
This error occurs when your source code contains a node or statement that is not compatible with Snowflake’s functionality.
Example Code¶
Input Code:¶
WITH my_av ANALYTIC VIEW AS
(USING sales_av HIERARCHIES(time_hier) ADD MEASURES(lag_sales AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 ))))
SELECT aValue from my_av;
Output Code:¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - SubavFactoring NOT SUPPORTED IN SNOWFLAKE ***/!!!
WITH my_av ANALYTIC VIEW AS
(USING sales_av HIERARCHIES(time_hier) ADD MEASURES(lag_sales AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 ))))
SELECT aValue from my_av;
Recommendations¶
This error occurs when there is no equivalent Snowflake functionality for the source code being converted.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0110¶
Severity¶
Low
Description¶
When SnowConvert encounters missing dependencies, it adds an EWI (Error, Warning, Information) message to indicate that the code transformation cannot be completed. While SnowConvert normally creates new code by analyzing the original source code’s structure through abstract syntax trees, it cannot complete the transformation if essential dependencies are missing from its semantic model.
Example code¶
Input Code :¶
ALTER TABLE MissingTable ADD
CONSTRAINT constraint1 DEFAULT (suser_name()) FOR col1;
Output Code:¶
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MissingTable" **
!!!RESOLVE EWI!!! /*** SSC-EWI-0110 - TRANSFORMATION NOT PERFORMED DUE TO MISSING DEPENDENCIES ***/!!!
ALTER TABLE MissingTable
ADD
CONSTRAINT constraint1 DEFAULT (CURRENT_USER()) FOR col1;
Recommendations¶
Add any required dependencies that are not present in your code.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0001¶
Parts of the code have been removed to make the example clearer and easier to understand.
Severity¶
Critical
Description¶
This error appears when the system cannot properly read and process the source code during conversion. This can happen for two reasons:
The source code contains syntax errors
The converter does not yet recognize certain code statements or syntax
Example Code¶
The following examples show various parsing error scenarios with incorrect syntax in the input. Each scenario displays a different error message that can help you identify and fix the issue. For more details, refer to the “Message Contents” section below.
Input Code:¶
CRATE;
CREATE TABLE someTable(col1 INTEGER, !);
CREATE TABRE badTable(col1 INTEGER);
CREATE PROCEDURE proc1()
BEGIN
CREATE TABLE badEmbeddedTable(col1 INTEGER);
END;
Output Code:¶
-- ** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '1' COLUMN '1' OF THE SOURCE CODE STARTING AT 'CRATE'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'CRATE' ON LINE '1' COLUMN '1'. **
--CRATE
;
CREATE OR REPLACE TABLE someTable (
col1 INTEGER
-- ,
-- ** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '3' COLUMN '37' OF THE SOURCE CODE STARTING AT '!'. EXPECTED 'Column Definition' GRAMMAR. LAST MATCHING TOKEN WAS ',' ON LINE '3' COLUMN '35'. FAILED TOKEN WAS '!' ON LINE '3' COLUMN '37'. **
-- !
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "teradata", "convertedOn": "07/04/2024" }}'
;
-- ** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '5' COLUMN '1' OF THE SOURCE CODE STARTING AT 'CREATE'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'CREATE' ON LINE '5' COLUMN '1'. **
--CREATE TABRE badTable(col1 INTEGER)
;
CREATE OR REPLACE PROCEDURE proc1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "teradata", "convertedOn": "07/04/2024" }}'
EXECUTE AS CALLER
AS
$$
BEGIN
CREATE OR REPLACE TABLE badEmbeddedTable (
col1 INTEGER);
END;
$$;
Message Contents¶
Starting Position: Shows where the unrecognized code begins, including line number, column number, and the actual text. Any unrecognized code from this point will be commented out until the parser finds valid code.
Expected Grammar: Indicates what type of code structure the parser was looking for. Compare this with the commented code to verify if they match.
Last Valid Token (OPTIONAL): Shows the last piece of code that was successfully recognized. If present, review the code that follows to ensure it’s syntactically correct.
Failed Token (OPTIONAL): Only appears when “Last Valid Token” is present. Identifies the exact point where the code became invalid or unrecognizable. Verify if this code element is allowed in this location.
Discontinued and no longer supported Message Contents¶
The following items are no longer in use and are maintained only for historical reference.
Recovery Code (DEPRECATED): This error code helps identify how the parser’s recovery mechanism was triggered. You can include this code when requesting support for parser upgrades to assist with troubleshooting.
Recommendations¶
Verify that your source code follows the correct syntax rules.
Use error messages to identify and resolve any issues.
If you encounter unsupported syntax, modify it manually to use supported syntax patterns.
For additional assistance, contact us at snowconvert-support@snowflake.com
SSC-EWI-0035¶
Parts of the code have been removed to make the example clearer and easier to understand.
Severity¶
Low
Description¶
The CHECK constraint is not supported in Snowflake, but this limitation does not impact the functionality of your database.
Example Code¶
Input Code Oracle :¶
CREATE TABLE "Schema"."BaseTable"(
"COLUMN1" VARCHAR2(255),
CHECK ( COLUMN1 IS NOT NULL )
);
Output Code:¶
CREATE OR REPLACE TABLE "Schema"."BaseTable" (
"COLUMN1" VARCHAR(255),
!!!RESOLVE EWI!!! /*** SSC-EWI-0035 - CHECK STATEMENT NOT SUPPORTED ***/!!!
CHECK ( COLUMN1 IS NOT NULL )
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
Input Code Teradata:¶
CREATE TABLE TABLE1,
NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
COL0 BYTEINT,
CONSTRAINT constraint_name CHECK (COL1 < COL2)
)
Output Code:¶
CREATE OR REPLACE TABLE TABLE1
(
COL0 BYTEINT,
!!!RESOLVE EWI!!! /*** SSC-EWI-0035 - CHECK STATEMENT NOT SUPPORTED ***/!!!
CONSTRAINT constraint_name CHECK (COL1 < COL2)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;
Input Code SqlServer¶
ALTER TABLE table_name2
ADD column_name VARCHAR(255)
CONSTRAINT constraint_name
CHECK NOT FOR REPLICATION (column_name > 1);
Output Code:¶
ALTER TABLE IF EXISTS table_name2
ADD column_name VARCHAR(255)
!!!RESOLVE EWI!!! /*** SSC-EWI-0035 - CHECK STATEMENT NOT SUPPORTED ***/!!!
CONSTRAINT constraint_name
CHECK NOT FOR REPLICATION (column_name > 1);
Recommendations¶
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0064¶
Severity¶
High
Description¶
This error occurs when a User-Defined Type (UDT) is referenced in a Data Manipulation Language (DML) statement, but the UDT was not previously defined in the database. This commonly happens when a table column is declared with a custom type that doesn’t exist in the system.
This error should not be confused with SSC-FDM-0015, which occurs when the object is referenced in a DDL query.
Example Code¶
Input Code (Oracle):¶
--Type was never defined
--CREATE TYPE type1;
CREATE TABLE table1
(
--the type will be unresolved
column1 type1
);
SELECT
column1
FROM table1;
Output Code:¶
--Type was never defined
--CREATE TYPE type1;
!!!RESOLVE EWI!!! /*** SSC-EWI-0050 - MISSING DEPENDENT OBJECT "type1" ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0063 - 'PUBLIC.table1_view' ADDED BECAUSE 'table1' USED A CUSTOM TYPE ***/!!!
CREATE OR REPLACE TABLE table1
(
--the type will be unresolved
column1 VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0064 - REFERENCED CUSTOM TYPE 'type1' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/!!! /*** SSC-FDM-0015 - DATA TYPE 'type1' NOT RECOGNIZED ***/
);
CREATE OR REPLACE VIEW PUBLIC.table1_view
AS
SELECT
column1
FROM
table1;
SELECT
column1 !!!RESOLVE EWI!!! /*** SSC-EWI-0064 - REFERENCED CUSTOM TYPE 'type1' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/!!!
FROM
table1;
Recommendations¶
Check if the data type mentioned in your code has been properly defined in the source code.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0025¶
The External Web Interface (EWI) is generated exclusively when translating Stored Procedures to JavaScript. However, this feature is no longer recommended, as Snowflake Scripting is now the preferred language for Stored Procedures.
Parts of the output code have been removed to make the example clearer and easier to follow.
Severity¶
Low
Description¶
Queries containing time variables may need to be modified when binding these variables.
Example Code¶
Input Code:¶
-- Additional Params: -t javascript
CREATE PROCEDURE P_1025()
BEGIN
DECLARE LN_EMP_KEY_NO_PARAM NUMERIC DEFAULT -1;
DECLARE FLOATVARNAME FLOAT DEFAULT 12.1;
DECLARE hErrorMsg CHARACTER(30) DEFAULT 'NO ERROR';
DECLARE CurrTs TIME DEFAULT CURRENT_TIME;
DECLARE CurrTs2 TIME DEFAULT CURRENT_TIMESTAMP;
END;
Output Code:¶
CREATE OR REPLACE PROCEDURE P_1025 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
var LN_EMP_KEY_NO_PARAM = -1;
var FLOATVARNAME = 12.1;
var HERRORMSG = `NO ERROR`;
var CURRTS = new Date() /*** SSC-EWI-0025 - BINDING TIME VARIABLE MIGHT REQUIRE CHANGE IN QUERY. ***/;
var CURRTS2 = new Date();
$$;
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0011¶
Severity¶
High
Description¶
This error occurs when a statement in the source code ends unexpectedly and the system cannot process it properly.
Recommendations¶
Verify that your source code is complete and that all statements are properly terminated.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0040¶
Parts of the output code have been removed to make the example clearer and easier to understand.
Severity¶
Low
Description¶
This warning appears when you use a SQL statement that is not supported in Snowflake.
Example Code¶
The following example shows a SQL Server SELECT query that uses the PERCENT
clause. This feature is not available in Snowflake.
Input Code (SQL Server):¶
SELECT TOP 1 PERCENT * FROM SampleTable;
Source Code:¶
// SnowConvert Helpers Code section is omitted.
SELECT
TOP 1 !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
*
FROM
SampleTable;
Recommendations¶
Evaluate if the original statement’s functionality is necessary for your Snowflake implementation.
For additional assistance, contact us at snowconvert-support@snowflake.com
SSC-EWI-0034¶
Parts of the output code have been removed to make the example clearer and easier to follow.
Severity¶
Low
Description¶
This warning appears when you attempt to use a CAST function with a column that has had its format removed.
Example Code¶
Input Code (Teradata):¶
CREATE VIEW SampleView AS
SELECT
DAY_DATE(FORMAT 'MMM-YYYY') + 1
FROM
SampleTable;
Output Code:¶
// SnowConvert Helpers Code section is omitted.
CREATE OR REPLACE VIEW SampleView
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
AS
SELECT
DAY_DATE !!!RESOLVE EWI!!! /*** SSC-EWI-0034 - FORMAT 'MMM-YYYY' REMOVED. ***/!!! + 1
FROM
SampleTable;
Recommendations¶
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0086¶
The EWI
feature has been deprecated. For current information, please see the SSC-FDM-0030 documentation.
Severity¶
Low
Description¶
The identifier contains characters that are not supported in the output language. These characters have been replaced with their corresponding UTF-8 codes.
Example Code¶
Input Code (Oracle):¶
CREATE PROCEDURE PROC1
AS
"VAR0" INT;
"VAR`/1ͷ" VARCHAR(20);
"o*/o" FLOAT;
" . " INT;
". ." INT;
"123Name" INT;
"return" INT;
yield INT;
ident#10 INT;
BEGIN
NULL;
END;
Output Code:¶
CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
"VAR0" INT;
!!!RESOLVE EWI!!! /*** SSC-EWI-0086 - IDENTIFIER '"VAR`/1ͷ"' HAS INVALID CHARACTERS. CHARACTERS WERE REPLACED WITH THEIR UTF-8 CODES ***/!!!
VAR_u60_u2F1ͷ VARCHAR(20);
!!!RESOLVE EWI!!! /*** SSC-EWI-0086 - IDENTIFIER '"o*/o"' HAS INVALID CHARACTERS. CHARACTERS WERE REPLACED WITH THEIR UTF-8 CODES ***/!!!
o_u2A_u2Fo FLOAT;
!!!RESOLVE EWI!!! /*** SSC-EWI-0086 - IDENTIFIER '" . "' HAS INVALID CHARACTERS. CHARACTERS WERE REPLACED WITH THEIR UTF-8 CODES ***/!!!
_u20_u2E_u20 INT;
!!!RESOLVE EWI!!! /*** SSC-EWI-0086 - IDENTIFIER '". ."' HAS INVALID CHARACTERS. CHARACTERS WERE REPLACED WITH THEIR UTF-8 CODES ***/!!!
_u2E_u20_u2E INT;
"123Name" INT;
"return" INT;
yield INT;
IDENT_HASHTAG_10 INT;
BEGIN
NULL;
END;
$$;
Recommendations¶
No action is required from end users.
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0010¶
Severity¶
Critical
Description¶
This error occurs when the system cannot find a corresponding transformation rule for a specific procedure statement.
Recommendations¶
Verify that your procedure statement is formatted correctly.
For additional assistance, please contact our support team at snowconvert-support@snowflake.com
SSC-EWI-0041¶
This EWI
has been deprecated. For more information, please see SSC-OOS-0001.
Description¶
This error occurs when the source code file uses a character encoding format that the tool cannot recognize. Character encoding is a method of converting text characters into numerical values that computers can process. When the conversion tool encounters characters it cannot interpret, it generates this error.
Recommendations¶
Ensure all files in the input folder use the same character encoding to prevent encoding-related errors.
Choose the correct encoding by either:
Adjusting the conversion settings, or
Using the –encoding parameter with the CLI
To identify the correct encoding, you can:
Use online tools like Free Online Formater
On Linux or OS, run the command
file -i *
For additional assistance, contact us at snowconvert-support@snowflake.com
SSC-EWI-0101¶
Severity¶
Low
Description¶
Snowflake does not support transaction label names since nested transactions are not allowed, making it unnecessary to identify specific transactions in COMMIT or ROLLBACK statements.
Example code¶
Input Code (SQL Server):¶
CREATE PROCEDURE TestTransaction
AS
BEGIN
DROP TABLE IF EXISTS NEWTABLE;
CREATE TABLE NEWTABLE(COL1 INT, COL2 VARCHAR);
BEGIN TRANSACTION LabelA;
INSERT INTO NEWTABLE VALUES (1, 'MICHAEL');
INSERT INTO NEWTABLE VALUES(2, 'JACKSON');
COMMIT TRANSACTION LabelA;
END
Output Code:¶
CREATE OR REPLACE PROCEDURE TestTransaction ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
DROP TABLE IF EXISTS NEWTABLE;
CREATE OR REPLACE TABLE NEWTABLE (
COL1 INT,
COL2 VARCHAR
);
BEGIN TRANSACTION
!!!RESOLVE EWI!!! /*** SSC-EWI-0101 - COMMENTED OUT TRANSACTION LABEL NAME BECAUSE IS NOT APPLICABLE IN SNOWFLAKE ***/!!!
LabelA;
INSERT INTO NEWTABLE VALUES (1, 'MICHAEL');
INSERT INTO NEWTABLE VALUES(2, 'JACKSON');
COMMIT;
END;
$$;
Recommendations¶
No action is required from users.
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0030¶
Parts of the output code have been removed to make the example clearer and easier to follow.
Severity¶
Moderate
Description¶
This error indicates that the statement contains dynamic SQL, which is SQL code constructed as text using string manipulation functions provided by the database engine. Different source languages have their own methods for executing dynamic SQL statements.
This pattern is complex because it involves SQL code that is generated and executed during runtime, making it challenging to track and debug issues. While SnowConvert, being a static code analyzer, cannot fully analyze such dynamic SQL, this error message helps identify potential problems.
Code Example¶
Teradata¶
REPLACE PROCEDURE teradata_dynamic_sql()
BEGIN
DECLARE str_sql VARCHAR(20);
SET str_sql = 'UPDATE TABLE
SET COLA = 0,
COLB = ''test''';
EXECUTE IMMEDIATE str_sql;
EXECUTE IMMEDIATE 'INSERT INTO TABLE1(COL1) VALUES(1)';
EXECUTE str_sql;
CALL DBC.SysExecSQL('INSERT INTO TABLE1(COL1) VALUES(1)');
END;
CREATE OR REPLACE PROCEDURE teradata_dynamic_sql ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "teradata", "convertedOn": "07/04/2024" }}'
EXECUTE AS CALLER
AS
$$
DECLARE
str_sql VARCHAR(20);
BEGIN
str_sql := 'UPDATE "TABLE"
SET COLA = 0,
COLB = ''test''';
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE str_sql;
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE 'INSERT INTO TABLE1 (COL1)
VALUES (1);';
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE str_sql;
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE 'INSERT INTO TABLE1 (COL1)
VALUES (1);';
END;
$$;
Oracle¶
CREATE OR REPLACE PROCEDURE oracle_dynamic_sql
AS
dynamic_statement VARCHAR(100);
numeric_variable INTEGER;
dynamic_statement VARCHAR(100);
column_variable VARCHAR(100);
cursor_variable SYS_REFCURSOR;
c INTEGER;
dynamic_statement VARCHAR(100);
BEGIN
dynamic_statement := 'INSERT INTO sample_table(col1) VALUES(1)';
numeric_variable := 3;
column_variable := 'col1';
EXECUTE IMMEDIATE dynamic_statement;
EXECUTE IMMEDIATE 'INSERT INTO sample_table(col1) VALUES(' || numeric_variable || ')';
OPEN cursor_variable FOR dynamic_statement;
OPEN cursor_variable FOR 'SELECT ' || column_variable || ' FROM sample_table';
OPEN cursor_variable FOR 'SELECT col1 FROM sample_table';
c := DBMS_SQL.OPEN_CURSOR;
dynamic_statement := 'SELECT * FROM sample_table';
DBMS_SQL.PARSE(c, dynamic_statement);
END;
CREATE OR REPLACE PROCEDURE oracle_dynamic_sql ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
dynamic_statement VARCHAR(100);
numeric_variable INTEGER;
dynamic_statement VARCHAR(100);
column_variable VARCHAR(100);
cursor_variable_res RESULTSET;
c INTEGER;
dynamic_statement VARCHAR(100);
BEGIN
dynamic_statement := 'INSERT INTO sample_table(col1) VALUES(1)';
numeric_variable := 3;
column_variable := 'col1';
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE :dynamic_statement;
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE 'INSERT INTO sample_table(col1) VALUES(' || NVL(:numeric_variable :: STRING, '') || ')';
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
cursor_variable_res := (
EXECUTE IMMEDIATE :dynamic_statement
);
LET cursor_variable CURSOR
FOR
cursor_variable_res;
OPEN cursor_variable;
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
cursor_variable_res := (
EXECUTE IMMEDIATE 'SELECT ' || NVL(:column_variable :: STRING, '') || ' FROM
sample_table'
);
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0133 - THE CURSOR VARIABLE NAMED 'cursor_variable' HAS ALREADY BEEN ASSIGNED IN ANOTHER CURSOR ***/!!!
LET cursor_variable CURSOR
FOR
cursor_variable_res;
OPEN cursor_variable;
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
cursor_variable_res := (
EXECUTE IMMEDIATE 'SELECT col1 FROM
sample_table'
);
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0133 - THE CURSOR VARIABLE NAMED 'cursor_variable' HAS ALREADY BEEN ASSIGNED IN ANOTHER CURSOR ***/!!!
LET cursor_variable CURSOR
FOR
cursor_variable_res;
OPEN cursor_variable;
c :=
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_SQL.OPEN_CURSOR' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS OPEN_CURSOR;
dynamic_statement := 'SELECT * FROM
sample_table';
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_SQL.PARSE' IS NOT CURRENTLY SUPPORTED. ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
DBMS_SQL.PARSE(:c, :dynamic_statement);
END;
$$;
SQL Server¶
CREATE OR ALTER PROCEDURE transact_dynamic_sql
AS
BEGIN
DECLARE @dynamicStatement AS VARCHAR(200);
DECLARE @numericVariable AS VARCHAR(200);
SET @dynamicStatement = 'INSERT INTO sample_table(col1) VALUES(1);';
SET @numericVariable = '3';
EXECUTE (@dynamicStatement);
EXEC ('INSERT INTO sampleTable(col1) VALUES (' + @numericVariable + ');');
EXECUTE ('INSERT INTO sampleTable(col1) VALUES(10);') AS USER = 'DbAdmin';
INSERT INTO sampleTable EXECUTE sp_executesql @statement = 'SELECT * FROM sampleTable;';
INSERT INTO sampleTable EXECUTE ('SELECT * FROM sampleTable;');
END;
CREATE OR REPLACE PROCEDURE transact_dynamic_sql ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "transact", "convertedOn": "11/13/2024", "domain": "test" }}'
EXECUTE AS CALLER
AS
$$
DECLARE
DYNAMICSTATEMENT VARCHAR(200);
NUMERICVARIABLE VARCHAR(200);
BEGIN
DYNAMICSTATEMENT := 'INSERT INTO sample_table (col1) VALUES(1);';
NUMERICVARIABLE := '3';
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE :DYNAMICSTATEMENT;
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE 'INSERT INTO sampleTable (col1) VALUES (' || :NUMERICVARIABLE || ');';
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - EXECUTE AS USER/LOGIN NOT SUPPORTED IN SNOWFLAKE ***/!!!
EXECUTE IMMEDIATE 'INSERT INTO sampleTable (col1) VALUES(10);';
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INSERT WITH EXECUTE' NODE ***/!!!
INSERT INTO sampleTable EXECUTE IMMEDIATE 'SELECT
*
FROM
sampleTable;';
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'INSERT WITH EXECUTE' NODE ***/!!!
INSERT INTO sampleTable EXECUTE IMMEDIATE 'SELECT
*
FROM
sampleTable;';
END;
$$;
Issues Inside of Dynamic SQL¶
When migrating dynamic SQL code, it’s important to note that SnowConvert does not detect or report issues within dynamic SQL statements. This applies to both the converted output code and assessment reports, even in cases where the documentation or translation specifications indicate that an issue should be flagged. For example, this limitation can be observed when migrating Oracle code.
SELECT dbms_random.value() FROM dual;
CREATE OR REPLACE PROCEDURE dynamic_sql_procedure
AS
result VARCHAR(100) := 'SELECT dbms_random.value() from dual';
BEGIN
NULL;
END;
SELECT
--** SSC-FDM-OR0033 - DBMS_RANDOM.VALUE DIGITS OF PRECISION ARE LOWER IN SNOWFLAKE **
DBMS_RANDOM.VALUE_UDF() FROM dual;
CREATE OR REPLACE PROCEDURE dynamic_sql_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
result VARCHAR(100) := 'SELECT
DBMS_RANDOM.VALUE_UDF() from dual';
BEGIN
NULL;
END;
$$;
When comparing the two approaches, both the query and variable assignment within the procedure will have identical conversions. However, when using dynamic SQL, any conversion issues will not be visible in either the output code or the assessment reports.
Recommendations¶
Enable statement tracking to monitor and validate dynamically generated SQL statements during troubleshooting.
For additional assistance, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0092¶
Deprecated
Severity¶
Low
Description¶
Snowflake Materialized Views have specific limitations that are documented in the Snowflake documentation. Currently, SnowConvert converts all Materialized Views to regular Views, removing any additional clauses. In future releases, SnowConvert will support converting compatible Materialized Views to their Snowflake equivalent.
Example Code¶
Input Code:¶
CREATE MATERIALIZED VIEW MATERIALIZED_VIEW1
SEGMENT CREATION IMMEDIATE
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
INMEMORY PRIORITY NONE MEMCOMPRESS FOR QUERY LOW DISTRIBUTE AUTO NO DUPLICATE
AS
select
*
from
aTable;
Output Code:¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0092 - MATERIALIZED VIEW WAS CONVERTED TO REGULAR VIEW. ***/!!!
CREATE OR REPLACE VIEW MATERIALIZED_VIEW1
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
--** SSC-FDM-0001 - VIEWS SELECTING ALL COLUMNS FROM A SINGLE TABLE ARE NOT REQUIRED IN SNOWFLAKE AND MAY IMPACT PERFORMANCE. **
select
*
from
aTable;
Recommendations¶
No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0004¶
Severity¶
Critical
Description¶
This issue occurs when there is an error while executing a conversion rule for a specific SCRIPT(bteq…) node in the source code.
Recommendations¶
For additional support, please contact our support team at snowconvert-support@snowflake.com
SSC-EWI-0045¶
Severity¶
Medium
Description¶
When converting code to Snowflake, you may encounter column names that are valid in your source database but conflict with Snowflake’s reserved keywords. This happens because Snowflake has a specific set of words that are reserved for system use and cannot be used as column names without special formatting. For a complete list of these keywords, please see Snowflake’s documentation on reserved and limited keywords.
Code example¶
Input¶
CREATE TABLE T1
(
LOCALTIME VARCHAR,
CURRENT_USER VARCHAR
);
Output¶
CREATE OR REPLACE TABLE T1
(
!!!RESOLVE EWI!!! /*** SSC-EWI-0045 - COLUMN NAME 'LOCALTIME' IS A SNOWFLAKE RESERVED KEYWORD ***/!!!
"LOCALTIME" VARCHAR,
!!!RESOLVE EWI!!! /*** SSC-EWI-0045 - COLUMN NAME 'CURRENT_USER' IS A SNOWFLAKE RESERVED KEYWORD ***/!!!
"CURRENT_USER" VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
Recommendations¶
Review and rename any columns that have names not compatible with Snowflake’s naming conventions.
For additional assistance, please contact our support team at snowconvert-support@snowflake.com
SSC-EWI-0014¶
Severity¶
Critical
Description¶
This error occurs when the system fails to generate the required body for a stored procedure statement.
Recommendations¶
For additional support, please contact us at snowconvert-support@snowflake.com
SSC-EWI-0020¶
Severity¶
Low
Summary¶
SnowConvert includes several User-Defined Functions (UDFs) that help replicate behaviors from source languages not natively supported by Snowflake. Here are their functions and descriptions.
UDFs (User-Defined Functions) are located in the “UDF Helpers” folder, which is automatically created in your specified output directory after the migration process completes.
Recommendations¶
Verify that the UDF Helpers folder has been created and contains the expected files.
For additional assistance, please contact our support team at snowconvert-support@snowflake.com