SnowConvert: General Functional Differences¶

 CREATE OR REPLACE VIEW simpleView1
AS
SELECT
*
FROM
simpleTable;

CREATE OR REPLACE VIEW simpleView2
AS
SELECT
*
FROM
simpleTable GROUP BY col1;

 CREATE OR REPLACE VIEW simpleView1
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
simpleTable;

CREATE OR REPLACE VIEW simpleView2
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
simpleTable
GROUP BY col1;

 CREATE TABLE schema1.table1(column1 NVARCHAR(50), column2 NVARCHAR(50));
CREATE TABLE schemaA.tableA(columnA NVARCHAR(50), columnB NVARCHAR(50));

--Correlated Subquery
SELECT columnA FROM schemaA.tableA ta WHERE columnA = (SELECT SUM(column1) FROM schema1.table1 t1 WHERE t1.column1 = ta.columnA);

 CREATE OR REPLACE TABLE schema1.table1 (
column1 VARCHAR(50),
column2 VARCHAR(50))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/11/2024",  "domain": "test" }}'
;

CREATE OR REPLACE TABLE schemaA.tableA (
columnA VARCHAR(50),
columnB VARCHAR(50))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "10/11/2024",  "domain": "test" }}'
;

--Correlated Subquery
SELECT
columnA
FROM
schemaA.tableA ta
WHERE
columnA =
          --** SSC-FDM-0002 - CORRELATED SUBQUERIES MAY HAVE SOME FUNCTIONAL DIFFERENCES. **
          (SELECT
          SUM(column1) FROM
          schema1.table1 t1
          WHERE
          t1.column1 = ta.columnA
          );

 CREATE OR REPLACE EXTERNAL TABLE external_table_test1
(
  col1 INTEGER
)
OPTIONS (
  format = 'CSV',
  uris = ['gs://my_bucket/file.csv']
);

 --** SSC-FDM-0004 - EXTERNAL TABLE TRANSLATED TO REGULAR TABLE **
CREATE OR REPLACE TABLE external_table_test1 (
   col1 INTEGER
 )
-- OPTIONS (
--   format = 'CSV',
--   uris = ['gs://my_bucket/file.csv']
-- )
  ;

 CREATE TABLE TABLE_TIME_TYPE (
    COLNAME TIME (9) WITH TIME ZONE
);

 CREATE OR REPLACE TABLE TABLE_TIME_TYPE (
    COLNAME TIME(9) /*** SSC-FDM-0005 - TIME ZONE NOT SUPPORTED FOR TIME DATA TYPE ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

 CREATE TABLE table1
(
column1 NUMBER,
column2 NUMBER (20, 4)
);

 CREATE OR REPLACE TABLE table1
(
column1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
column2 NUMBER(20, 4) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

 CREATE OR REPLACE TABLE number_table( column1 NUMBER(38, 19) );

INSERT INTO number_table VALUES (1);

SELECT column1 / column1 FROM number_table;

 CREATE OR REPLACE TABLE number_table( column1 NUMBER(38, 20) );

INSERT INTO number_table VALUES (1);

SELECT column1 * column1 FROM number_table;

 CREATE OR REPLACE TABLE number_table( column1 NUMBER(38, 18) );

INSERT INTO number_table VALUES (1);

SELECT column1 / column1 FROM number_table;

 CREATE OR REPLACE TABLE numbertable( column1 NUMBER(38, 19) );

INSERT INTO number_table VALUES (1);

SELECT column1 * column1 FROM number_table;

 CREATE VIEW VIEW01 AS SELECT * FROM TABLE1;

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "TABLE1" **
CREATE OR REPLACE VIEW VIEW01
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
AS
--** SSC-FDM-0001 - VIEWS SELECTING ALL COLUMNS FROM A SINGLE TABLE ARE NOT REQUIRED IN SNOWFLAKE AND MAY IMPACT PERFORMANCE. **
SELECT
* FROM
TABLE1;

 CREATE TABLE TABLE01 (COLNAME VARCHAR(20)) ON COMMIT PRESERVE ROWS
CREATE TEMPORARY TABLE TABLE02 (COLNAME VARCHAR(20)) ON COMMIT DELETE ROWS

 CREATE OR REPLACE TABLE TABLE01 (
COLNAME VARCHAR(20))
----** 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"}}'
;

CREATE OR REPLACE TEMPORARY TABLE TABLE02 (
COLNAME VARCHAR(20))
----** SSC-FDM-0008 - ON COMMIT NOT SUPPORTED **
--ON COMMIT DELETE ROWS
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

 CREATE OR REPLACE GLOBAL TEMPORARY TABLE GLOBAL_TEMP_TABLE
(
    col3 INTEGER,
    col4 VARCHAR(50)
);

 --** SSC-FDM-0009 - GLOBAL TEMPORARY TABLE FUNCTIONALITY NOT SUPPORTED. **
CREATE OR REPLACE TABLE GLOBAL_TEMP_TABLE
    (
        col3 INTEGER,
        col4 VARCHAR(50)
    )
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

 CREATE TABLE "SYSDATE_DEFAULT_TEST_TABLE_1"( 
 "COLUMN1" VARCHAR2(30 BYTE) DEFAULT SYSDATE
);

 CREATE OR REPLACE TABLE "SYSDATE_DEFAULT_TEST_TABLE_1" (
  "COLUMN1" TIMESTAMP DEFAULT CURRENT_TIMESTAMP() /*** SSC-FDM-0010 - CONVERTED FROM VARCHAR2 TO DATE FOR CURRENT_DATE DEFAULT ***/
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 ;

 CREATE TABLE T1
(
    LOCALTIME VARCHAR,
    CURRENT_USER VARCHAR
);

 CREATE OR REPLACE TABLE T1
    (
    --** SSC-FDM-0011 - COLUMN NAME 'LOCALTIME' IS A SNOWFLAKE RESERVED KEYWORD **
    "LOCALTIME" VARCHAR,
    --** SSC-FDM-0011 - 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"}}'
    ;

 CREATE TABLE TABLE1 ( 
COL1 VARCHAR (10) CONSTRAINT constraintName DEFAULT ('0') NOT NULL 
);

 CREATE OR REPLACE TABLE TABLE1 (
COL1 VARCHAR(10)
                 --** SSC-FDM-0012 - CONSTRAINT IN DEFAULT EXPRESSION IS NOT SUPPORTED IN SNOWFLAKE **
                 DEFAULT ('0') NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

 SELECT TIMESTAMP '1998-12-25 09:26:50.12' AT TIME ZONE SESSIONTIMEZONE FROM DUAL;
SELECT TIMESTAMP '1998-12-25 09:26:50.12' AT TIME ZONE Expression FROM DUAL;

 SELECT
--** SSC-FDM-0013 - TIMEZONE EXPRESSION COULD NOT BE MAPPED, RESULTS MAY BE DIFFERENT **
TO_TIMESTAMP_LTZ( TIMESTAMP '1998-12-25 09:26:50.12')
FROM DUAL;

SELECT
--** SSC-FDM-0013 - TIMEZONE EXPRESSION COULD NOT BE MAPPED, RESULTS MAY BE DIFFERENT **
CONVERT_TIMEZONE(Expression, TIMESTAMP '1998-12-25 09:26:50.12')
FROM DUAL;

 select TIMESTAMP '1998-12-25 09:26:50.12' AT TIME ZONE SESSIONTIMEZONE;
select current_timestamp at time zone CONCAT(' America ', ' Pacific'); 
select current_timestamp at time zone (SELECT COL1 FROM TABLE1 WHERE COL2 = 2);

 SELECT
CONVERT_TIMEZONE(SESSIONTIMEZONE, TIMESTAMP '1998-12-25 09:26:50.12') /*** SSC-FDM-0013 - TIMEZONE EXPRESSION COULD NOT BE MAPPED, RESULTS MAY BE DIFFERENT ***/;

SELECT
CONVERT_TIMEZONE(CONCAT(' America ', ' Pacific'), CURRENT_TIMESTAMP) /*** SSC-FDM-0013 - TIMEZONE EXPRESSION COULD NOT BE MAPPED, RESULTS MAY BE DIFFERENT ***/;

SELECT
CONVERT_TIMEZONE((
SELECT
COL1 FROM
TABLE1
WHERE COL2 = 2), CURRENT_TIMESTAMP) /*** SSC-FDM-0013 - TIMEZONE EXPRESSION COULD NOT BE MAPPED, RESULTS MAY BE DIFFERENT ***/;

 CREATE TABLE "Schema"."BaseTable"(
  "COLUMN1" VARCHAR2(255),
  CHECK ( COLUMN1 IS NOT NULL )
);

 CREATE OR REPLACE TABLE "Schema"."BaseTable" (
    "COLUMN1" VARCHAR(255)
--                          ,
--    --** SSC-FDM-0014 - CHECK STATEMENT NOT SUPPORTED **
--    CHECK ( COLUMN1 IS NOT NULL )
  )
  COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
  ;

 CREATE TABLE TABLE1,
    NO FALLBACK,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL
(
    COL0 BYTEINT,
    CONSTRAINT constraint_name CHECK (COL1 < COL2)
)

 CREATE TABLE TABLE1
(
    COL0 BYTEINT
--                ,
--    --** SSC-FDM-0014 - CHECK STATEMENT NOT SUPPORTED **
--    CONSTRAINT constraint_name CHECK (COL1 < COL2)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

 ALTER TABLE table_name2
ADD column_name VARCHAR(255)
CONSTRAINT constraint_name 
CHECK NOT FOR REPLICATION (column_name > 1);

 ALTER TABLE IF EXISTS table_name2
ADD column_name VARCHAR(255)
----** SSC-FDM-0014 - CHECK STATEMENT NOT SUPPORTED **
--CONSTRAINT constraint_name
--CHECK NOT FOR REPLICATION (column_name > 1)
                                           ;

 --Type was never defined
--CREATE TYPE type1;

CREATE TABLE table1
(
column1 type1
);

 --Type was never defined
--CREATE TYPE type1;

CREATE OR REPLACE TABLE table1
(
column1 VARIANT /*** SSC-FDM-TS0015 - DATA TYPE TYPE1 IS NOT SUPPORTED IN SNOWFLAKE ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

 CREATE OR REPLACE PROCEDURE p_constants
AS
my_const1 CONSTANT NUMBER := 40;
my_const2 CONSTANT NUMBER NOT NULL := 40;
BEGIN
NULL;
END;

 CREATE OR REPLACE PROCEDURE p_constants ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
--** SSC-FDM-0016 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE **
my_const1 NUMBER(38, 18) := 40;
--** SSC-FDM-0016 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE **
--** SSC-FDM-OR0025 - NOT NULL CONSTRAINT IS NOT SUPPORTED BY SNOWFLAKE **
my_const2 NUMBER(38, 18) := 40;
BEGIN
NULL;
END;
$$;

 CREATE TABLE t1 (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    SysStartTime TIMESTAMP,
    SysEndTime TIMESTAMP
) WITH SYSTEM VERSIONING;

 CREATE TABLE t1 (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    SysStartTime TIMESTAMP,
    SysEndTime TIMESTAMP
)
----** SSC-FDM-0017 - WITH SYSTEM VERSIONING CLAUSE IS NOT SUPPORTED BY SNOWFLAKE. **
--WITH SYSTEM VERSIONING
                      ;

 CREATE TABLE TABLE01(
    COLNAME VARCHAR(20) CHARACTER SET character_specification
);

 CREATE TABLE TABLE01 (
    COLNAME VARCHAR(20)
--                        --** SSC-FDM-0018 - CHARACTER SET CLAUSE IS NOT SUPPORTED BY SNOWFLAKE. **
--                        CHARACTER SET character_specification
);

 CREATE TABLE T1
(
    COL1 INTEGER
);


CREATE TABLE T1
(
    COL2 INTEGER
);

 CREATE OR REPLACE TABLE T1
(
    COL1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

--** SSC-FDM-0019 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR T1. CHECK IF THE NAME IS INVALID OR DUPLICATED. **
CREATE TABLE T1
(
    COL2 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

 REPLACE MACRO sampleMacro AS 
(
    SELECT CURRENT_DATE AS DT;
    SELECT CURRENT_DATE AS DT_TWO;
);

 CREATE OR REPLACE PROCEDURE sampleMacro ()
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        return_arr ARRAY := array_construct();
        tbl_nm VARCHAR;
    BEGIN
        tbl_nm := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
        CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_nm) AS
            SELECT
                CURRENT_DATE() AS DT;
        return_arr := array_append(return_arr, :tbl_nm);
        tbl_nm := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
        CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_nm) AS
            SELECT
                CURRENT_DATE() AS DT_TWO;
        return_arr := array_append(return_arr, :tbl_nm);
        --** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
        RETURN return_arr;
    END;
$$;

 CREATE INDEX index1
ON table1(column1);

 ----** SSC-FDM-0021 - CREATE INDEX IS NOT SUPPORTED BY SNOWFLAKE **
----** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "table1" **
--CREATE INDEX index1
--ON table1(column1)
                  ;

SELECT
    C_NAME,
    SUM(C_BIRTH_DAY)
    OVER (ORDER BY C_BIRTH_DAY
    RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS MAX1
FROM WINDOW_TABLE;

 SELECT
    C_NAME,
    SUM(C_BIRTH_DAY)
    OVER (ORDER BY C_BIRTH_DAY ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING /*** SSC-FDM-0022 - WINDOW FRAME UNIT 'RANGE' WAS CHANGED TO ROWS ***/) AS MAX1
    FROM
WINDOW_TABLE;

 create global temporary table t1 
    (col1 varchar); 
create view view1 as 
    select col1 from t1;

 --** 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
    --** SSC-FDM-0023 - A Global Temporary Table is being referenced **
    t1;

 CREATE OR REPLACE PROCEDURE PROC01
IS
  number_variable INTEGER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL' INTO number_variable;
END;

 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
    EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL'
--                                           --** SSC-FDM-0024 - FUNCTIONALITY FOR 'EXECUTE IMMEDIATE RETURNING CLAUSE' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--                                           INTO number_variable
                                                               ;
  END;
$$;

 CREATE TABLE MYTABLE
(
    COL1 SYS.ANYDATASET
);

 CREATE OR REPLACE TABLE MYTABLE
    (
    --** SSC-FDM-0026 - TYPE NOT SUPPORTED BY SNOWFLAKE **
        COL1 SYS.ANYDATASET
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
    ;

 .LOGTABLE tduser.Employee_log;  
   .BEGIN MLOAD TABLES Employee_Stg;  
      .LAYOUT Employee;  
      .FIELD in_EmployeeNo * VARCHAR(10);  
      .FIELD in_FirstName * VARCHAR(30); 
      .FIELD in_LastName * VARCHAR(30);  
      .FIELD in_BirthDate * VARCHAR(10); 
      .FIELD in_JoinedDate * VARCHAR(10);  
      .FIELD in_DepartmentNo * VARCHAR(02);

      .dml label EmpLabel
  IGNORE DUPLICATE INSERT ROWS; 
      INSERT INTO Employee_Stg (
         EmployeeNo,
         FirstName,
         LastName,
         BirthDate,
         JoinedDate,
         DepartmentNo
      )  
      VALUES (
         :in_EmployeeNo,
         :in_FirstName,
         :in_Lastname,
         :in_BirthDate,
         :in_JoinedDate,
         :in_DepartmentNo
      );
      .IMPORT INFILE employee.txt  
      FORMAT VARTEXT ','
      LAYOUT Employee
      APPLY EmpLabel;  
   .END MLOAD;  
LOGOFF;

 #*** Generated code is based on the SnowConvert Python Helpers version 2.0.6 ***
// SnowConvert Helpers Code section is omitted.
 
import os
import sys
import snowconvert.helpers
from snowconvert.helpers import Export
from snowconvert.helpers import exec
from snowconvert.helpers import BeginLoading
con = None
def main():
  snowconvert.helpers.configure_log()
  con = snowconvert.helpers.log_on()
  #** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
  #.LOGTABLE tduser.Employee_log
   
  #** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
  #.BEGIN MLOAD TABLES Employee_Stg
   
  Employee_TableName = "Employee_TEMP_TABLE"
  Employee_Columns = """in_EmployeeNo VARCHAR(10), 
in_FirstName VARCHAR(30), 
in_LastName VARCHAR(30), 
in_BirthDate VARCHAR(10), 
in_JoinedDate VARCHAR(10), 
in_DepartmentNo VARCHAR(02)"""
  Employee_Conditions = """in_EmployeeNo AS in_EmployeeNo, in_FirstName AS in_FirstName, in_LastName AS in_LastName, in_BirthDate AS in_BirthDate, in_JoinedDate AS in_JoinedDate, in_DepartmentNo AS in_DepartmentNo"""
  def EmpLabel(tempTableName, queryConditions = ""):
    exec(f"""INSERT INTO Employee_Stg (EmployeeNo, FirstName, LastName, BirthDate, JoinedDate, DepartmentNo)
SELECT
   SRC.in_EmployeeNo,
   SRC.in_FirstName,
   :in_Lastname,
   SRC.in_BirthDate,
   SRC.in_JoinedDate,
   SRC.in_DepartmentNo
FROM {tempTableName} SRC {queryConditions}""")
  #** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. TRANSLATED BELOW **
  #.IMPORT INFILE employee.txt FORMAT VARTEXT ',' LAYOUT Employee APPLY EmpLabel
   
  snowconvert.helpers.import_file_to_temptable(fr"employee.txt", Employee_TableName, Employee_Columns, Employee_Conditions, ',')
  EmpLabel(Employee_TableName)
  exec(f"""DROP TABLE {Employee_TableName}""")

  if con is not None:
    con.close()
    con = None
  snowconvert.helpers.quit_application()

if __name__ == "__main__":
  main()

 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;

 ----** SSC-FDM-0028 - 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
                        ;

 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;

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

 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;

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

 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;

 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;
        --** SSC-FDM-0030 - IDENTIFIER '"VAR`/1ͷ"' HAS INVALID CHARACTERS. CHARACTERS WERE REPLACED WITH THEIR UTF-8 CODES **
        VAR_u60_u2F1_uCD_B7 VARCHAR(20);
        --** SSC-FDM-0030 - IDENTIFIER '"o*/o"' HAS INVALID CHARACTERS. CHARACTERS WERE REPLACED WITH THEIR UTF-8 CODES **
        o_u2A_u2Fo FLOAT;
        --** SSC-FDM-0030 - IDENTIFIER '" . "' HAS INVALID CHARACTERS. CHARACTERS WERE REPLACED WITH THEIR UTF-8 CODES **
        _u20_u2E_u20 INT;
        --** SSC-FDM-0030 - 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;
$$;

 CREATE MATERIALIZED VIEW mv1
AS SELECT * FROM table1;

 CREATE OR REPLACE DYNAMIC TABLE mv1
--** SSC-FDM-0031 - DYNAMIC TABLE REQUIRED PARAMETERS SET BY DEFAULT **
TARGET_LAG='1 day'
WAREHOUSE=UPDATE_DUMMY_WAREHOUSE
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
table1;

 SELECT TO_CHAR(DATE '2001-01-01', 'YYY/MM/DD'),
TO_CHAR(DATE '2001-01-01', f)
FROM (SELECT 'YYY/MM/DD' as f);

 SELECT
PUBLIC.YEAR_PART_UDF(DATE '2001-01-01', 3) || TO_CHAR(DATE '2001-01-01', '/MM/DD'),
--** SSC-FDM-0032 - PARAMETER 'format_string' IS NOT A LITERAL VALUE, TRANSFORMATION COULD NOT BE FULLY APPLIED **
TO_CHAR(DATE '2001-01-01', f)
FROM (SELECT 'YYY/MM/DD' as f);

 SELECT * FROM Employee SAMPLE 2;
SELECT * FROM Employee SAMPLE 0.25;

 SELECT
    * FROM
    Employee
--** SSC-FDM-0033 - SAMPLE CLAUSE BEHAVES DIFFERENTLY IN SNOWFLAKE **
SAMPLE(2 ROWS);

SELECT
    * FROM
    Employee
--** SSC-FDM-0033 - SAMPLE CLAUSE BEHAVES DIFFERENTLY IN SNOWFLAKE **
SAMPLE(25);