SnowConvert AI - Problèmes généraux¶

 CRATE;

CREATE TABLE someTable(col1 INTEGER, !);

CREATE TABRE badTable(col1 INTEGER);

CREATE PROCEDURE proc1()
BEGIN
    CREATE TABLE badEmbeddedTable(col1 INTEGER);
END;

 -- ** 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;
$$;

 CREATE PROCEDURE MySampleProc
    @Param1 NVARCHAR(50) = NULL,
    @Param2 NVARCHAR(10),
    @Param3 NVARCHAR(10) = NULL,
    @Param4 NVARCHAR(10)
AS   
    SELECT 1;

 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;
$$;

select
    TABLE_NAME,
       parent_object_id !!!RESOLVE EWI!!! /*** SSC-EWI-0003 - SYSTEM COLUMN 'parent_object_id' FOR BUILT-IN OBJECT 'SYS.TABLES' HAS NOT BEEN TRANSLATED. ***/!!!
    from
    INFORMATION_SCHEMA.TABLES;

 SELECT 
   DATE_TRUNC('decade', TIMESTAMP '2017-03-17 02:09:30'),
   DATE_TRUNC('century', TIMESTAMP '2017-03-17 02:09:30'),
   DATE_TRUNC('millennium', TIMESTAMP '2017-03-17 02:09:30');

 SELECT
      !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - DECADE FORMAT IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
      DATE_TRUNC('decade', TIMESTAMP '2017-03-17 02:09:30'),
      !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - CENTURY FORMAT IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
      DATE_TRUNC('century', TIMESTAMP '2017-03-17 02:09:30'),
      !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - MILLENNIUM FORMAT IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
      DATE_TRUNC('millennium', TIMESTAMP '2017-03-17 02:09:30');

 CREATE TABLE TABLE01 (
    col1 text COLLATE "C"
);

 CREATE TABLE TABLE01 (
    col1 text
              !!!RESOLVE EWI!!! /*** SSC-EWI-0008 - COLLATE CLAUSE MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!! COLLATE "C"
);

 SELECT REGEXP_SUBSTR('qaqequ','q(?=u)', 1, 1);

 SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-0009 - REGEXP_SUBSTR FUNCTION ONLY SUPPORTS POSIX REGULAR EXPRESSIONS ***/!!!
REGEXP_SUBSTR('qaqequ','q(?=u)', 1, 1);

 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);

 --** 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);

 SELECT * FROM star1p UNPIVOT ((sales,cogs)  FOR  yr_qtr
    IN ((Q101Sales, Q101Cogs) AS 'Q101A',
        (Q201Sales, Q201Cogs) AS 'Q201A', 
        (Q301Sales, Q301Cogs) AS 'Q301A')) AS Tmp;

 // SnowConvert AI 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;

 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
	);

 SELECT *
FROM STAR1 PIVOT(SUM(COL1), SUM(COL2) FOR YR IN ('Y1', 'Y2', 'Y3'))TMP;

 // SnowConvert AI 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;

 SELECT *
FROM STAR1 PIVOT(SUM(COL1) FOR YR IN ('Y1', 'Y2', 'Y3') WITH SUM(*) AS withalias)TMP;

 // SnowConvert AI 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;

 SELECT * FROM   (SELECT product_code, quantity FROM pivot_test)
PIVOT XML (SUM(quantity) 
FOR (product_code) IN ('A','B','C'));

 // SnowConvert AI 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'));

 SELECT * FROM s1 PIVOT(SUM(COL1) FOR FORCOL IN (SELECT SELCOL FROM S2))DT;

 // SnowConvert AI 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));

 SELECT * FROM (SELECT product_code, quantity FROM pivot_test)
PIVOT (SUM(quantity)
FOR product_code IN (ANY, ANY, ANY));

 // SnowConvert AI 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));

 SELECT * FROM db1.star1p UNPIVOT INCLUDE NULLS (column1  FOR  for_column IN (col1, col2)) Tmp;

 // SnowConvert AI 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;

 CREATE VIEW my_view
OPTIONS (
  expiration_timestamp=TIMESTAMP "2026-01-01 00:00:00 UTC",
  privacy_policy='{"aggregation_threshold_policy": {"threshold": 50, "privacy_unit_columns": "ID"}}'
) AS
SELECT column1, column2
FROM my_table;

 CREATE VIEW my_view
!!!RESOLVE EWI!!! /*** SSC-EWI-0016 - SNOWFLAKE DOES NOT SUPPORT THE OPTIONS: EXPIRATION_TIMESTAMP, PRIVACY_POLICY. ***/!!!
OPTIONS(
  expiration_timestamp=TIMESTAMP "2026-01-01 00:00:00 UTC",
  privacy_policy='{"aggregation_threshold_policy": {"threshold": 50, "privacy_unit_columns": "ID"}}'
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "bigquery",  "convertedOn": "07/10/2025",  "domain": "no-domain-provided" }}'
AS
SELECT column1, column2
FROM
  my_table;

 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;

 !!!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;

 -- 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;
);

-- Additional Params: -t javascript
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "TABLE1" **
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": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert AI Helpers Code
var HANDLE_NOTFOUND;
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', ERROR_HANDLERS, ACTIVITY_COUNT = 0, INTO, _OUTQUERIES = [], DYNAMIC_RESULTS = -1;
var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
var fixBind = function (arg) {
arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
return arg;
};
var EXEC = function (stmt,binds,noCatch,catchFunction,opts) {
try {
binds = binds ? binds.map(fixBind) : binds;
_RS = snowflake.createStatement({
sqlText : stmt,
binds : binds
});
_ROWS = _RS.execute();
ROW_COUNT = _RS.getRowCount();
ACTIVITY_COUNT = _RS.getNumRowsAffected();
HANDLE_NOTFOUND && HANDLE_NOTFOUND(_RS);
if (INTO) return {
INTO : function () {
return INTO();
}
};
if (_OUTQUERIES.length < DYNAMIC_RESULTS) _OUTQUERIES.push(_ROWS.getQueryId());
if (opts && opts.temp) return _ROWS.getQueryId();
} catch(error) {
MESSAGE_TEXT = error.message;
SQLCODE = error.code;
SQLSTATE = error.state;
var msg = `ERROR CODE: ${SQLCODE} SQLSTATE: ${SQLSTATE} MESSAGE: ${MESSAGE_TEXT}`;
if (catchFunction) catchFunction(error);
if (!noCatch && ERROR_HANDLERS) ERROR_HANDLERS(error); else throw new Error(msg);
}
};
// END REGION

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
//   UPPER(RTRIM(:LOAD_USER_ID)) = UPPER(RTRIM(LOAD_USER_ID))
null
$$;

 -- 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;

 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 AI 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();
$$;

 -- 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;

 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 AI 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();
$$;

 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;

 -- 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 AI 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();
$$;

 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 AI 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();
$$;

 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;
;

 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;
$$;

 CREATE TABLE MYTABLE
(
    COL1 SYS.ANYDATASET
);

 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"}}'
    ;

 CREATE OR REPLACE EXTERNAL TABLE test.backup_restore_table
OPTIONS (
  format = 'DATASTORE_BACKUP',
  uris = ['gs://backup_bucket/backup_folder/*']
);

 !!!RESOLVE EWI!!! /*** SSC-EWI-0029 - EXTERNAL TABLE DATA FORMAT NOT SUPPORTED IN SNOWFLAKE ***/!!!
CREATE OR REPLACE EXTERNAL TABLE test.backup_restore_table USING TEMPLATE (
SELECT
  ARRAY_AGG(OBJECT_CONSTRUCT('COLUMN_NAME', COLUMN_NAME, 'TYPE', TYPE, 'NULLABLE', NULLABLE, 'EXPRESSION', EXPRESSION))
FROM
  --** SSC-FDM-0035 - THE INFER_SCHEMA FUNCTION REQUIRES A FILE PATH WITHOUT WILDCARDS TO GENERATE THE TABLE TEMPLATE, REPLACE THE FILE_PATH PLACEHOLDER WITH IT **
  TABLE(INFER_SCHEMA(LOCATION => '@EXTERNAL_STAGE/FILE_PATH', FILE_FORMAT => 'SC_TEST_BACKUP_RESTORE_TABLE_FORMAT'))
)
!!!RESOLVE EWI!!! /*** SSC-EWI-0032 - EXTERNAL TABLE REQUIRES AN EXTERNAL STAGE TO ACCESS gs://backup_bucket, DEFINE AND REPLACE THE EXTERNAL_STAGE PLACEHOLDER ***/!!!
LOCATION = @EXTERNAL_STAGE
AUTO_REFRESH = false
PATTERN = 'backup_folder/.*'
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "bigquery",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}';

 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;
$$;

 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;
$$;