Snowflake Documentation Home

TERA Mode For Strings Comparison

This section defines the translation specification for string in Tera mode.

Description

Teradata modes description

The Teradata database has different modes for running queries: ANSI Mode (rules based on the ANSI SQL: 2011 specifications) and TERA mode (rules defined by Teradata). Please review the following Teradata documentation for more information.

Teradata mode for string

For strings, the Teradata mode works differently. As it is explained in the following table based on the Teradata documentation:

FeatureANSI modeTeradata mode

Default attribute for character comparisons

CASESPECIFIC

NOT CASESPECIFIC

Default TRIM behavior

TRIM(BOTH FROM)

TRIM(BOTH FROM)

Translation specification summary

For this feature, the intended translation is focused only on Tera Mode. Notice that ANSI mode is added here just for comparison.

ModeColumn constraint valuesTeradata behaviorSnowConvert expected behavior

ANSI Mode

CASESPECIFIC

CASESPECIFIC

No constraint added.

NOT CASESPECIFIC

CASESPECIFIC

Add COLLATE 'en-cs' in column definition.

Teradata Mode

CASESPECIFIC

CASESPECIFIC

In most cases, do not add COLLATE, and convert its usages of string comparison to RTRIM( expression )

NOT CASESPECIFIC

NOT CASESPECIFIC

In most cases, do not add COLLATE, and convert its usages of string comparison to RTRIM(UPPER( expression ))

Sample Source Patterns

Setup data

IN -> Teradata_01.sql
CREATE TABLE databaseTest.employees (
   first_name VARCHAR(50) NOT CASESPECIFIC,
   last_name VARCHAR(50) CASESPECIFIC,
   department VARCHAR(50) CASESPECIFIC
);

INSERT INTO databaseTest.employees(first_name, last_name, department) VALUES ('George', 'Snow', 'Sales');
INSERT INTO databaseTest.employees(first_name, last_name, department) VALUES ('Jonh', 'Snow', 'Engineering');

Evaluation operation

Sample 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode

This example demonstrates the usage of a column set up as NOT CASE SPECIFIC as it is a first_name column. Even when asking for the string 'GEorge', the query execution will retrieve results in Teradata because the case specification is not considered.

To emulate this scenario in Snowflake, there are implemented two functions: RTRIM(UPPER(string_evaluation)), UPPER is required in this scenario because the string does not review the case specification.

Teradata

IN -> Teradata_02.sql
SELECT *
FROM databaseTest.employees
WHERE first_name = 'GEorge ';

Snowflake

OUT -> Teradata_02.sql
 SELECT
 *
FROM
 databaseTest.employees
WHERE
 RTRIM(UPPER(first_name)) = RTRIM(UPPER('GEorge '));

Sample 2: Column constraint is CASESPECIFIC and database mode is TERA Mode

For this example, the column constraint is CASESPECIFIC, for which the example does not retrieve rows in Teradata because 'Snow' is not equal to 'SNOW'.

In Snowflake, the resulting migration points only to the use of the RTRIM function since the case specification is important.

Teradata

IN -> Teradata_03.sql
SELECT *
FROM databaseTest.employees
WHERE last_name = 'SNOW ';

Snowflake

OUT -> Teradata_03.sql
SELECT
 *
FROM
 databaseTest.employees
WHERE
 RTRIM(last_name) = RTRIM('SNOW ');

LIKE operation

This operation works differently from another one. Blank spaces must be the same quantity to retrieve information.

Sample 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode

This example is expected to display one row because the case specification is not relevant.

In Snowflake, the migration uses the ILIKE operation. This performs a case-insensitive comparison.

Teradata

IN -> Teradata_04.sql
SELECT *
FROM databaseTest.employees
WHERE first_name LIKE 'GeorgE';

Snowflake

OUT -> Teradata_04.sqlql
SELECT *
FROM databaseTest.employees
WHERE first_name ILIKE 'GeorgE';

Sample 2: Column constraint is CASESPECIFIC and database mode is TERA Mode

In this case, the case specification is important. The query is expected to show two rows in the results since there are two coincidences for the last name "Snow". If there is an upper case letter in the middle of the comparison, like "sNow', there would be zero rows in the result.

Teradata

IN -> Teradata_05.sql
SELECT *
FROM databaseTest.employees
WHERE last_name LIKE 'Snow';

Snowflake

OUT -> Teradata_05.sql
SELECT *
FROM databaseTest.employees
WHERE last_name LIKE 'Snow';

HAVING clause

Sample 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode

Teradata

IN -> Teradata_06.sql
SELECT first_name
FROM databaseTest.employees
GROUP BY first_name
HAVING first_name = 'GeorgE';

Snowflake

OUT -> Teradata_06.sql
SELECT first_name
FROM databaseTest.employees
GROUP BY first_name
HAVING RTRIM(UPPER(first_name)) = RTRIM(UPPER('GeorgE'));

Sample 2: Column constraint is CASESPECIFIC and database mode is TERA Mode

Teradata

IN -> Teradata_07.sql
SELECT department
FROM databaseTest.employees
GROUP BY department
HAVING department = 'Sales';

Snowflake

OUT -> Teradata_07.sql
SELECT department
FROM databaseTest.employees
GROUP BY department
HAVING RTRIM(department) = RTRIM('Sales');

IN Operation

Sample 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode

Since this example is NOT CASESPECIFIC, there has to be the RTRIM and UPPER functions in the translation to Snowflake. So, the results are the same in the Teradata database and Snowflake database.

Teradata

IN -> Teradata_08.sql
SELECT *
FROM databaseTest.employees
WHERE first_name IN ('GeorgE');

Snowflake

OUT -> Teradata_08.sql
SELECT *
FROM databaseTest.employees
WHERE RTRIM(UPPER(first_name)) IN (RTRIM(UPPER('GeorgE')));

Sample 2: Column constraint is CASESPECIFIC and database mode is TERA Mode

For this example, the usage of the UPPER function is not required since, in the Teradata database, the case specification is relevant to the results. It is expected to not have rows in the results because "Sales" is not the same as "SaleS".

Teradata

IN -> Teradata_09.sql
SELECT *
FROM databaseTest.employees
WHERE department IN ('SaleS');

Snowflake

OUT -> Teradata_09.sql
SELECT *
FROM databaseTest.employees
WHERE RTRIM(department) IN (RTRIM('SaleS'));

CASE WHEN statement

Teradata

IN -> Teradata_10.sql
SELECT first_name,
      last_name,
      CASE
          WHEN department = 'EngineerinG' THEN 'Information Technology'
          WHEN first_name = 'GeorgE' THEN 'The sales guy'
          ELSE 'Other'
      END AS department_full_name
FROM databaseTest.employees;

Snowflake

OUT -> Teradata_10.sql
SELECT first_name,
      last_name,
      CASE
          WHEN RTRIM(department) = RTRIM('EngineerinG') THEN 'Information Technology'
          WHEN RTRIM(UPPER(first_name)) = RTRIM(UPPER('GeorgE')) THEN 'The sales guy'
          ELSE 'Other'
      END AS department_full_name
FROM databaseTest.employees;

GROUP BY clause

To review this clauses, it is required to set up new and better delimited data to showcase the behavior of the statements.

Sample 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode

Setup data

IN -> Teradata_11.sql
CREATE TABLE databaseTest.students (
   first_name VARCHAR(50) NOT CASESPECIFIC
);

INSERT INTO databaseTest.students(first_name) VALUES ('George');
INSERT INTO databaseTest.students(first_name) VALUES ('   George');

About the column behavior

Notice that this sample does not allow to insert values with upper and lower case letters in the NOT CASE SPECIFIC column because it takes it as the same value. Because the column does not supervise the case specification, the 'GEORGE' and 'george' value are checked as the same information.

The following rows are taken as duplicated row error:

INSERT INTO databaseTest.students(first_name) VALUES ('GEORGE');
INSERT INTO databaseTest.students(first_name) VALUES ('GeorGe');
INSERT INTO databaseTest.students(first_name) VALUES ('George  ');
INSERT INTO databaseTest.students(first_name) VALUES ('GeOrge');
INSERT INTO databaseTest.students(first_name) VALUES ('GEorge');
INSERT INTO databaseTest.students(first_name) VALUES ('George');

Please review the insertion of data in Snowflake. Snowflake does allow the insertion of values as 'GEORGE' and 'georges' without showing errors because the case specification is not bound explicitly with the column.

Query sample

Notice that this output can differ. To ensure a functional equivalence, it is required to use the COLLATE expression.

Please review the SSC-EWI-TD0007 for more information.

Teradata

IN -> Teradata_12.sql
SELECT first_name
FROM databaseTest.students
GROUP BY first_name;

Snowflake

OUT -> Teradata_12.sql
SELECT
   department
FROM
   databaseTest.workers
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY department;

Sample 2: Column constraint is CASESPECIFIC and database mode is TERA Mode

Setup data

IN -> Teradata_13.sql
CREATE TABLE databaseTest.students (
   first_name VARCHAR(50) CASESPECIFIC
);

INSERT INTO databaseTest.students(first_name) VALUES ('George');
INSERT INTO databaseTest.students(first_name) VALUES ('   George');
INSERT INTO databaseTest.students(first_name) VALUES ('GEORGE');
INSERT INTO databaseTest.students(first_name) VALUES ('GeorGe');
INSERT INTO databaseTest.students(first_name) VALUES ('GeOrge');
INSERT INTO databaseTest.students(first_name) VALUES ('GEorge');

About the column behavior

For this example, the following value shows duplicated row error:

INSERT INTO databaseTest.students(first_name) VALUES ('George  ');

It is because, the blank spaces at the right are trimed so the value is taken as one already inserted.

Query sample

Notice that this output can differ. To ensure a functional equivalence, it is required to use the COLLATE expression.

Please review the SSC-EWI-TD0007 for more information.

Teradata

IN -> Teradata_14.sql
SELECT first_name
FROM databaseTest.students
GROUP BY first_name;

Snowflake

OUT -> Teradata_14.sql
SELECT
   department
FROM
   databaseTest.workers
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY department;

JOIN clause

Simple scenarios with evaluation operations are supported.

To review these clauses, new and better-delimited data must be set up to showcase the statements' behavior.

Sample 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode

Setup data

IN -> Teradata_11.sql

CREATE TABLE databaseTest.employees (
   employee_id INTEGER NOT NULL,
   first_name VARCHAR(50) NOT CASESPECIFIC,
   last_name VARCHAR(50) NOT CASESPECIFIC,
   department VARCHAR(50) NOT CASESPECIFIC
);

INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (1, 'Name_A', 'Last_name_A', 'IT');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (2, 'Name_B', 'Last_name_B', 'HR');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (3, 'Name_C', 'Last_name_C', 'IT');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (4, 'Name_D', 'Last_name_D', 'Sales');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (5, 'Name_E', 'Last_name_E', 'Finance');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (6, 'Name_F', 'Last_name_F', 'IT');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (7, 'Name_G', 'Last_name_G', 'HR');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (8, 'Name_H', 'Last_name_H', 'Sales');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (9, 'Name_I', 'Last_name_I', 'IT');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (10, 'Name_J', 'Last_name_J', 'Finance');


CREATE TABLE databaseTest.departments (
    department_id INTEGER NOT NULL,
    department_name VARCHAR(50) NOT CASESPECIFIC,
    location VARCHAR(100) NOT CASESPECIFIC,
    PRIMARY KEY (department_id)
);

INSERT INTO databaseTest.departments (department_id, department_name, location) VALUES (101, 'Information Technology', 'New York');
INSERT INTO databaseTest.departments (department_id, department_name, location) VALUES (102, 'Human Resources', 'Chicago');
INSERT INTO databaseTest.departments (department_id, department_name, location) VALUES (103, 'Sales', 'San Francisco');
INSERT INTO databaseTest.departments (department_id, department_name, location) VALUES (104, 'Finance', 'Boston');

Query sample

Teradata

IN -> Teradata_12.sql
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name
FROM
    databaseTest.employees e
JOIN
    databaseTest.departments d
ON
    e.department = d.department_name;

Snowflake

OUT -> Teradata_12.sql
SELECT
   e.employee_id,
   e.first_name,
   e.last_name,
   d.department_name
FROM
   databaseTest.employees e
JOIN
      databaseTest.departments d
ON UPPER(RTRIM(e.department)) = UPPER(RTRIM(d.department_name));

Sample 2: Column constraint is CASESPECIFIC and database mode is TERA Mode

Setup data

Notice that the departments table has the Sales name in upper case 'SALES'.

IN -> Teradata_13.sql
CREATE TABLE databaseTest.employees (
   employee_id INTEGER NOT NULL,
   first_name VARCHAR(50) NOT CASESPECIFIC,
   last_name VARCHAR(50) NOT CASESPECIFIC,
   department VARCHAR(50) CASESPECIFIC
);

INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (1, 'Name_A', 'Last_name_A', 'IT');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (2, 'Name_B', 'Last_name_B', 'HR');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (3, 'Name_C', 'Last_name_C', 'IT');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (4, 'Name_D', 'Last_name_D', 'Sales');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (5, 'Name_E', 'Last_name_E', 'Finance');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (6, 'Name_F', 'Last_name_F', 'IT');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (7, 'Name_G', 'Last_name_G', 'HR');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (8, 'Name_H', 'Last_name_H', 'Sales');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (9, 'Name_I', 'Last_name_I', 'IT');
INSERT INTO databaseTest.employees (employee_id, first_name, last_name, department) VALUES (10, 'Name_J', 'Last_name_J', 'Finance');

CREATE TABLE databaseTest.departments (
    department_id INTEGER NOT NULL,
    department_name VARCHAR(50) NOT CASESPECIFIC,
    location VARCHAR(100) NOT CASESPECIFIC,
    PRIMARY KEY (department_id)
);

INSERT INTO databaseTest.departments (department_id, department_name, location) VALUES (101, 'Information Technology', 'New York');
INSERT INTO databaseTest.departments (department_id, department_name, location) VALUES (102, 'Human Resources', 'Chicago');
INSERT INTO databaseTest.departments (department_id, department_name, location) VALUES (103, 'SALES', 'San Francisco');
INSERT INTO databaseTest.departments (department_id, department_name, location) VALUES (104, 'Finance', 'Boston');

Teradata

IN -> Teradata_14.sql
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name
FROM
    databaseTest.employees e
JOIN
    databaseTest.departments d
ON
    e.department = d.department_name;

Snowflake

OUT -> Teradata_14.sql
SELECT
   e.employee_id,
   e.first_name,
   e.last_name,
   d.department_name
FROM
   databaseTest.employees e
JOIN
      databaseTest.departments d
ON RTRIM(e.department) = RTRIM(d.department_name);

ORDER BY clause

Sample 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode

For this example, it is required to set up specific data to test the examples. The column that is being evaluated is the department column. Notice that with the NOT CASESPECIFIC constraint, it is possible to insert different data with different upper and lower cases.

The migrated code uses UPPER to emulate Teradata's NOT CASESPECIFIC behavior.

Setup data

IN -> Teradata_15.sql
CREATE TABLE databaseTest.workers (
   first_name VARCHAR(50) NOT CASESPECIFIC,
   last_name VARCHAR(50) CASESPECIFIC,
   department VARCHAR(50) NOT CASESPECIFIC
);
INSERT INTO databaseTest.workers(first_name, last_name, department) VALUES ('George', 'Snow', 'Sales   ');

INSERT INTO databaseTest.workers(first_name, last_name, department) VALUES ('George', 'Snow', '   Sales');

INSERT INTO databaseTest.workers(first_name, last_name, department) VALUES ('George1', 'Snow', 'SALES');

INSERT INTO databaseTest.workers(first_name, last_name, department) VALUES ('George2', 'Snow', 'SalEs');

INSERT INTO databaseTest.workers(first_name, last_name, department) VALUES ('George3', 'Snow', 'sales');

Query sample

Notice that this output can differ. In this example, the fourth and fifth rows are inverted.

Teradata

IN -> Teradata_14.sql
SELECT department
FROM databaseTest.workers
ORDER BY department;

Snowflake

OUT -> Teradata_14.sql
SELECT department
FROM databaseTest.workers
ORDER BY UPPER(department);

Sample 2: Column constraint is CASESPECIFIC and database mode is TERA Mode

Once again, focus on the department column which is the one presenting the sample behavior.

Setup data

IN -> Teradata_15.sql
CREATE TABLE databaseTest.workers (
   first_name VARCHAR(50) NOT CASESPECIFIC,
   last_name VARCHAR(50) CASESPECIFIC,
   department VARCHAR(50) CASESPECIFIC
);
INSERT INTO databaseTest.workers(first_name, last_name, department) VALUES ('George', 'Snow', 'Sales   ');

INSERT INTO databaseTest.workers(first_name, last_name, department) VALUES ('George', 'Snow', '   Sales');

INSERT INTO databaseTest.workers(first_name, last_name, department) VALUES ('George1', 'Snow', 'SALES');

INSERT INTO databaseTest.workers(first_name, last_name, department) VALUES ('George2', 'Snow', 'SalEs');

INSERT INTO databaseTest.workers(first_name, last_name, department) VALUES ('George3', 'Snow', 'sales');

Query sample

Notice that this output can differ. In this example, the fourth and fifth rows are inverted.

Teradata

IN -> Teradata_14.sql
SELECT department
FROM databaseTest.workers
ORDER BY department;

Snowflake

OUT -> Teradata_14.sql
SELECT department
FROM databaseTest.workers
ORDER BY department;

CAST to the column [Exceptional Case]

At insertion restriction are important for this case.

Sample cases:

SELECT col1 (NOT CASESPECIFIC) FROM TestTable;

SELECT col1 (CASESPECIFIC) FROM TestTable;

Sample 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode

Insertion values should not let insert the duplicated columns defined as: 'GEorge', 'GEORGE', 'George ', 'George '.

Setup data

IN -> Teradata_15.sql
CREATE TABLE databaseTest.workers (
   first_name VARCHAR(50) NOT CASESPECIFIC
);

INSERT INTO databaseTest.workers(first_name) VALUES ('George');
INSERT INTO databaseTest.workers(first_name) VALUES ('  George');

Query sample

Teradata

For all the following queries, the result is the same.

IN -> Teradata_14.sql
SELECT first_name (NOT CASESPECIFIC) from databaseTest.workers;
SELECT first_name (CASESPECIFIC) from databaseTest.workers;
SELECT first_name from databaseTest.workers;
SELECT * FROM databaseTest.workers;

Snowflake

Column cast should be removed.

OUT -> Teradata_14.sql
SELECT first_name from databaseTest.workers;

Sample 2: Column constraint is CASESPECIFIC and database mode is TERA Mode

The order in the output is different from Teradata but values are the same.

The insertion queries should accept data with upper and lower case as different values. The right white spaces are trimmed so, 'George ' and 'George ' are the same.

Setup data

IN -> Teradata_15.sql
CREATE TABLE databaseTest.workers (
   first_name VARCHAR(50) CASESPECIFIC
);

INSERT INTO databaseTest.workers(first_name) VALUES ('George');
INSERT INTO databaseTest.workers(first_name) VALUES ('  George');
INSERT INTO databaseTest.workers(first_name) VALUES ('GEorge');
INSERT INTO databaseTest.workers(first_name) VALUES ('GEORGE');

Query sample

Teradata

For all the following queries, the result is the same.

IN -> Teradata_14.sql
SELECT first_name (NOT CASESPECIFIC) from databaseTest.workers;
SELECT first_name (CASESPECIFIC) from databaseTest.workers;
SELECT first_name from databaseTest.workers;
SELECT * FROM databaseTest.workers;

Snowflake

Column cast should be removed.

OUT -> Teradata_14.sql
SELECT first_name from databaseTest.workers;

CAST to Operations or Clauses [Exceptional Case]

Sample 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode

Setup data

IN -> Teradata_15.sql
CREATE TABLE databaseTest.workers (
   first_name VARCHAR(50) NOT CASESPECIFIC
);

INSERT INTO databaseTest.workers(first_name) VALUES ('George');
INSERT INTO databaseTest.workers(first_name) VALUES ('  George');

Query sample

Teradata

The (CASESPECIFIC) overwrite the column constraint in the table definition.

IN -> Teradata_14.sql
SELECT * FROM databaseTest.workers WHERE first_name = 'GEorge   ' (CASESPECIFIC);

Snowflake

OUT -> Teradata_14.sql
SELECT * FROM databaseTest.workers WHERE first_name = RTRIM(UPPER('GEorge   '));

Sample 2: Column constraint is CASESPECIFIC and database mode is TERA Mode

Setup data

IN -> Teradata_15.sql
CREATE TABLE databaseTest.workers (
   first_name VARCHAR(50) CASESPECIFIC
);

INSERT INTO databaseTest.workers(first_name) VALUES ('George');
INSERT INTO databaseTest.workers(first_name) VALUES ('  George');
INSERT INTO databaseTest.workers(first_name) VALUES ('GEorge');
INSERT INTO databaseTest.workers(first_name) VALUES ('GEORGE');

Query sample

Teradata

IN -> Teradata_14.sql
SELECT * FROM databaseTest.workers WHERE first_name = 'GEorge   ' (NOT CASESPECIFIC) ;

Snowflake

OUT -> Teradata_14.sql
SELECT * FROM databaseTest.workers WHERE first_name = RTRIM('GEorge   ');

Blank spaces [Exceptional Case]

For blank spaces, the cases can be resolved as in the other cases described previously in this page.

Multiple blank spaces may be reviewed.

Sample 1: Column constraint is NOT CASESPECIFIC, database mode is TERA Mode and using equal operation

Setup data

IN -> Teradata_15.sql
CREATE TABLE databaseTest.workers (
   first_name VARCHAR(50) NOT CASESPECIFIC
);

INSERT INTO databaseTest.workers(first_name) VALUES (' ');

Query sample

Teradata

IN -> Teradata_14.sql
SELECT *
FROM databaseTest.workers
WHERE first_name = '';

Snowflake

OUT -> Teradata_14.sql
SELECT *
FROM databaseTest.workers
WHERE RTRIM(UPPER(first_name)) = RTRIM(UPPER(''));

Sample 2: Column constraint is CASESPECIFIC, database mode is TERA Mode and using equal operation

Setup data

IN -> Teradata_15.sql
CREATE TABLE databaseTest.workers (
   first_name VARCHAR(50) CASESPECIFIC
);

INSERT INTO databaseTest.workers(first_name) VALUES (' ');

Query sample

Teradata

IN -> Teradata_14.sql
SELECT *
FROM databaseTest.workers
WHERE first_name = '';

Snowflake

OUT -> Teradata_14.sql
SELECT *
FROM databaseTest.workers
WHERE RTRIM(first_name) = RTRIM('');

Known Issues

  1. there are some mode-specific SQL statement restrictions: BEGIN TRANSACTION, END TRANSACTION, COMMIT [WORK].

  2. Data insertion may differ in Snowflake since the case specification is not bound to the column declaration.

  3. GROUP BY may differ in order, but group the correct values.

  4. ORDER BY behaves differently in Snowflake.

  5. If a function has a TRIM() from the source code, this workaround will add the required functions to the source code. So, RTRIM will be applied to the TRIM() source function.

Related EWIs

SSC-EWI-TD0007: GROUP BY IS NOT EQUIVALENT IN TERADATA MODE

PreviousXMLAGGNextSQL to Snowflake Scripting (Procedures)

Last updated