SnowConvert: Teradata Issues¶

SSC-EWI-TD0069¶

This Early Warning Issue (EWI) is no longer supported. Please see the SSC-FDM-TD0031 documentation for current information.

Severity¶

Low

Description¶

The Teradata function ST_SPHERICALDISTANCE and Snowflake’s ST_DISTANCE function calculate distances between geographical points differently. Teradata uses the Haversine formula to measure the distance between two points on a sphere (like Earth), while Snowflake’s ST_DISTANCE uses a different method to determine the minimum distance between two geographical coordinates.

Example Code¶

Input Code:¶

--The distance between New York and Los Angeles
Select Cast('POINT(-73.989308 40.741895)' As ST_GEOMETRY) As location1,
	Cast('POINT(40.741895 34.053691)' As ST_GEOMETRY) As location2,
	location1.ST_SPHERICALDISTANCE(location2) As Distance_In_km;

Copy

Output Code¶

--The distance between New York and Los Angeles
SELECT
	Cast('POINT(-73.989308 40.741895)' As GEOGRAPHY) As location1,
	Cast('POINT(40.741895 34.053691)' As GEOGRAPHY) As location2,
	!!!RESOLVE EWI!!! /*** SSC-EWI-TD0069 - ST_DISTANCE RESULTS ARE SLIGHTLY DIFFERENT FROM ST_SPHERICALDISTANCE ***/!!!
	ST_DISTANCE(
	location1, location2) As Distance_In_km;

Copy

Recommendations¶

For additional support, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0079¶

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity¶

Low

Description¶

This warning appears because the required ‘Period’ column, which is needed to replicate the Normalize clause functionality, could not be found.

Example Code¶

Input Code:¶

SELECT NORMALIZE emp_id, duration2 FROM project;

Copy

Output Code¶

!!!RESOLVE EWI!!! /*** SSC-EWI-TD0079 - THE REQUIRED PERIOD TYPE COLUMN WAS NOT FOUND ***/!!!
// SnowConvert Helpers Code section is omitted.
WITH NormalizeCTE AS
(
SELECT
T1.*,
SUM(GroupStartFlag)
OVER (
PARTITION BY
emp_id, duration2
ORDER BY
PeriodColumn_begin
ROWS UNBOUNDED PRECEDING) GroupID
FROM
(
SELECT 
emp_id,
duration2,
PUBLIC.PERIOD_BEGIN_UDF(PeriodColumn) PeriodColumn_begin,
PUBLIC.PERIOD_END_UDF(PeriodColumn) PeriodColumn_end,
(CASE
WHEN PeriodColumn_begin <= LAG(PeriodColumn_end)
OVER (
PARTITION BY
emp_id, duration2
ORDER BY
PeriodColumn_begin,
PeriodColumn_end)
THEN 0
ELSE 1
END) GroupStartFlag FROM 
project
) T1
)
SELECT
emp_id,
duration2,
PUBLIC.PERIOD_UDF(MIN(PeriodColumn_begin), MAX(PeriodColumn_end))
FROM
NormalizeCTE
GROUP BY
emp_id,
duration2,
GroupID;

Copy

Recommendations¶

To resolve this warning, locate the first period column in your code and remove all references to it except where it is initially defined. Then, replace ‘PeriodColumn’ with the name of the column you found.
For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0008¶

Severity¶

Low

Description¶

Currently, Snowflake does not have direct equivalents for certain string comparison functions.

This EWI appears when using any of these string comparison methods: jaro, n_gram, LD (Levenshtein Distance), LDWS (Levenshtein Distance with Substitution), OSA (Optimal String Alignment), DL (Damerau-Levenshtein), hamming, LCS (Longest Common Subsequence), jaccard, cosine, or soundexcode.

Example Code¶

Teradata is a relational database management system that specializes in handling large-scale data warehousing and analytics.¶

SELECT * FROM StringSimilarity (
  ON (
    SELECT CAST(a AS VARCHAR(200)) AS a, CAST(b AS VARCHAR(200)) AS b
    FROM table_1
  ) PARTITION BY ANY
  USING
  ComparisonColumnPairs ('ld(a,b) AS sim_fn')
) AS dt ORDER BY 1;

Copy

Snowflake Scripting:¶

SELECT
  * FROM
  !!!RESOLVE EWI!!! /*** SSC-EWI-TD0008 - FUNCTION FOR COMPARING STRINGS IS NOT SUPPORTED ***/!!! StringSimilarity (
   ON (
     SELECT CAST(a AS VARCHAR(200)) AS a, CAST(b AS VARCHAR(200)) AS b
     FROM table_1
   ) PARTITION BY ANY
   USING
   ComparisonColumnPairs ('ld(a,b) AS sim_fn')
 ) AS dt ORDER BY 1;

Copy

Recommendations¶

For additional support, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0059¶

Severity¶

Low

Description¶

Similar to Teradata, when you set a default time zone for a user, all their sessions will use that time zone until a different time zone is specified for the session.

This warning reminds you to set the same time zone in Snowflake that was previously defined for your Teradata user. To set the time zone in Snowflake, run the following query:

ALTER SESSION SET TIMEZONE = 'equivalent_timezone'

Note: Snowflake only accepts time zones from the IANA Time Zone Database.

Example Code¶

Input Code:¶

SET TIME ZONE USER;

Copy

Output Code:¶

!!!RESOLVE EWI!!! /*** SSC-EWI-TD0059 - SNOWFLAKE USER DEFAULT TIME ZONE MAY REQUIRE CONFIGURATION TO MATCH TERADATA VALUE ***/!!!
ALTER SESSION UNSET TIMEZONE;

Copy

Recommendations¶

Make sure to configure the user’s default time zone to match the time zone setting from the original Teradata user.
For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0049¶

Severity¶

High

Description¶

The tool was unable to process and convert a DML statement in TPT. This typically occurs when:

Script variables are concatenated within the statement
The statement contains escaped quotation marks

Example code¶

Input Code:¶

-- Script1.tpt
DEFINE JOB load_job
DESCRIPTION 'LOAD TABLE FROM A FILE'
  (
     DEFINE SCHEMA schema_name
     DESCRIPTION 'define SCHEMA'
   (
       var1 VARCHAR (50)
   );

   STEP setup_tables
   (
      APPLY
       ('RELEASE MLOAD database_name.table_name;')
     TO OPERATOR (DDL_OPERATOR() );

   );
);

Copy

Output Code:¶

#*** Generated code is based on the SnowConvert Python Helpers version 2.0.6 ***

import os
import sys
import snowconvert.helpers
from snowconvert.helpers import Export
from snowconvert.helpers import exec
from snowconvert.helpers import BeginLoading
import argparse
args = None
# Script1.tpt
class load_job:
    #'LOAD TABLE FROM A FILE'

  jobname = "load_job"
    #'define SCHEMA'

  schema_name = """(
var1 VARCHAR(50)
);"""
  def setup_tables(self):
    self.DDL_OPERATOR()
    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0049 - THE FOLLOWING STATEMENT COULD NOT BE PROCESSED ***/!!!
      #'RELEASE MLOAD database_name.table_name;'


con = None
def main():
  snowconvert.helpers.configure_log()
  con = snowconvert.helpers.log_on()
  _load_job = load_job()
  _load_job.setup_tables()
  snowconvert.helpers.quit_application()

if __name__ == "__main__":
  main()

Copy

Recommendations¶

To resolve this issue, you can either manually write the INSERT statement, or contact the SnowConvert team to request support for this specific DML statement, as it’s not currently supported.
For additional assistance, please email us at snowconvert-support@snowflake.com

SSC-EWI-TD0009¶

Severity¶

Low

Description¶

Teradata’s temporal tables use derived period columns to track time-based data at the column level. This feature is not currently available in Snowflake.

Example Code¶

Teradata:¶

CREATE MULTISET TABLE Policy(
      Policy_ID INTEGER,
      Customer_ID INTEGER,
      Policy_Type CHAR(2) NOT NULL,
      Policy_Details CHAR(40),
      Policy_Start DATE NOT NULL,
      Policy_End DATE NOT NULL,
      PERIOD FOR Validity(Policy_Start,Policy_End) AS VALIDTIME
      )
   PRIMARY INDEX(Policy_ID);

Copy

Snowflake Scripting:¶

CREATE OR REPLACE TABLE Policy (
   Policy_ID INTEGER,
   Customer_ID INTEGER,
   Policy_Type CHAR(2) NOT NULL,
   Policy_Details CHAR(40),
   Policy_Start DATE NOT NULL,
   Policy_End DATE NOT NULL,
   !!!RESOLVE EWI!!! /*** SSC-EWI-TD0009 - TEMPORAL COLUMN NOT SUPPORTED ***/!!!
         PERIOD FOR Validity(Policy_Start,Policy_End) AS VALIDTIME
         )
         COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
         ;

Copy

Recommendations¶

For additional support, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0039¶

Severity¶

Medium

Description¶

The file format you are trying to use is not compatible with Snowflake.

Example Code¶

Input:¶

SELECT
    CAST('01-12-2020' AS DATE FORMAT 'dd-mm-yyyy'),
    CAST('01-12-2020' AS DATE FORMAT 'DDDBEEEE');

Copy

Output:¶

SELECT
    TO_DATE('01-12-2020', 'DD-MM-YYYY') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0025 - OUTPUT FORMAT 'dd-mm-yyyy' NOT SUPPORTED. ***/!!!,
    TO_DATE('01-12-2020', 'DDD EEEE') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0025 - OUTPUT FORMAT 'DDDBEEEE' NOT SUPPORTED. ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-TD0039 - INPUT FORMAT 'DDD EEEE' NOT SUPPORTED ***/!!!;

Copy

Recommendations¶

For additional support, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0068¶

Severity¶

Medium

Description¶

Teradata profiles enable you to set multiple shared parameters that control storage space and manage password restrictions.

Snowflake manages storage through its cloud architecture and handles all optimization automatically, eliminating the need for user-side storage configuration. Additionally, Snowflake enforces a standard password policy that applies universally to all users and cannot be customized.

This error occurs when a Teradata profile reference is detected and converted to a Snowflake role reference. While roles in Snowflake are the closest equivalent to Teradata profiles, query results may differ unless the profile name in Teradata matches the role name in Snowflake.

Example code¶

Input Code:¶

SELECT PROFILE;

Copy

Output Code:¶

SELECT
CURRENT_ROLE() !!!RESOLVE EWI!!! /*** SSC-EWI-TD0068 - SNOWFLAKE DOES NOT SUPPORT PROFILES, REFERENCING ROLE INSTEAD ***/!!!;

Copy

Recommendations¶

Do not use user profiles in your queries because they are not supported. Query results may vary if the user’s profile name differs from their role name.
For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0029¶

Severity¶

Low

Description¶

This warning appears when migrating a TABLE that uses the QUEUE attribute. Since Snowflake does not support the QUEUE keyword, it will be removed during migration.

Example Code¶

Input:¶

CREATE MULTISET TABLE SAMPLE_TABLE,
QUEUE,
NO FALLBACK
(
    COL1 INTEGER
);

Copy

Output:¶

!!!RESOLVE EWI!!! /*** SSC-EWI-TD0029 - QUEUE TABLE FUNCTIONALITY NOT SUPPORTED ***/!!!
CREATE OR REPLACE TABLE SAMPLE_TABLE
(
    COL1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

Copy

Recommendations¶

For additional support, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0063¶

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity¶

Medium

Description¶

This error occurs when SnowConvert encounters a JSON path string that does not follow the required JSON format and cannot be properly deserialized.

Example code¶

Input Code:¶

SELECT
    *
FROM
JSON_TABLE (
    ON (
        SELECT
            id,
            trainSchedule as ts
        FROM
            demo.PUBLIC.Train T
    ) USING rowexpr('$weekShedule.Monday[*]') colexpr(
        '[{"jsonpath"  "$.time",
              "type"" : "CHAR ( 12 )"}]'
    )
) AS JT(Id, Ordinal, Time, City);

Copy

Output Code:¶

SELECT
    *
FROM
    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0063 - UNRECOGNIZED JSON PATH $weekShedule.Monday[*] ***/!!!
JSON_TABLE (
    ON
       !!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!! (
           SELECT
               id,
               trainSchedule as ts
FROM
               demo.PUBLIC.Train T
    ) USING rowexpr('$weekShedule.Monday[*]') colexpr(
        '[{"jsonpath"  "$.time",
              "type"" : "CHAR ( 12 )"}]'
    )
) AS JT(Id, Ordinal, Time, City);

Copy

Recommendations¶

Verify that the JSON path is properly formatted and does not contain invalid characters.
For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0057¶

Severity¶

Low

Description¶

The NEW JSON function accepts JSON data in either string or binary format. When the data is in binary format, the function remains unchanged because Snowflake cannot interpret the binary format’s metadata about the JSON object. For more details, please refer to the Teradata NEW JSON documentation.

Example Code¶

Input Code¶

SELECT NEW JSON ('160000000268656C6C6F0006000000776F726C640000'xb, BSON);

Copy

Output Code¶

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0057 - NEW JSON FUNCTION WITH BINARY DATA IS NOT SUPPORTED ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-TD0039 - INPUT FORMAT 'BSON' NOT SUPPORTED ***/!!!
NEW JSON (TO_BINARY('160000000268656C6C6F0006000000776F726C640000'), BSON);

Copy

Recommendations¶

For additional support, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0006¶

Severity¶

Low

Description¶

The FLOAT data type does not support default values using DEFAULT TIME, DEFAULT DATE, DEFAULT CURRENT_DATE, DEFAULT CURRENT_TIME, or DEFAULT CURRENT_TIMESTAMP specifications.

Example Code¶

Teradata:¶

CREATE TABLE T_2004
(
    -- In the output code all of these columns will be FLOAT type
    -- and will include the SSC-EWI-TD0006 message.
    COL1 FLOAT DEFAULT TIME,
    COL2 FLOAT DEFAULT DATE,
    COL3 FLOAT DEFAULT CURRENT_DATE,
    COL4 FLOAT DEFAULT CURRENT_TIME,
    COL5 FLOAT DEFAULT CURRENT_TIMESTAMP
);

Copy

Snowflake Scripting:¶

CREATE OR REPLACE TABLE T_2004
(
    -- In the output code all of these columns will be FLOAT type
    -- and will include the SSC-EWI-TD0006 message.
    COL1 FLOAT DEFAULT TIME !!!RESOLVE EWI!!! /*** SSC-EWI-TD0006 - DEFAULT CURRENT_TIME NOT VALID FOR DATA TYPE ***/!!!,
    COL2 FLOAT DEFAULT DATE !!!RESOLVE EWI!!! /*** SSC-EWI-TD0006 - DEFAULT CURRENT_DATE NOT VALID FOR DATA TYPE ***/!!!,
    COL3 FLOAT DEFAULT CURRENT_DATE !!!RESOLVE EWI!!! /*** SSC-EWI-TD0006 - DEFAULT CURRENT_DATE NOT VALID FOR DATA TYPE ***/!!!,
    COL4 FLOAT DEFAULT CURRENT_TIME !!!RESOLVE EWI!!! /*** SSC-EWI-TD0006 - DEFAULT CURRENT_TIME NOT VALID FOR DATA TYPE ***/!!!,
    COL5 FLOAT DEFAULT CURRENT_TIMESTAMP !!!RESOLVE EWI!!! /*** SSC-EWI-TD0006 - DEFAULT CURRENT_TIMESTAMP NOT VALID FOR DATA TYPE ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

Copy

Recommendations¶

No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0002¶

This EWI has been deprecated since the SnowConvert 28.1.100 release.

Severity¶

High

Description¶

When a SQL statement includes a column selector of type INTERVAL, SnowConvert will add an EWI (Error, Warning, Information) message and create a stub function. Since Snowflake does not support the INTERVAL data type, manual intervention will be required after the conversion process is complete.

Example Code¶

Teradata:¶

SELECT
     CAST('07:00' AS INTERVAL HOUR(2) TO MINUTE),
     CAST('08:00' AS INTERVAL HOUR(2) TO MINUTE) As Test_Interval;

Copy

Snowflake Scripting:¶

SELECT
     !!!RESOLVE EWI!!! /*** SSC-EWI-TD0002 - INTERVAL TYPE NOT SUPPORTED IN SNOWFLAKE ***/!!!
     INTERVAL '07 hour, 00 min',
     !!!RESOLVE EWI!!! /*** SSC-EWI-TD0002 - INTERVAL TYPE NOT SUPPORTED IN SNOWFLAKE ***/!!!
     INTERVAL '08 hour, 00 min' As Test_Interval;

Copy

Recommendations¶

For additional support, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0053¶

Parts of the code have been removed to make the example clearer and easier to understand.

Precision of generated varchar representations¶

The PERIOD_UDF function creates a string representation of a period using Snowflake’s default timestamp and time formats. By default, timestamps include three decimal places for precision, while time variables have no decimal places. If you need to adjust the precision in the output string, you have two options available.

Use the three-parameter version of PERIOD_UDF with the PRECISIONDIGITS parameter. This parameter accepts an integer from 0 to 9 and controls the number of decimal places in the fractional time part of the result. While Snowflake supports up to nine decimal places, Teradata only supports up to six. For example:

Call	Result
`PUBLIC.PERIOD_UDF(time '13:30:45.870556', time '15:35:20.344891', 0)`	`'13:30:45*15:35:20'`
`PUBLIC.PERIOD_UDF(time '13:30:45.870556', time '15:35:20.344891', 2)`	`'13:30:45.87*15:35:20.34'`
`PUBLIC.PERIOD_UDF(time '13:30:45.870556', time '15:35:20.344891', 5)`	`'13:30:45.87055*15:35:20.34489'`

Set the time format for session output: Use the commands ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = <format> and ALTER SESSION SET TIME_OUTPUT_FORMAT = <format> to modify how timestamps and times are displayed in your session.

You can modify Snowflake’s default formats for the current session to specify the desired number of precision digits. These changes will affect all subsequent executions of PERIOD_UDF during your session.

Example code¶

Input code:¶

create table vacations (
    employeeName varchar(50),
    duration period(date)
);

insert into vacations values ('Richard', period(date '2021-05-15', date '2021-06-15'));

select end(duration) from vacations;

Copy

Output code:¶

CREATE OR REPLACE TABLE vacations (
    employeeName varchar(50),
    duration VARCHAR(24) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

INSERT INTO vacations
VALUES ('Richard', PUBLIC.PERIOD_UDF(date '2021-05-15', date '2021-06-15') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!);

SELECT
    PUBLIC.PERIOD_END_UDF(duration) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!! from
    vacations;

Copy

Recommendations¶

Since PERIOD and its related functions are simulated using varchar data type, we recommend that you verify the accuracy of the results.
For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0077¶

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity¶

Moderate

Description¶

SnowConvert (SC) only supports RESET WHEN clauses that use binary comparison operators (<=, >= or =). If you use any other conditions, such as IS NOT NULL, SC will remove the RESET WHEN clause and display an error message because Snowflake does not support these conditions.

This error message appears when the RESET WHEN condition refers to an expression that the migration tool cannot locate. At present, the tool only supports column aliases that are defined within the same query.

Example Code¶

Condition is not binary¶

Input Code:¶

SELECT
    account_id,
    month_id,
    balance,
    ROW_NUMBER() OVER (
        PARTITION BY account_id
        ORDER BY month_id
        RESET WHEN balance IS NOT NULL
        ROWS UNBOUNDED PRECEDING
    ) as balance_increase
FROM account_balance
ORDER BY 1,2;

Copy

Output Code¶

// SnowConvert Helpers Code section is omitted.
SELECT
    account_id,
    month_id,
    balance,
    ROW_NUMBER() OVER (
        PARTITION BY account_id
    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0077 - RESET WHEN CLAUSE IS NOT SUPPORTED IN THIS SCENARIO DUE TO ITS CONDITION ***/!!!
        ORDER BY month_id
        ROWS UNBOUNDED PRECEDING
    ) as balance_increase
FROM
    account_balance
ORDER BY 1,2;

Copy

Condition expression was not found¶

Input Code:¶

SELECT
    account_id,
    month_id,
    balance,
    ROW_NUMBER() OVER (
        PARTITION BY account_id
        ORDER BY month_id
        RESET WHEN balance <= not_found_expresion
    ) as balance_increase
FROM account_balance
ORDER BY 1,2;

Copy

Output Code¶

// SnowConvert Helpers Code section is omitted.
SELECT
    account_id,
    month_id,
    balance,
    ROW_NUMBER() OVER (
        PARTITION BY account_id
    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0077 - RESET WHEN CLAUSE IS NOT SUPPORTED IN THIS SCENARIO DUE TO ITS CONDITION ***/!!!
        ORDER BY month_id
    ) as balance_increase
FROM
    account_balance
ORDER BY 1,2;

Copy

Recommendations¶

For additional support, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0012¶

Severity¶

Low

Description¶

This warning appears when SnowConvert detects a BINARY data type column with a DEFAULT value. Since Snowflake does not support default values for BINARY columns, SnowConvert removes the DEFAULT value specification.

Example Code¶

Teradata:¶

CREATE TABLE TableExample
(
ColumnExample BINARY DEFAULT '00000000'XB NOT NULL
)

Copy

Snowflake Scripting:¶

CREATE OR REPLACE TABLE TableExample (
ColumnExample BINARY DEFAULT NOT TO_BINARY('00000000') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0012 - BINARY DOES NOT SUPPORT DEFAULT NOT TO_BINARY('00000000') ***/!!! NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

Copy

Recommendations¶

For additional support, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0003¶

Severity¶

Low

Description¶

In Snowflake, the trim functions (LTRIM, RTRIM, or TRIM) only support collation when removing empty spaces or whitespace characters. They do not support collation for other character types.

When SnowConvert encounters LTRIM, RTRIM, or TRIM (with LEADING or TRAILING options) functions on a collated column, it automatically adds a COLLATE function. This function creates an uncollated copy of the input column before applying the trim operation. The Early Warning Indicator (EWI) alerts you that the column’s collation was removed before the trim function was applied. As a result, the trimmed output will not have collation settings, which may affect subsequent string comparisons using this result.

Example Code¶

Teradata:¶

CREATE TABLE collateTable (
	col1 VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC
);

SELECT
    TRIM(BOTH '0' FROM col1),
    TRIM(LEADING '  ' FROM col1),
    TRIM(TRAILING '0' FROM col1),
    LTRIM(col1, '0'),
    RTRIM(col1)
FROM
    collateTable;

Copy

Snowflake Scripting:¶

CREATE OR REPLACE TABLE collateTable (
	col1 VARCHAR(50) COLLATE 'en-cs'
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "06/17/2024" }}'
;

SELECT
	TRIM(COLLATE(col1, ''), '0') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0003 - COLLATION NOT SUPPORTED IN TRIM FUNCTIONS, ADD ORIGINAL COLLATION TO FUNCTION RESULT TO PRESERVE IT ***/!!!,
	LTRIM(col1, '  '),
	RTRIM(COLLATE(col1, ''), '0') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0003 - COLLATION NOT SUPPORTED IN TRIM FUNCTIONS, ADD ORIGINAL COLLATION TO FUNCTION RESULT TO PRESERVE IT ***/!!!,
	LTRIM(COLLATE(col1, ''), '0') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0003 - COLLATION NOT SUPPORTED IN TRIM FUNCTIONS, ADD ORIGINAL COLLATION TO FUNCTION RESULT TO PRESERVE IT ***/!!!,
	RTRIM(col1)
	FROM
	collateTable;

Copy

Recommendations¶

To maintain consistent comparison behavior, append the original column’s collation to the TRIM function result using the COLLATE function. Specify the original collation value as a literal string in the second argument.
For additional assistance, contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0052¶

Severity¶

Low

Description¶

Both Teradata and Snowflake automatically convert string values to numbers when they are passed to functions that expect numeric parameters. The string values are parsed and converted to their numeric equivalents.

Teradata and the target language handle numeric strings differently. Teradata is more flexible and can parse various string formats as numbers, including:

Empty strings or strings with only whitespace
Strings with embedded dashes
Numbers without digits in the mantissa or exponent
Currency symbols
Numbers with digit separators
Numbers where the sign appears after the digits
'1-2-3-4-5' -> 12345
'$50' -> 50
'5000-' -> -5000
'1,569,284.55' -> 1569284.55

Snowflake uses automatic optimistic string conversion and expects strings to match either TM9 or TME formats. When strings don’t match these formats, the conversion fails. To address this, SnowConvert processes string literals that require conversion to numeric values by generating strings that match TM9 or TME formats. This ensures Snowflake can parse them correctly. Note that this conversion only works for literal string values; non-literal values may not be parsed successfully by Snowflake.

Example code¶

Input code:¶

create table myTable(
    stringCol varchar(30)
);

insert into myTable values ('   1,236,857.45-');

select cos('   1,236,857.45-');

select cos(stringCol) from myTable;

Copy

Output code:¶

CREATE OR REPLACE TABLE myTable (
    stringCol varchar(30)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

INSERT INTO myTable
VALUES ('   1,236,857.45-');

SELECT
    cos('-1236857.45');

SELECT
    cos(stringCol !!!RESOLVE EWI!!! /*** SSC-EWI-TD0052 - SNOWFLAKE IMPLICIT CONVERSION TO NUMERIC DIFFERS FROM TERADATA AND MAY FAIL FOR NON-LITERAL STRING VALUES ***/!!!)
from
    myTable;

Copy

Recommendations¶

No action is required from your side.
For additional support, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0066¶

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity¶

Low

Description¶

This message appears when SnowConvert encounters a Teradata Unicode Delimited Identifier that contains characters not supported by Snowflake. For more information, see Teradata Unicode Delimited Identifier.

Example code¶

Input Code:¶

SELECT * FROM U&"#000f#ffff" UESCAPE '#';

Copy

Output Code:¶

// SnowConvert Helpers Code section is omitted.
SELECT
* FROM
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0066 - THE FOLLOWING IDENTIFIER HAS ONE OR MORE UNICODE ESCAPE CHARACTERS THAT ARE INVALID IN SNOWFLAKE ***/!!!
"\u000f\uffff";

Copy

Recommendations¶

Use only valid Unicode characters when creating identifiers in Snowflake.
For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0027¶

Severity¶

Low

Description¶

Snowflake does not support Teradata’s built-in temporal attributes such as VALIDTIME or TRANSACTIONTIME, which results in an EWI (Error, Warning, Information) message.

Example Code¶

Teradata input:¶

CREATE MULTISET TABLE SAMPLE_TABLE
(
    COL1 PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME
);

Copy

Snowflake output:¶

CREATE OR REPLACE TABLE SAMPLE_TABLE (
       COL1 VARCHAR(68) NOT NULL !!!RESOLVE EWI!!! /*** SSC-EWI-TD0027 - SNOWFLAKE DOES NOT SUPPORT 'TRANSACTIONTIME' COLUMN OPTION ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!
   )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

Copy

Recommendations¶

Create TIMESTAMP columns manually and set their default values to CURRENT_TIMESTAMP.
Use table streams to track changes in your data and capture metadata for each modification. (Guide)
For additional assistance, contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0076¶

Severity¶

Moderate

Description¶

Foreign tables in Teradata allow you to access data stored in external locations like Amazon S3, Azure Blob storage, and Google Cloud Storage. While Snowflake doesn’t support this specific syntax, you can achieve similar functionality using:

External tables
Iceberg tables
Standard tables

Example code¶

Input code:¶

SELECT cust_id, income, age FROM
FOREIGN TABLE (SELECT cust_id, income, age FROM twm_customer)@hadoop1 T1;

Copy

Output code:¶

SELECT
cust_id,
income,
age FROM
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0076 - THE USE OF FOREIGN TABLES IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
 FOREIGN TABLE (SELECT cust_id, income, age FROM twm_customer)@hadoop1 T1;

Copy

Recommendations¶

To replace Teradata foreign tables, you can use Snowflake external tables. These tables read data directly from cloud storage platforms (Amazon S3, Google Cloud Storage, or Microsoft Azure). This allows you to query data lake files as if they were database tables. External tables support all file formats that work with COPY INTO
Snowflake’s Iceberg tables provide another option. These tables use open formats and store data as Parquet files in your own cloud storage.
Standard Snowflake tables can also replace Teradata foreign tables functionality.
For additional help, contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0091¶

Some sections of the output code have been removed to make it easier to understand.

Severity¶

Medium

Description¶

In Teradata scripts, you can convert data types using the CAST expression with the following syntax:

<expression> ( <DataType> )

Copy

When converting data types using CAST with DATE or TIME, ambiguity can occur because these keywords also function as CURRENT_DATE and CURRENT_TIME functions respectively.

Therefore, when examining code without additional context about the expression requiring a CAST operation, it becomes difficult to distinguish between a genuine CAST operation and a function that simply takes DATE or TIME parameters as input.

To determine if <expression> is a column or a user-defined function (UDF), you must include the corresponding CREATE TABLE or CREATE FUNCTION statement when converting the code. This provides the necessary context about the expression’s definition.

For example, let’s examine the following SELECT statement. When we encounter AMBIGUOUS_EXPR, without additional context, we cannot determine if it represents a function call or a type conversion to DATE. However, we can be certain that COL1 (DATE) is a type conversion (CAST) operation because COL1 is a column from the table TAB.

CREATE TABLE TAB (
    COL1 VARCHAR(23)
)

SELECT
    COL1 (DATE),
    AMBIGUOUS_EXPR (DATE)
FROM TAB;

Copy

Example Code¶

Input Code:¶

CREATE TABLE TAB (
    COL1 VARCHAR(23)
)

SELECT
    COL1 (DATE),
    AMBIGUOUS_EXPR (DATE)
FROM TAB;

Copy

Output Code¶

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

SELECT
    TO_DATE(
    COL1, 'YYYY/MM/DD') AS COL1,
    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0091 - EXPRESSION CONVERTED AS CAST BY DEFAULT. CONVERSION MIGHT PRESENT ERRORS DUE TO MISSING DEPENDENCIES FOR 'AMBIGUOUS_EXPR'. ***/!!!
    AMBIGUOUS_EXPR :: DATE
    FROM
    TAB;

Copy

Recommendations¶

For additional support, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0017¶

Severity¶

Low

Description¶

This warning appears when SnowConvert detects a Create Table statement that includes the GLOBAL TEMPORARY TRACE option. This feature, which is specific to Teradata (TRACE functionality), is not available in Snowflake and will be removed during conversion.

Example Code¶

Teradata:¶

CREATE GLOBAL TEMPORARY TRACE TABLE TableExample
(
ColumnExample Number
)

Copy

Snowflake Scripting:¶

!!!RESOLVE EWI!!! /*** SSC-EWI-TD0017 - GLOBAL TEMPORARY TABLE TRACE FUNCTIONALITY NOT SUPPORTED ***/!!!
CREATE OR REPLACE TABLE TableExample (
ColumnExample NUMBER(38, 18)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

Copy

Recommendations¶

Note: You can replicate tracing functionality in Snowflake using an EVENT TABLE. For more information, please refer to the Snowflake documentation on Logging and Tracing.
For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0046¶

Severity¶

Medium

Description¶

This error occurs when referencing a DBC table where the selected column does not have a corresponding match in Snowflake.

Example Code¶

Input:¶

CREATE VIEW SAMPLE_VIEW
AS
SELECT PROTECTIONTYPE FROM DBC.DATABASES;

Copy

Output:¶

CREATE OR REPLACE VIEW SAMPLE_VIEW
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "08/14/2024" }}'
AS
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0046 - BUILT-IN REFERENCE TO PROTECTIONTYPE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
PROTECTIONTYPE FROM
INFORMATION_SCHEMA.DATABASES;

Copy

Recommendations¶

For additional support, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0023¶

Severity¶

Low

Description¶

The ACTIVITY_COUNT status variable shows how many rows were modified by a SQL DML statement (such as INSERT, UPDATE, or DELETE) when used in either embedded SQL or stored procedures. For more details, visit here.

To replicate the behavior of ACTIVITY_COUNT, you can use the workaround described in the translation specification.

SELECT $1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Copy

When using ACTIVITY_COUNT in a SELECT or SET INTO VARIABLE statement, you cannot directly substitute it with the previously mentioned workaround.

Example Code¶

Teradata¶

REPLACE PROCEDURE InsertEmployeeSalaryAndLog_4 ()
BEGIN
    DECLARE rowCount INT;
    DECLARE message VARCHAR(100);

    INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
    VALUES (101, 'Alice', 'Smith', 10, 70000.00);

    SELECT ACTIVITY_COUNT INTO rowCount;
    SET message = 'ROWS INSERTED: ' || rowCount;

    -- Insert the ACTIVITY_COUNT into the activity_log table
    INSERT INTO activity_log (operation, row_count)
    VALUES (message, rowCount);
END;

Copy

Snowflake¶

CREATE OR REPLACE PROCEDURE InsertEmployeeSalaryAndLog_4 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/15/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
               rowCount INT;
               message VARCHAR(100);
    BEGIN


               INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
               VALUES (101, 'Alice', 'Smith', 10, 70000.00);
               SELECT
            ACTIVITY_COUNT !!!RESOLVE EWI!!! /*** SSC-EWI-TD0023 - ACTIVITY_COUNT INSIDE SELECT/SET INTO VARIABLE REQUIRES MANUAL FIX ***/!!! INTO
            :rowCount;
            message := 'ROWS INSERTED: ' || rowCount;

            -- Insert the ACTIVITY_COUNT into the activity_log table
            INSERT INTO activity_log (operation, row_count)
            VALUES (:message, :rowCount);
    END;
$$;

Copy

Manual Fix¶

To retrieve the count of rows that were inserted, updated, or deleted, you can use part of the workaround described above.

CREATE OR REPLACE PROCEDURE InsertEmployeeSalaryAndLog_4 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/15/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
               rowCount INT;
               message VARCHAR(100);
    BEGIN


               INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
               VALUES (101, 'Alice', 'Smith', 10, 70000.00);
               SELECT $1 INTO :rowCount FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
            message := 'ROWS INSERTED: ' || rowCount;

            -- Insert the ACTIVITY_COUNT into the activity_log table
            INSERT INTO activity_log (operation, row_count)
            VALUES (:message, :rowCount);
    END;
$$;

Copy

To use this query in Snowflake, you need to modify it to use Snowflake’s SELECT INTO VARIABLE syntax.

Furthermore, if you notice incorrect results when using RESULT_SCAN(LAST_QUERY_ID()), please refer to SSC-FDM-TD0033 for information about potential limitations and how to address them.

Recommendations¶

Apply the suggested workaround manually.
Review SSC-FDM-TD0033 to understand the limitations of using LAST_QUERY_ID and how to handle them.
For additional assistance, contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0051¶

Severity¶

Low

Description¶

When working with byte data types, there’s an important difference between Teradata and Snowflake:

In Teradata, the BYTES function will always count the trailing zeros because it uses fixed-length storage. This means it returns the column’s size rather than the actual data size.
In Snowflake, the LENGTH function returns the actual size of the stored data because it uses variable-length storage for binary types.

Here’s an example to illustrate this difference:

Teradata:

create table exampleTable(
	bytecol byte(10)
);

insert into exampleTable values ('2B'XB);

select bytes(bytecol) from exampleTable;
-- Will return 10, the size of bytecol

Copy

Code equivalent in Snowflake:

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

INSERT INTO exampleTable
VALUES (TO_BINARY('2B'));

SELECT
	LENGTH(bytecol) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0051 - TERADATA BYTES FUNCTION RESULTS DIFFER FROM SNOWFLAKE LENGTH FUNCTION FOR BYTE TYPE COLUMNS ***/!!! from
	exampleTable;
	-- Will return 10, the size of bytecol

Copy

Example code:¶

Input code:¶

create table sampleTable(
    byteColumn byte(10),
    varbyteColumn varbyte(15)
);

select bytes(byteColumn), bytes(varbyteColumn) from sampleTable;

Copy

Output code:¶

CREATE OR REPLACE TABLE sampleTable (
    byteColumn BINARY,
    varbyteColumn BINARY(15)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

SELECT
    LENGTH(byteColumn) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0051 - TERADATA BYTES FUNCTION RESULTS DIFFER FROM SNOWFLAKE LENGTH FUNCTION FOR BYTE TYPE COLUMNS ***/!!!,
    LENGTH(varbyteColumn) from
    sampleTable;

Copy

Recommendations¶

The BYTES function results analysis shows that the Snowflake LENGTH function already provides the desired behavior, so no modifications are needed.
For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0034¶

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity¶

Low

Description¶

Executing multiple SQL statements in a single request is not supported. Your request was processed as a single transaction.

This error warning information (EWI) appears exclusively when the PL Target Language flag is set to Javascript using the command: ‘–PLTargetLanguage Javascript’

Example Code¶

Input:¶

-- Additional Params: --PLTargetLanguage Javascript
REPLACE PROCEDURE proc1()
  BEGIN
    BEGIN REQUEST;
      SELECT* FROM TABLE1;
    END REQUEST;
END;

Copy

Output:¶

CREATE OR REPLACE PROCEDURE 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 TRANSACTION_HANDLER = function (error) {
    throw error;
  };
  // ** SSC-EWI-TD0034 - MULTISTATEMENT SQL EXECUTION NOT SUPPORTED, REQUEST HANDLED AS TRANSACTION **
  try {
    EXEC(`BEGIN`);
    EXEC(`SELECT
   *
FROM
   TABLE1`,[],undefined,TRANSACTION_HANDLER);
    EXEC(`COMMIT`);
  } catch(error) {
    EXEC(`ROLLBACK`);
  }
$$;

Copy

Recommendations¶

For additional support, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0024¶

Severity¶

Low

Description¶

This warning appears when an AGGREGATE function is used within a commented-out ABORT statement inside a stored procedure.

Example Code¶

Teradata:¶

REPLACE PROCEDURE ABORT_SAMPLE()
BEGIN
    ABORT WHERE SUM(TABLE1.COL1) < 2;
END;

Copy

Snowflake Scripting:¶

CREATE OR REPLACE PROCEDURE ABORT_SAMPLE()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-TD0024 - ABORT STATEMENT IS NOT SUPPORTED DUE TO AN AGGREGATE FUNCTION ***/!!!
        ABORT WHERE SUM(TABLE1.COL1) < 2;
    END;
$$;

Copy

Recommendations¶

For additional support, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0010¶

Severity¶

Low

Description¶

The UPPERCASE column attribute, which is used to convert text to uppercase letters, is not available in Snowflake.

Example Code¶

Teradata:¶

CREATE TABLE T_2010
(
    col1 VARCHAR(1) UPPERCASE
);

Copy

Snowflake Scripting:¶

CREATE OR REPLACE TABLE T_2010 (
    col1 VARCHAR(1)
                    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0010 - UPPERCASE NOT SUPPORTED BY SNOWFLAKE ***/!!!
 UPPERCASE
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

Copy

Recommendations¶

The UPPERCASE clause converts lowercase characters to uppercase when storing data. For example, ‘aaa’ is stored as ‘AAA’. To handle this, you can add the UPPER function to all insert statements. However, be aware that you will also need to modify any ETL processes that load external data.
For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0041¶

Severity¶

Low

Description¶

The file format you provided is not supported by the system.

Example Code¶

Input:¶

SELECT
    cast(T_2041.integer_column as integer);

Copy

Output:¶

SELECT
    cast(TRUNC(T_2041.integer_column) as integer) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0041 - TRUNC FUNCTION WAS ADDED TO ENSURE INTEGER. MAY NEED CHANGES IF NOT NUMERIC OR STRING. ***/!!!;

Copy

Recommendations¶

For additional support, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0061¶

Severity¶

Low

Description¶

SnowConvert does not support or transform the TD_UNPIVOT function, which converts column data into rows in a table.

This transformation needs information about the column names in the table(s) to work correctly. If this information is not available, the transformation may be incomplete, with missing columns in the result. In such cases, an EWI (Error, Warning, Information) message will be generated.

Example code¶

Input Code:¶

CREATE TABLE unpivotTable  (
	myKey INTEGER NOT NULL PRIMARY KEY,
	firstSemesterIncome DECIMAL(10,2),
	secondSemesterIncome DECIMAL(10,2),
	firstSemesterExpenses DECIMAL(10,2),
	secondSemesterExpenses DECIMAL(10,2)
);

SELECT * FROM
 TD_UNPIVOT(
 	ON unpivotTable
 	USING
 	VALUE_COLUMNS('Income', 'Expenses')
 	UNPIVOT_COLUMN('Semester')
 	COLUMN_LIST('firstSemesterIncome, firstSemesterExpenses', 'secondSemesterIncome, secondSemesterExpenses')
 	COLUMN_ALIAS_LIST('First', 'Second')
 )X ORDER BY mykey;

SELECT * FROM
 TD_UNPIVOT(
 	ON unknownTable
 	USING
 	VALUE_COLUMNS('MonthIncome')
 	UNPIVOT_COLUMN('Months')
 	COLUMN_LIST('januaryIncome', 'februaryIncome', 'marchIncome', 'aprilIncome')
 	COLUMN_ALIAS_LIST('January', 'February', 'March', 'April')
 )X ORDER BY yearKey;

Copy

Output Code:¶

CREATE OR REPLACE TABLE unpivotTable (
	myKey INTEGER NOT NULL PRIMARY KEY,
	firstSemesterIncome DECIMAL(10,2),
	secondSemesterIncome DECIMAL(10,2),
	firstSemesterExpenses DECIMAL(10,2),
	secondSemesterExpenses DECIMAL(10,2)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "VALUE_COLUMNS", "UNPIVOT_COLUMN", "COLUMN_LIST", "COLUMN_ALIAS_LIST" **
SELECT
	* FROM
	(
		SELECT
			myKey,
			TRIM(GET_IGNORE_CASE(OBJECT_CONSTRUCT('FIRSTSEMESTERINCOME', 'First', 'FIRSTSEMESTEREXPENSES', 'First', 'SECONDSEMESTERINCOME', 'Second', 'SECONDSEMESTEREXPENSES', 'Second'), Semester), '"') AS Semester,
			Income,
			Expenses
		FROM
			unpivotTable UNPIVOT(Income FOR Semester IN (
				firstSemesterIncome,
				secondSemesterIncome
			)) UNPIVOT(Expenses FOR Semester1 IN (
				firstSemesterExpenses,
				secondSemesterExpenses
			))
		WHERE
			Semester = 'FIRSTSEMESTERINCOME'
			AND Semester1 = 'FIRSTSEMESTEREXPENSES'
			OR Semester = 'SECONDSEMESTERINCOME'
			AND Semester1 = 'SECONDSEMESTEREXPENSES'
	) X ORDER BY mykey;

	--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "VALUE_COLUMNS", "UNPIVOT_COLUMN", "COLUMN_LIST", "COLUMN_ALIAS_LIST" **
	SELECT
	* FROM
	!!!RESOLVE EWI!!! /*** SSC-EWI-TD0061 - TD_UNPIVOT TRANSFORMATION REQUIRES COLUMN INFORMATION THAT COULD NOT BE FOUND, COLUMNS MISSING IN RESULT ***/!!!
	(
		SELECT
			TRIM(GET_IGNORE_CASE(OBJECT_CONSTRUCT('JANUARYINCOME', 'January', 'FEBRUARYINCOME', 'February', 'MARCHINCOME', 'March', 'APRILINCOME', 'April'), Months), '"') AS Months,
			MonthIncome
		FROM
			unknownTable UNPIVOT(MonthIncome FOR Months IN (
				januaryIncome,
				februaryIncome,
				marchIncome,
				aprilIncome
			))
	) X ORDER BY yearKey;

Copy

Recommendations¶

You can provide column information to the conversion tool using one of two methods:
- Include the table specification in the same file as the TD_UNPIVOT call
- List specific columns in the SELECT query of the ON expression instead of using SELECT * or just the table name
If you are unpivoting ALL columns from the input tables, you can ignore this issue. However, if you are only unpivoting some columns, the result will be missing data.
For additional support, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0004¶

Severity¶

Low

Description¶

Snowflake procedures do not have a direct equivalent for Teradata’s Continue Handler functionality. While SnowConvert can handle some exception codes and emulate similar behavior, it will generate an EWI (Error, Warning, or Information) message when encountering unsupported exception codes in Continue Handler statements.

Example Code¶

Teradata:¶

REPLACE PROCEDURE PURGING_ADD_TABLE
(
 IN inDatabaseName     	VARCHAR(30),
 IN inTableName    		VARCHAR(30)
)
BEGIN
 DECLARE vCHAR_SQLSTATE CHAR(5);
 DECLARE vSUCCESS       CHAR(5);

  DECLARE CONTINUE HANDLER FOR SQLSTATE 'UNSUPPORTED'
  BEGIN
     SET vCHAR_SQLSTATE = SQLCODE;
     SET vSUCCESS    = SQLCODE;
  END;

  SELECT 1;

END;

Copy

Snowflake Scripting:¶

CREATE OR REPLACE PROCEDURE PURGING_ADD_TABLE
(INDATABASENAME VARCHAR(30), INTABLENAME VARCHAR(30)
)
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
  vCHAR_SQLSTATE CHAR(5);
  vSUCCESS       CHAR(5);
 BEGIN


  !!!RESOLVE EWI!!! /*** SSC-EWI-TD0004 - NOT SUPPORTED SQL EXCEPTION ON CONTINUE HANDLER ***/!!!

  DECLARE CONTINUE HANDLER FOR SQLSTATE 'UNSUPPORTED'
  BEGIN
   vCHAR_SQLSTATE := SQLCODE;
   vSUCCESS := SQLCODE;
  END;
  SELECT
   1;
 END;
$$;

Copy

Recommendations¶

Identify statements that might cause the exception and wrap them in a code block, similar to the examples shown in the Continue Handler Translation Reference.
For additional assistance, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0055¶

This Early Warning Issue (EWI) is no longer supported. Please see the SSC-FDM-TD0029 documentation for current information.

Format elements that depend on session parameters¶

The following Teradata format elements are mapped to Snowflake functions that require specific session parameter settings. To ensure consistent results between Teradata and Snowflake, you must configure these session parameters to match your Teradata settings:

D: Mapped to DAYOFWEEK function. Note that the results may differ between Teradata and Snowflake due to different default settings. Teradata uses Sunday as the first day of the week, while Snowflake uses Monday.
WW: Mapped to WEEK function. The results may differ due to different week calculation policies. Snowflake uses the ISO standard by default (first week must contain at least four days of January), while Teradata considers January 1st as the start of the first week.

To change session parameters, use the ALTER SESSION SET parameter_name = value command. For more details about available session parameters and their usage, visit this page.

Single parameter version of TO_CHAR¶

The TO_CHAR(Datetime) function with a single parameter uses default date and time formats defined in your session parameters. These parameters include:

TIMESTAMP_LTZ_OUTPUT_FORMAT
TIMESTAMP_NTZ_OUTPUT_FORMAT
TIMESTAMP_TZ_OUTPUT_FORMAT
TIME_OUTPUT_FORMAT

To ensure consistent behavior between Teradata and Snowflake, make sure these parameters match your Teradata settings.

When converting numeric values to strings using TO_CHAR(Numeric), Snowflake automatically uses either the TM9 or TME format to create a compact string representation. Since Teradata also creates compact number representations by default, no additional formatting is needed.

Example Code¶

Input Code:¶

select to_char(date '2008-09-13', 'DD/RM/YYYY');

select to_char(date '2010-10-20', 'DS');

select to_char(1255.495, 'SC9999.9999', 'nls_iso_currency = ''EUR''');

select to_char(45620);

Copy

Output Code:¶

SELECT
TO_CHAR(date '2008-09-13', 'DD/') || PUBLIC.ROMAN_NUMERALS_MONTH_UDF(date '2008-09-13') || TO_CHAR(date '2008-09-13', '/YYYY') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0055 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/!!!;

SELECT
TO_CHAR(date '2010-10-20', 'MM/DD/YYYY') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0055 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/!!!;

SELECT
PUBLIC.INSERT_CURRENCY_UDF(TO_CHAR(1255.495, 'S9999.0000'), 2, 'EUR') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0055 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/!!!;

SELECT
TO_CHAR(45620) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0055 - SNOWFLAKE SUPPORTED FORMATS FOR TO_CHAR DIFFER FROM TERADATA AND MAY FAIL OR HAVE DIFFERENT BEHAVIOR ***/!!!;

Copy

Recommendations¶

When working with Format Functions (FF), use DateTime types that match Teradata’s precision, or specify a precision in the format element to ensure consistent behavior.
For timezone formatting, ensure the first parameter is of type TIMESTAMP_TZ for consistent results. Note that Snowflake’s TIME data type does not support timezone information.
Configure session parameters to match Teradata’s default values to maintain consistent behavior.
For additional assistance, contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0082¶

Severity¶

Medium

Description¶

The Translate function with current encoding parameters cannot be used in Snowflake. During the translation process, this function will be commented out.

Example Code¶

Input Code:¶

SELECT Translate('abc' USING KANJISJIS_TO_LATIN);

Copy

Output Code¶

SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0082 - TRANSLATE FUNCTION USING KANJISJIS_TO_LATIN ENCODING IS NOT SUPPORTED ***/!!!
Translate('abc' USING KANJISJIS_TO_LATIN);

Copy

Recommendations¶

For additional support, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0020¶

This warning message is no longer in use. Please see SSC-EWI-0009 for current documentation.

Severity¶

Low

Description¶

Snowflake currently supports only 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 some regular expression features may not be supported in Snowflake. Important unsupported features include:

Lookahead
Lookbehind
Non-capturing groups

Example Code¶

Teradata:¶

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

Copy

Snowflake Scripting:¶

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

Copy

Recommendations¶

Review each regular expression pattern to identify if manual adjustments are needed. For more details about Snowflake’s regex capabilities and alternative options, check here.
For additional assistance, contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0060¶

Parts of the output code have been removed to make the example clearer and easier to follow.

Severity¶

Low

Description¶

The SnowConvert tool can transform the JSON_TABLE function, but it needs to know the specific column names that are being selected in the JSON_TABLE ON subquery to perform the transformation correctly.

This warning appears when column names are not explicitly specified in a subquery (such as when using SELECT *) and the system cannot find the table’s structure information. Without this information, the column names cannot be determined automatically.

To learn how to load JSON data into a table, please refer to our documentation page.

Example code¶

Input Code:¶

CREATE TABLE demo.Train (
    firstCol INT,
    jsonCol JSON(400),
    thirdCol VARCHAR(30)
);

SELECT * FROM JSON_TABLE
(ON (SELECT T.*
           FROM demo.Train T)
USING rowexpr('$.schools[*]')
               colexpr('[ {"jsonpath" : "$.name",
                           "type" : "CHAR(20)"},
                          {"jsonpath" : "$.type",
                           "type" : "VARCHAR(20)"}]')
)
AS JT;

SELECT * FROM JSON_TABLE
(ON (SELECT T.*
           FROM demo.missingTable T)
USING rowexpr('$.schools[*]')
               colexpr('[ {"jsonpath" : "$.name",
                           "type" : "CHAR(20)"},
                          {"jsonpath" : "$.type",
                           "type" : "VARCHAR(20)"}]')
)
AS JT;

Copy

Output Code:¶

CREATE OR REPLACE TABLE demo.Train (
    firstCol INT,
    jsonCol VARIANT,
    thirdCol VARCHAR(30)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "12/16/2024",  "domain": "test" }}'
;

SELECT
    * FROM
    (
        SELECT
            firstCol,
            rowexpr.value:name :: CHAR(20) AS Column_0,
            rowexpr.value:type :: VARCHAR(20) AS Column_1,
            thirdCol
        FROM
            demo.Train T,
            TABLE(FLATTEN(INPUT => jsonCol:schools)) rowexpr
    ) JT;

    SELECT
    * FROM
    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0060 - JSON_TABLE NOT TRANSFORMED, COLUMN NAMES COULD NOT BE RETRIEVED FROM SEMANTIC INFORMATION ***/!!! JSON_TABLE
   (ON (
        SELECT
            T.*
                  FROM
            demo.missingTable T)
   USING rowexpr('$.schools[*]')
                  colexpr('[ {"jsonpath" : "$.name",
                           "type" : "CHAR(20)"},
                          {"jsonpath" : "$.type",
                           "type" : "VARCHAR(20)"}]')
   )
   AS JT;

Copy

Recommendations¶

Make sure to include table definitions when providing code to SnowConvert. Without them, you’ll need to run the code again.
For additional assistance, contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0031¶

Severity¶

Low

Description¶

When comparing CHAR data types in Teradata and Snowflake, there’s an important difference to note. In Teradata, CHAR is fixed-length, which means the LIKE function attempts to match the entire column length, including trailing spaces. This can lead to unexpected results. In contrast, Snowflake’s CHAR type is variable-length, so the LIKE function only matches against the actual value stored, without considering trailing spaces. Here’s an example to demonstrate this behavior:

Example Code¶

Input:¶

CREATE TABLE table1
(
    col1 VARCHAR(36),
    col2 CHAR(36)
);

INSERT INTO table1 VALUES ('Gabriel', 'Gabriel');
INSERT INTO table1 VALUES ('Barnum', 'Barnum');
INSERT INTO table1 VALUES ('Sergio', 'Sergio');

SELECT col1 FROM table1 where col1 LIKE 'Barnum';
-- The result is a single row with 'Barnum'
SELECT col2 FROM table1 where col2 LIKE 'Barnum';
-- It does not return any row

Copy

Output:¶

CREATE OR REPLACE TABLE table1
(
    col1 VARCHAR(36),
    col2 CHAR(36)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

INSERT INTO table1
VALUES ('Gabriel', 'Gabriel');

INSERT INTO table1
VALUES ('Barnum', 'Barnum');

INSERT INTO table1
VALUES ('Sergio', 'Sergio');

SELECT
    col1 FROM
    table1
where col1 LIKE 'Barnum';
-- The result is a single row with 'Barnum'
    SELECT
    col2 FROM
    table1
    where
    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0031 - THE RESULT OF LIKE MAY DIFFER DUE TO CHAR TYPE HAVING A FIXED LENGTH IN TERADATA ***/!!! col2 LIKE 'Barnum';
    -- It does not return any row

Copy

Recommendations¶

For additional support, please contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0005¶

Severity¶

Critical

Description¶

The code was successfully converted, but it may not work as intended because this feature has not been fully implemented yet.

The warning notifies users that using this statement will result in functionality that differs from the original script.

Example source¶

BTEQ Input code:¶

.SET SIDETITLES ON

Copy

Python Output code:¶

#*** Generated code is based on the SnowConvert Python Helpers version 2.0.6 ***

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-EWI-TD0005 - THE STATEMENT WAS CONVERTED BUT ITS FUNCTIONALITY IS NOT IMPLEMENTED YET **
  Export.side_titles(True)
  snowconvert.helpers.quit_application()

if __name__ == "__main__":
  main()

Copy

Recommendations¶

For detailed information about BTEQ to Python translation, please see the translation spec of BTEQ to Python.
For additional support, contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0083¶

Parts of the output code have been removed to make it easier to understand.

Severity¶

Medium

Description¶

SnowConvert cannot process multiple complex SELECT statements simultaneously. This limitation exists because converting these statements requires mapping them to either Common Table Expressions (CTEs) or composite FROM clauses, which can result in compilation errors or infinite logical loops.

What do we consider a SELECT complex clause?¶

Statements that need to be mapped to a Common Table Expression (CTE) or a composite FROM clause, including NORMALIZE, EXPAND ON, and RESET WHEN functions.

Example Code¶

Input Code:¶

SELECT
   NORMALIZE emp_id,
   duration,
   dept_id,
   balance,
   (
     ROW_NUMBER() OVER (
       PARTITION BY emp_id
       ORDER BY
         dept_id RESET WHEN balance <= SUM(balance) OVER (
           PARTITION BY emp_id
           ORDER BY dept_id
           ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
         )
     ) -1
   ) AS balance_increase
FROM project
EXPAND ON duration AS bg BY ANCHOR ANCHOR_SECOND
ORDER BY 1, 2;

Copy

Output Code¶

// SnowConvert Helpers Code section is omitted.
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0083 - NOT ABLE TO TRANSFORM TWO OR MORE COMPLEX SELECT CLAUSES AT A TIME ***/!!!
NORMALIZE emp_id,
   duration,
   dept_id,
   balance,
   (
     ROW_NUMBER() OVER (
   PARTITION BY
      emp_id, new_dynamic_part
   ORDER BY
         dept_id
     ) -1
   ) AS balance_increase
FROM
   (
      SELECT
         emp_id,
         duration,
         dept_id,
         balance,
         previous_value,
         SUM(dynamic_part) OVER (
                 PARTITION BY emp_id
                 ORDER BY dept_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ) AS new_dynamic_part
      FROM
         (
            SELECT
               emp_id,
               duration,
               dept_id,
               balance,
               SUM(balance) OVER (
                       PARTITION BY emp_id
                       ORDER BY dept_id
                       ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
                     ) AS previous_value,
               (CASE
                  WHEN balance <= previous_value
                     THEN 1
                  ELSE 0
               END) AS dynamic_part
            FROM
               project
         )
   )
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0083 - NOT ABLE TO TRANSFORM TWO OR MORE COMPLEX SELECT CLAUSES AT A TIME ***/!!!
EXPAND ON duration AS bg BY ANCHOR ANCHOR_SECOND
ORDER BY 1, 2;

Copy

Recommendations¶

For additional support, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0070¶

This Early Warning Issue (EWI) is no longer supported. Please see the SSC-FDM-TD0030 documentation for current information.

Severity¶

Medium

Description¶

When SC replaces a Goto statement with a Label section, it automatically adds a return statement at the end of the section if one is not present. This ensures that the program’s execution flow remains the same as in the original code.

When a BTEQ Goto command is executed, all statements between the Goto command and its corresponding label are skipped. To prevent any unintended execution after reaching the label, you should include a return statement in the label section.

It’s important to note that when a Goto command is executed, it skips all statements until it reaches the matching Label. The program execution then continues from that Label. Any Label sections defined before the Goto command will never be executed.

Example Code¶

Input Code:¶

-- Additional Params: --scriptsTargetLanguage SnowScript
.LOGON dbc,dbc;
select 'STATEMENTS';
.GOTO LABEL_B
select 'IGNORED STATEMENTS';
.label LABEL_B
select 'LABEL_B STATEMENTS';

Copy

Output Code¶

EXECUTE IMMEDIATE
$$
  DECLARE
    STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
  BEGIN
    --.LOGON dbc,dbc
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'BTLogOn' NODE ***/!!!
    null;
    BEGIN
      SELECT
        'STATEMENTS';
      STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
    EXCEPTION
      WHEN OTHER THEN
        STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
    END;

    /*.label LABEL_B*/

    BEGIN
      SELECT
        'LABEL_B STATEMENTS';
      STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
    EXCEPTION
      WHEN OTHER THEN
        STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
    END;
    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0070 - A RETURN STATEMENT WAS ADDED AT THE END OF THE LABEL SECTION LABEL_B TO ENSURE THE SAME EXECUTION FLOW ***/!!!
    RETURN 0;
    BEGIN
      SELECT
        'IGNORED STATEMENTS';
      STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
    EXCEPTION
      WHEN OTHER THEN
        STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
    END;
    /*.label LABEL_B*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **

    BEGIN
      SELECT
        'LABEL_B STATEMENTS';
      STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
    EXCEPTION
      WHEN OTHER THEN
        STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
    END;
  END
$$

Copy

Recommendations¶

For additional support, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0001¶

Parts of the output code have been removed to make it easier to understand.

Severity¶

Low

Description¶

This warning appears when SnowConvert identifies recursive patterns in aliased expressions. In such cases, SnowConvert cannot perform the Forward Alias transformation, which is necessary for aliases to work properly in Snowflake.

A recursive alias occurs when two or more aliases reference each other in a circular pattern. This can happen directly, where Alias A references Alias B and Alias B references Alias A, or indirectly (transitively), where the circular reference occurs through multiple Medium aliases.

Example Code¶

Note: While Snowflake does not generally support recursive aliases, some basic cases are supported.

Note: Snowflake does not support recursive aliases in general. However, some basic cases are supported, as shown in the examples below.

The code below demonstrates successful execution in Snowflake after completing the migration process:

Teradata:¶

SELECT
    COL1 AS COL2,
    COL2 AS COL1
FROM
    TABLE_EXAMPLE;

Copy

Snowflake Scripting:¶

// SnowConvert Helpers Code section is omitted.
SELECT
    COL1 AS COL2,
    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0001 - 'COL1' HAS RECURSIVE REFERENCES. FORWARD ALIAS CONVERSION COULD NOT BE COMPLETED ***/!!!
    COL2 AS COL1
FROM
    TABLE_EXAMPLE;

Copy

The code example shown below will not execute properly:

Teradata:¶

SELECT
    A + B as C,
    COL2 + C AS A,
    COL3 AS B
FROM
    TABLE_EXAMPLE;

Copy

Snowflake Scripting:¶

// SnowConvert Helpers Code section is omitted.
SELECT
    !!!RESOLVE EWI!!! /*** SSC-EWI-TD0001 - 'A' HAS RECURSIVE REFERENCES. FORWARD ALIAS CONVERSION COULD NOT BE COMPLETED ***/!!!
    COL2 + C AS A,
    COL3 AS B,
    A + B as C
FROM
    TABLE_EXAMPLE;

Copy

Recommendations¶

Check your code for recursive forward aliases. The EWI identifies the first recursive alias, but there may be others in your code that need attention.
For additional assistance, contact us at snowconvert-support@snowflake.com

SSC-EWI-TD0087¶

This documentation is no longer maintained. Please refer to SSC-FDM-TD0026 for current information.

Parts of the code have been omitted to keep the example clear and concise.

Severity¶

Medium

Description¶

When replicating SQL IF statement functionality, developers often combine GOTO commands with IF and LABEL commands. These combinations can be directly converted into if, if-else, or if-elseif-else statements. In such cases, you should remove the GOTO commands to avoid their replacement with LABEL sections, as they become redundant.

Example Code¶

Input Code:¶

-- Additional Params: --scriptsTargetLanguage SnowScript
.If ActivityCount = 0 THEN .GOTO endIf
DROP TABLE TABLE1;
.Label endIf
SELECT A FROM TABLE1;

Copy

Output Code¶

EXECUTE IMMEDIATE
$$
  DECLARE
    STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
  BEGIN
    IF (NOT (STATUS_OBJECT['SQLROWCOUNT'] = 0)) THEN
      !!!RESOLVE EWI!!! /*** SSC-EWI-TD0087 - GOTO endIf WAS REMOVED DUE TO IF STATEMENT INVERSION ***/!!!

      BEGIN
        DROP TABLE TABLE1;
        STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
      EXCEPTION
        WHEN OTHER THEN
          STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
      END;
    END IF;
    /*.Label endIf*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **

    BEGIN
      SELECT
        A
      FROM
        TABLE1;
      STATUS_OBJECT := OBJECT_CONSTRUCT('SQLROWCOUNT', SQLROWCOUNT);
    EXCEPTION
      WHEN OTHER THEN
        STATUS_OBJECT := OBJECT_CONSTRUCT('SQLCODE', SQLCODE, 'SQLERRM', SQLERRM, 'SQLSTATE', SQLSTATE);
    END;
  END
$$

Copy

Recommendations¶

For additional support, please contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0025¶

Severity¶

Low

Description¶

This warning appears when you use a CAST function with an output format that Snowflake scripting does not support.

Code Example¶
Teradata:¶CREATE TABLE SAMPLE_TABLE
(
    VARCHAR_TYPE VARCHAR
);

REPLACE VIEW SAMPLE_VIEW
AS
SELECT
CAST(VARCHAR_TYPE AS FLOAT FORMAT 'ZZZ.ZZZZZ'),
CAST('01:02.030405' AS TIME(1) WITH TIME ZONE FORMAT 'MI:SS.S(6)'),
CAST('01-12-2020' AS DATE FORMAT 'dd-mm-yyyy')
FROM SAMPLE_TABLE;
Copy

Snowflake Scripting:¶CREATE OR REPLACE TABLE SAMPLE_TABLE (
    VARCHAR_TYPE VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

CREATE OR REPLACE VIEW SAMPLE_VIEW
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
AS
SELECT
    TO_NUMBER(VARCHAR_TYPE, '999.00000', 38, 10) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0025 - OUTPUT FORMAT 'ZZZ.ZZZZZ' NOT SUPPORTED. ***/!!!,
    TO_TIME('01:02.030405', 'MI:SS.FF6') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0025 - OUTPUT FORMAT 'MI:SS.S(6)' NOT SUPPORTED. ***/!!!,
    TO_DATE('01-12-2020', 'DD-MM-YYYY') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0025 - OUTPUT FORMAT 'dd-mm-yyyy' NOT SUPPORTED. ***/!!!
   FROM
    SAMPLE_TABLE;
Copy

Recommendations¶

Verify that the converted code functions the same way as the source code.
For additional assistance, contact our support team at snowconvert-support@snowflake.com

SSC-EWI-TD0040¶

Severity¶

Low

Description¶

The file format you provided is not supported by the system.

Example Code¶

Input:¶

CREATE TABLE T_2040
(
    C1 VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(50)',
    C2 VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(75)'
);

Copy

Output:¶

CREATE OR REPLACE TABLE T_2040
(
    C1 VARCHAR(255) COLLATE 'en-cs' FORMAT 'X(50)' !!!RESOLVE EWI!!! /*** SSC-EWI-TD0040 - FORMAT IN TABLE T_2040 NOT SUPPORTED ***/!!!,
    C2 VARCHAR(255) COLLATE 'en-cs' FORMAT 'X(75)' !!!RESOLVE EWI!!! /*** SSC-EWI-TD0040 - FORMAT IN TABLE T_2040 NOT SUPPORTED ***/!!!
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "06/17/2024" }}'
;

Copy

Recommendations¶

For additional support, please contact our support team at snowconvert-support@snowflake.com