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:

Feature	ANSI mode	Teradata 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.

Mode	Column constraint values	Teradata behavior	SnowConvert 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

Teradata

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

Snowflake

CREATE OR REPLACE TABLE databaseTest.employees (
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   department VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/26/2024" }}'
;

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

Query

SELECT *
FROM databaseTest.employees
WHERE first_name = 'GEorge ';

Output

first_name	last_name	department
George	Snow	Sales

Snowflake

Query

 SELECT
 *
FROM
 databaseTest.employees
WHERE
 RTRIM(UPPER(first_name)) = RTRIM(UPPER('GEorge '));

Output

first_name	last_name	department
George	Snow	Sales

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

Query

SELECT *
FROM databaseTest.employees
WHERE last_name = 'SNOW ';

Output

No results.

Snowflake

Query

SELECT
 *
FROM
 databaseTest.employees
WHERE
 RTRIM(last_name) = RTRIM('SNOW ');

Output

No results.

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

Query

SELECT *
FROM databaseTest.employees
WHERE first_name LIKE 'GeorgE';

Output

first_name	last_name	department
George	Snow	Sales

Snowflake

Query

SELECT *
FROM databaseTest.employees
WHERE first_name ILIKE 'GeorgE';

Output

first_name	last_name	department
George	Snow	Sales

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

Query

SELECT *
FROM databaseTest.employees
WHERE last_name LIKE 'Snow';

Output

first_name	last_name	department
George	Snow	Sales
Jonh	Snow	Engineering

Snowflake

Query

SELECT *
FROM databaseTest.employees
WHERE last_name LIKE 'Snow';

Output

first_name	last_name	department
George	Snow	Sales
Jonh	Snow	Engineering

HAVING clause

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

Teradata

Query

SELECT first_name
FROM databaseTest.employees
GROUP BY first_name
HAVING first_name = 'GeorgE';

Output

first_name
George

Snowflake

Query

SELECT first_name
FROM databaseTest.employees
GROUP BY first_name
HAVING RTRIM(UPPER(first_name)) = RTRIM(UPPER('GeorgE'));

Output

first_name
George

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

Teradata

Query

SELECT department
FROM databaseTest.employees
GROUP BY department
HAVING department = 'Sales';

Output

department
Sales

Snowflake

Query

SELECT department
FROM databaseTest.employees
GROUP BY department
HAVING RTRIM(department) = RTRIM('Sales');

Output

department
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

Query

SELECT *
FROM databaseTest.employees
WHERE first_name IN ('GeorgE');

Output

first_name	last_name	department
George	Snow	Sales

Snowflake

Query

SELECT *
FROM databaseTest.employees
WHERE RTRIM(UPPER(first_name)) IN (RTRIM(UPPER('GeorgE')));

Output

first_name	last_name	department
George	Snow	Sales

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

Query

SELECT *
FROM databaseTest.employees
WHERE department IN ('SaleS');

Output

Query produced no results.

Snowflake

Query

SELECT *
FROM databaseTest.employees
WHERE RTRIM(department) IN (RTRIM('SaleS'));

Output

Query produced no results.

CASE WHEN statement

Teradata

Query

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;

Output

first_name	last_name	department_full_name
George	Snow	The sales guy
Jonh	Snow	Other

Snowflake

Query

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;

Output

first_name	last_name	department_full_name
George	Snow	The sales guy
Jonh	Snow	Other

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

Teradata

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

Snowflake

CREATE OR REPLACE TABLE databaseTest.students (
   first_name VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/31/2024" }}'
;

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

Query

SELECT first_name
FROM databaseTest.students
GROUP BY first_name;

Output

first_name
George
George

Snowflake

Query

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

Output

first_name
George
George

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

Setup data

Teradata

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

Snowflake

CREATE OR REPLACE TABLE databaseTest.students (
   first_name VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/31/2024" }}'
;

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

Query

SELECT first_name
FROM databaseTest.students
GROUP BY first_name;

Output

first_name
GEORGE
GEorge
GeOrge
GeorGe
George
George

Snowflake

Query

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

Output

first_name
George
George
GEORGE
GeorGe
GeOrge
GEorge

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

Teradata

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

Snowflake

CREATE OR REPLACE TABLE databaseTest.employees (
   employee_id INTEGER NOT NULL,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   department VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "08/23/2024" }}'
;

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 OR REPLACE TABLE databaseTest.departments (
   department_id INTEGER NOT NULL,
   department_name VARCHAR(50),
   location VARCHAR(100),
      PRIMARY KEY (department_id)
  )
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "08/23/2024" }}'
;

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

Query

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;

Output

4,Name_D,Last_name_D,Sales
5,Name_E,Last_name_E,Finance
8,Name_H,Last_name_H,Sales
10,Name_J,Last_name_J,Finance

Snowflake

Query

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

Output

4,Name_D,Last_name_D,Sales
5,Name_E,Last_name_E,Finance
8,Name_H,Last_name_H,Sales
10,Name_J,Last_name_J,Finance

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'.

Teradata

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

Snowflake

CREATE OR REPLACE TABLE databaseTest.employees (
   employee_id INTEGER NOT NULL,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   department VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "08/23/2024" }}'
;

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 OR REPLACE TABLE databaseTest.departments (
   department_id INTEGER NOT NULL,
   department_name VARCHAR(50),
   location VARCHAR(100),
      PRIMARY KEY (department_id)
  )
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "08/23/2024" }}'
;

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

Query

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;

Output

5,Name_E,Last_name_E,Finance
10,Name_J,Last_name_J,Finance

Snowflake

Query

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

Output

5,Name_E,Last_name_E,Finance
10,Name_J,Last_name_J,Finance

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

Teradata

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

Snowflake

CREATE OR REPLACE TABLE databaseTest.workers (
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   department VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "08/07/2024" }}'
;

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

Query

SELECT department
FROM databaseTest.workers
ORDER BY department;

Output

department
Sales
SALES
SalEs
sales
Sales

Snowflake

Query

SELECT department
FROM databaseTest.workers
ORDER BY UPPER(department);

Output

department
Sales
SALES
SalEs
Sales
sales

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

Teradata

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

Snowflake

CREATE OR REPLACE TABLE databaseTest.workers (
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   department VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "08/07/2024" }}'
;

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

Query

SELECT department
FROM databaseTest.workers
ORDER BY department;

Output

department
Sales
SALES
SalEs
sales
Sales

Snowflake

Query

SELECT department
FROM databaseTest.workers
ORDER BY department;

Output

department
Sales
SALES
SalEs
Sales
sales

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

Teradata

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

Snowflake

CREATE OR REPLACE TABLE databaseTest.workers (
   first_name VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "08/07/2024" }}'
;

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.

Query

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;

Output

first_name
George
George

Snowflake

Column cast should be removed.

Query

SELECT first_name from databaseTest.workers;

Output

first_name
George
George

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

Teradata

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

Snowflake

CREATE OR REPLACE TABLE databaseTest.workers (
   first_name VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "08/08/2024" }}'
;

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.

Query

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;

Output

first_name
George
GEorge
GEORGE
George

Snowflake

Column cast should be removed.

Query

SELECT first_name from databaseTest.workers;

Output

first_name
George
George
GEorge
GEORGE

CAST to Operations or Clauses [Exceptional Case]

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

Setup data

Teradata

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

Snowflake

CREATE OR REPLACE TABLE databaseTest.workers (
   first_name VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "08/08/2024" }}'
;

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.

Query

SELECT * FROM databaseTest.workers WHERE first_name = 'GEorge   ' (CASESPECIFIC);

Output

No results.

Snowflake

Query

SELECT * FROM databaseTest.workers WHERE first_name = RTRIM(UPPER('GEorge   '));

Output

No results.

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

Setup data

Teradata

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

Snowflake

CREATE OR REPLACE TABLE databaseTest.workers (
   first_name VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "08/08/2024" }}'
;

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

Query

SELECT * FROM databaseTest.workers WHERE first_name = 'GEorge   ' (NOT CASESPECIFIC) ;

Output

first_name
GEorge

Snowflake

Query

SELECT * FROM databaseTest.workers WHERE first_name = RTRIM('GEorge   ');

Output

first_name
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

Teradata

CREATE TABLE databaseTest.workers (
   first_name VARCHAR(50) NOT CASESPECIFIC
);

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

Snowflake

CREATE OR REPLACE TABLE databaseTest.workers (
   first_name VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "08/08/2024" }}'
;

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

Query sample

Teradata

Query

SELECT *
FROM databaseTest.workers
WHERE first_name = '';

Output

1 rows with blanck spaces.

Snowflake

Query

SELECT *
FROM databaseTest.workers
WHERE RTRIM(UPPER(first_name)) = RTRIM(UPPER(''));

Output

1 rows with blanck spaces.

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

Setup data

Teradata

CREATE TABLE databaseTest.workers (
   first_name VARCHAR(50) CASESPECIFIC
);

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

Snowflake

CREATE OR REPLACE TABLE databaseTest.workers (
   first_name VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "08/08/2024" }}'
;

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

Query sample

Teradata

Query

SELECT *
FROM databaseTest.workers
WHERE first_name = '';

Output

1 rows with blanck spaces.

Snowflake

Query

SELECT *
FROM databaseTest.workers
WHERE RTRIM(first_name) = RTRIM('');

Output

1 rows with blanck spaces.

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