SnowConvert: Teradata-Sitzungsmodi¶
Beschreibung der Teradata-Sitzungsmodi¶
Die Teradata-Datenbank verfügt über verschiedene Modi für die Ausführung von Abfragen: ANSI Modus (Regeln basierend auf den ANSI SQL: 2011 Spezifikationen) und TERA Modus (von Teradata definierte Regeln). Bitte lesen Sie die folgende Teradata-Dokumentation für weitere Informationen.
Teradata-Modus für informative Tabellen mit Zeichenfolgen¶
Bei Zeichenfolgen funktioniert der Teradata-Modus anders. Wie in der folgenden Tabelle, die auf der Teradata-Dokumentation basiert, erläutert wird:
| Feature | ANSI mode | Teradata mode |
| ------------------------------------------- | --------------- | ---------------- |
| Default attribute for character comparisons | CASESPECIFIC | NOT CASESPECIFIC |
| Default TRIM behavior | TRIM(BOTH FROM) | TRIM(BOTH FROM) |
Zusammenfassung der Übersetzungsspezifikation¶
| Mode | Column constraint values | Teradata behavior | SC 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 )) |
Verfügbare Optionen für Übersetzungsspezifikationen¶
ANSI Modus für den Vergleich von Zeichenfolgen - COLLATE¶
Beschreibung ¶
ANSI-Modus für den Vergleich von Zeichenfolgen und die Verwendung von COLLATE¶
Der Vergleich von Zeichenfolgen im Modus ANSI wendet die Einschränkung COLLATE wie erforderlich auf die Spalten oder Anweisungen an. Die Standardeinstellung für die Groß-/Kleinschreibung für die TRIM-Funktion ist zu berücksichtigt.
Beachten Sie, dass in Teradata die Standardeinstellung für die Groß-/Kleinschreibung „CASESPECIFIC“ lautet, was der Standardeinstellung in Snowflake „case-sensitive“ entspricht. Daher werden diese Fälle nicht mit COLLATE übersetzt, da dies redundant wäre.
Beispielhafte Quellcode-Muster ¶
Datenkonfiguration¶
CREATE TABLE employees (
employee_id INTEGER NOT NULL,
first_name VARCHAR(50) NOT CASESPECIFIC,
last_name VARCHAR(50) CASESPECIFIC,
department VARCHAR(50)
);
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (1, 'George', 'Snow', 'Sales');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (2, 'John', 'SNOW', 'Engineering');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (3, 'WIlle', 'SNOW', 'Human resources ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (4, 'Marco', 'SnoW ', 'EngineerinG');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (5, 'Mary', ' ', 'SaleS ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (6, 'GEORGE', ' ', 'sales ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (7, 'GEORGE ', ' ', 'salEs ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (8, ' GeorgE ', ' ', 'SalEs ');
INSERT INTO employees (employee_id, first_name, last_name, department) VALUES (9, 'JOHN', ' SnoW', 'IT');
INSERT INTO employees (employee_id, first_name, last_name, department) VALUES (10, 'JOHN ', 'snow', 'Finance ');
CREATE TABLE departments (
department_id INTEGER NOT NULL,
department_name VARCHAR(50) NOT CASESPECIFIC,
location VARCHAR(100) CASESPECIFIC,
PRIMARY KEY (department_id)
);
INSERT INTO departments (department_id, department_name, location) VALUES (101, 'Information Technology', 'New York');
INSERT INTO departments (department_id, department_name, location) VALUES (102, 'Human Resources', 'Chicago');
INSERT INTO departments (department_id, department_name, location) VALUES (103, 'Sales', 'San Francisco');
INSERT INTO departments (department_id, department_name, location) VALUES (104, 'Finance', 'Boston');
CREATE OR REPLACE TABLE employees (
employee_id INTEGER NOT NULL,
first_name VARCHAR(50) COLLATE 'en-ci',
last_name VARCHAR(50),
department VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "teradata", "convertedOn": "11/01/2024", "domain": "test" }}'
;
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (1, 'George', 'Snow', 'Sales');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (2, 'John', 'SNOW', 'Engineering');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (3, 'WIlle', 'SNOW', 'Human resources ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (4, 'Marco', 'SnoW ', 'EngineerinG');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (5, 'Mary', ' ', 'SaleS ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (6, 'GEORGE', ' ', 'sales ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (7, 'GEORGE ', ' ', 'salEs ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (8, ' GeorgE ', ' ', 'SalEs ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (9, 'JOHN', ' SnoW', 'IT');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (10, 'JOHN ', 'snow', 'Finance ');
CREATE OR REPLACE TABLE departments (
department_id INTEGER NOT NULL,
department_name VARCHAR(50) COLLATE 'en-ci',
location VARCHAR(100),
PRIMARY KEY (department_id)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "teradata", "convertedOn": "11/01/2024", "domain": "test" }}'
;
INSERT INTO departments (department_id, department_name, location)
VALUES (101, 'Information Technology', 'New York');
INSERT INTO departments (department_id, department_name, location)
VALUES (102, 'Human Resources', 'Chicago');
INSERT INTO departments (department_id, department_name, location)
VALUES (103, 'Sales', 'San Francisco');
INSERT INTO departments (department_id, department_name, location)
VALUES (104, 'Finance', 'Boston');
Vergleichsoperation¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE first_name = 'GEorge ';
```none
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
COLLATE(first_name, 'en-cs-rtrim') = RTRIM('George');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE last_name = 'SNOW ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) = RTRIM('SNOW ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Fall 3: CAST NOT CASESPECIFIC-Spalte zu CASESPECIFIC und Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT * FROM employees WHERE first_name = 'George ' (CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
Bemerkung
COLLATE ‚en-cs‘ ist für die funktionale Gleichwertigkeit erforderlich.
SELECT
*
FROM
employees
WHERE
COLLATE(first_name, 'en-cs-rtrim') = 'George ' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Fall 4: CAST CASESPECIFIC-Spalte zu NOT CASESPECIFIC und Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT * FROM employees WHERE first_name = 'GEorge ' (NOT CASESPECIFIC) ;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
| 7 | GEORGE | | salEs |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) = RTRIM('GEorge ' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
| 7 | GEORGE | | salEs |
Fall 5: CAST NOT CASESPECIFIC-Spalte zu NOT CASESPECIFIC und Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT * FROM employees WHERE first_name (NOT CASESPECIFIC) = 'George ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
Bemerkung
COLLATE ist erforderlich.
SELECT
*
FROM
employees
WHERE
COLLATE(first_name /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/, 'en-cs-rtrim') = 'George ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
LIKE-Operation¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'George';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
SELECT *
FROM employees
WHERE COLLATE(first_name, 'en-cs-rtrim') LIKE 'George';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
Snowflake¶
SELECT *
FROM employees
WHERE RTRIM(last_name) LIKE RTRIM('Snow');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
Fall 3: CAST NOT CASESPECIFIC-Spalte zu CASESPECIFIC und Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'Mary' (CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 5 | Mary | | SaleS |
Snowflake¶
SELECT
*
FROM
employees
WHERE
COLLATE(first_name, 'en-cs-rtrim') LIKE 'Mary' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 5 | Mary | | SaleS |
Fall 4: CAST CASESPECIFC-Spalte zu NOT CASESPECIFIC und Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE last_name LIKE 'SNO%' (NOT CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) LIKE RTRIM('SNO%' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
IN-Operation¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE first_name IN ('George ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
Bemerkung
Dieser Fall erfordert COLLATE(_ column_name_ , 'en-cs-rtrim')
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) IN (COLLATE('George ', 'en-cs-rtrim'));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
Bemerkung
In diesem Fall hat die Spalte keine Spalteneinschränkung, aber die Standardbeschränkung im Teradata ANSI-Modus ist CASESPECIFIC.
SELECT *
FROM employees
WHERE department IN ('EngineerinG ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ----------- |
| 4 | Marco | SnoW | EngineerinG |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(department) IN (RTRIM('EngineerinG '));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ----------- |
| 4 | Marco | SnoW | EngineerinG |
ORDER BY-Klausel.¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT first_name
FROM employees
ORDER BY first_name;
| first\_name |
| ------------ |
| GeorgE |
| GEORGE |
| GEORGE |
| **George** |
| John |
| JOHN |
| JOHN |
| Marco |
| Mary |
| WIlle |
Snowflake¶
Warnung
Bitte überprüfen Sie FDM.Pending to add. _
SELECT
first_name
FROM
employees
ORDER BY first_name;
| first\_name |
| ------------ |
| GeorgE |
| **George** |
| GEORGE |
| GEORGE |
| John |
| JOHN |
| JOHN |
| Marco |
| Mary |
| WIlle |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT last_name
FROM employees
ORDER BY last_name;
| department |
| --------------- |
| EngineerinG |
| Engineering |
| Finance |
| Human resources |
| IT |
| SalEs |
| SaleS |
| Sales |
| salEs |
| sales |
Snowflake¶
SELECT
last_name
FROM
employees
ORDER BY last_name;
| department |
| --------------- |
| EngineerinG |
| Engineering |
| Finance |
| Human resources |
| IT |
| SalEs |
| SaleS |
| Sales |
| salEs |
| sales |
GROUP BY-Klausel.¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT first_name
FROM employees
GROUP BY first_name;
| first\_name |
| ------------ |
| Mary |
| GeorgE |
| WIlle |
| **JOHN** |
| Marco |
| GEORGE |
Snowflake¶
Warnung
Groß-/Kleinschreibung oder die Reihenfolge kann in der Ausgabe abweichen.
Bemerkung
RTRIM ist in ausgewählten Spalten erforderlich.
SELECT
first_name
FROM
employees
GROUP BY first_name;
| first\_name |
| ------------ |
| **John** |
| Marco |
| **George** |
| GeorgE |
| WIlle |
| Mary |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT last_name
FROM employees
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| |
| SNOW |
| SnoW |
| Snow |
| snow |
Snowflake¶
Bemerkung
Die Reihenfolge kann abweichen.
SELECT
last_name
FROM
employees
GROUP BY last_name;
| first\_name |
| ----------- |
| Snow |
| SNOW |
| SnoW |
| |
| SnoW |
| snow |
HAVING-Klausel.¶
Die HAVING-Klausel verwendet die Muster in:
Auswertungsoperationen.
Beispiel:
=, !=, <, >.
LIKE-Operation.
IN-Operation.
CAST zur Auswertungsoperation.
CAST zur LIKE-Operation.
Das folgende Beispiel zeigt ein Muster mit Auswertungsoperation.
Beispiel: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT first_name
FROM employees
GROUP BY first_name
HAVING first_name = 'Mary';
Mary
Snowflake¶
SELECT
first_name
FROM
employees
GROUP BY first_name
HAVING
COLLATE(first_name, 'en-cs-rtrim') = 'Mary';
Mary
CASEWHEN-Anweisung¶
Die Anweisung CASE WHEN verwendet die Muster, beschrieben in der
Auswertungsoperationen.
Beispiel:
=, !=, <, >.
LIKE-Operation.
IN-Operation.
CAST zur Auswertungsoperation.
CAST zur LIKE-Operation.
Das folgende Beispiel zeigt ein Muster mit Auswertungsoperation.
Teradata¶
SELECT first_name,
last_name,
CASE
WHEN department = 'EngineerinG' THEN 'Information Technology'
WHEN first_name = ' GeorgE ' THEN 'GLOBAL SALES'
ELSE 'Other'
END AS department_full_name
FROM employees
WHERE last_name = '';
| first\_name | last\_name | department\_full\_name |
| ------------- | ---------- | ---------------------- |
| GEORGE | | Other |
| Mary | | Other |
| GeorgE | | GLOBAL SALES |
| GEORGE | | Other |
Snowflake¶
SELECT
first_name,
last_name,
CASE
WHEN RTRIM(department) = RTRIM('EngineerinG')
THEN 'Information Technology'
WHEN COLLATE(first_name, 'en-cs-rtrim') = ' GeorgE '
THEN 'GLOBAL SALES'
ELSE 'Other'
END AS department_full_name
FROM
employees
WHERE RTRIM(last_name) = RTRIM('');
| first\_name | last\_name | department\_full\_name |
| ------------- | ---------- | ---------------------- |
| Mary | | Other |
| GEORGE | | Other |
| GEORGE | | Other |
| GeorgE | | GLOBAL SALES |
JOIN-Klausel.¶
Warnung
Einfache Szenarien mit Auswertungsoperationen werden unterstützt.
Die Anweisung JOIN verwendet die
Auswertungsoperationen.
Beispiel:
=, !=, <, >.
LIKE-Operation.
IN-Operation.
CAST zur Auswertungsoperation.
CAST zur LIKE-Operation.
Das folgende Beispiel zeigt ein Muster mit Auswertungsoperation.
Beispiel: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name
FROM
employees e
JOIN
departments d
ON
e.department = d.department_name;
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ----------- | ---------- | ---------------- |
| 1 | George | Snow | Sales |
| 10 | JOHN | snow | Finance |
Snowflake¶
Bemerkung
d.department_name ist NOT CASESPECIFIC und erfordert daher COLLATE.
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name
FROM
employees e
JOIN
departments d
ON COLLATE(e.department, 'en-cs-rtrim') = d.department_name;
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ----------- | ---------- | ---------------- |
| 1 | George | Snow | Sales |
| 10 | JOHN | snow | Finance |
ANSI Modus für den Vergleich von Zeichenfolgen - NO COLLATE¶
Beschreibung ¶
ANSI-Modus für den Vergleich von Zeichenfolgen und die Verwendung von NO COLATE.¶
Der Vergleich von Zeichenfolgen im Modus ANSI ohne die Verwendung von COLLATE wendet RTRIM und UPPER nach Bedarf an. Die Standardeinstellung für die Groß-/Kleinschreibung für die TRIM-Funktion ist zu berücksichtigt, d. h. wenn eine Spalte im Teradata ANSI-Modus keine Einstellung für die Groß-/Kleinschreibung hat, wird Teradata als Standard CASESPECIFIC verwenden.
Beispielhafte Quellcode-Muster ¶
Datenkonfiguration¶
CREATE TABLE employees (
employee_id INTEGER NOT NULL,
first_name VARCHAR(50) NOT CASESPECIFIC,
last_name VARCHAR(50) CASESPECIFIC,
department VARCHAR(50)
);
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (1, 'George', 'Snow', 'Sales');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (2, 'John', 'SNOW', 'Engineering');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (3, 'WIlle', 'SNOW', 'Human resources ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (4, 'Marco', 'SnoW ', 'EngineerinG');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (5, 'Mary', ' ', 'SaleS ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (6, 'GEORGE', ' ', 'sales ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (7, 'GEORGE ', ' ', 'salEs ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (8, ' GeorgE ', ' ', 'SalEs ');
INSERT INTO employees (employee_id, first_name, last_name, department) VALUES (9, 'JOHN', ' SnoW', 'IT');
INSERT INTO employees (employee_id, first_name, last_name, department) VALUES (10, 'JOHN ', 'snow', 'Finance ');
CREATE TABLE departments (
department_id INTEGER NOT NULL,
department_name VARCHAR(50) NOT CASESPECIFIC,
location VARCHAR(100) CASESPECIFIC,
PRIMARY KEY (department_id)
);
INSERT INTO departments (department_id, department_name, location) VALUES (101, 'Information Technology', 'New York');
INSERT INTO departments (department_id, department_name, location) VALUES (102, 'Human Resources', 'Chicago');
INSERT INTO departments (department_id, department_name, location) VALUES (103, 'Sales', 'San Francisco');
INSERT INTO departments (department_id, department_name, location) VALUES (104, 'Finance', 'Boston');
CREATE OR REPLACE TABLE 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": "10/30/2024", "domain": "test" }}'
;
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (1, 'George', 'Snow', 'Sales');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (2, 'John', 'SNOW', 'Engineering');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (3, 'WIlle', 'SNOW', 'Human resources ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (4, 'Marco', 'SnoW ', 'EngineerinG');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (5, 'Mary', ' ', 'SaleS ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (6, 'GEORGE', ' ', 'sales ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (7, 'GEORGE ', ' ', 'salEs ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (8, ' GeorgE ', ' ', 'SalEs ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (9, 'JOHN', ' SnoW', 'IT');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (10, 'JOHN ', 'snow', 'Finance ');
CREATE OR REPLACE TABLE 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": "10/30/2024", "domain": "test" }}'
;
INSERT INTO departments (department_id, department_name, location)
VALUES (101, 'Information Technology', 'New York');
INSERT INTO departments (department_id, department_name, location)
VALUES (102, 'Human Resources', 'Chicago');
INSERT INTO departments (department_id, department_name, location)
VALUES (103, 'Sales', 'San Francisco');
INSERT INTO departments (department_id, department_name, location)
VALUES (104, 'Finance', 'Boston');
Vergleichsoperation¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE first_name = 'George ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) = RTRIM('George ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE last_name = 'SNOW ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) = RTRIM('SNOW ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Fall 3: CAST NOT CASESPECIFIC-Spalte zu CASESPECIFIC und Datenbankmodus ist ANSI-Modus¶
Warnung
Die (CASESPECIFIC) überschreiben die Spalteneinschränkung in der Tabellendefinition.
Teradata¶
SELECT * FROM employees WHERE first_name = 'GEorge ' (CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT * FROM workers
WHERE RTRIM(first_name) = RTRIM(UPPER('GEorge '));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 6 | GEORGE | | sales |
Fall 4: CAST CASESPECIFIC-Spalte zu NOT CASESPECIFIC und Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT * FROM employees
WHERE last_name = 'SnoW ' (NOT CASESPECIFIC) ;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ----------- |
| 4 | Marco | SnoW | EngineerinG |
Snowflake¶
SELECT * FROM employees
WHERE RTRIM(last_name) = RTRIM('SnoW ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ----------- |
| 4 | Marco | SnoW | EngineerinG |
LIKE-Operation¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'Georg%';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
SELECT *
FROM employees
WHERE first_name LIKE 'Georg%';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Fall 3: CAST NOT CASESPECIFIC-Spalte zu NOT CASESPECIFIC und Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'George' (NOT CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
first_name ILIKE 'George' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Fall 4: CAST CASESPECIFIC-Spalte zu NOT CASESPECIFIC und Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE last_name LIKE 'SNO%' (NOT CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
Snowflake¶
SELECT
*
FROM
employees
WHERE
last_name LIKE 'SNO%' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
IN-Operation¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE first_name IN ('GEORGE ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 6 | GEORGE | | sales |
| 7 | GEORGE | | salEs |
Snowflake¶
SELECT *
FROM employees
WHERE RTRIM(first_name) IN (RTRIM('GEORGE '));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 6 | GEORGE | | sales |
| 7 | GEORGE | | salEs |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE department IN ('SaleS');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 5 | Mary | | SaleS |
Snowflake¶
SELECT *
FROM employees
WHERE RTRIM(department) IN (RTRIM('SaleS'));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 5 | Mary | | SaleS |
ORDER BY-Klausel.¶
Bemerkung
Beachten Sie, dass diese Funktionsäquivalenz unterschiedlich sein kann.
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT department_name
FROM departments
ORDER BY department_name;
| department |
| --------------- |
| EngineerinG |
| Engineering |
| Finance |
| Human resources |
| IT |
| SalEs |
| SaleS |
| Sales |
| salEs |
| sales |
Snowflake¶
Bemerkung
Bitte beachten Sie FDM. Die Reihenfolge unterscheidet sich in der Reihenfolge, in der die Daten eingefügt werden.
SELECT
department_name
FROM
departments
ORDER BY
UPPER(department_name);
| department |
| --------------- |
| EngineerinG |
| Engineering |
| Finance |
| Human resources |
| IT |
| SalEs |
| SaleS |
| Sales |
| salEs |
| sales |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT last_name
FROM employees
ORDER BY last_name;
| department |
| ---------------------- |
| Finance |
| Human Resources |
| Information Technology |
| Sales |
Snowflake¶
SELECT last_name
FROM employees
ORDER BY last_name;
| department |
| ---------------------- |
| Finance |
| Human Resources |
| Information Technology |
| Sales |
GROUP BY-Klausel.¶
Warnung
Um eine Funktionsäquivalenz zu gewährleisten, ist es erforderlich, den Ausdruck COLLATE zu verwenden.
Bitte lesen Sie die [SSC-EWI-TD0007](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/teradataFDM. md#ssc-fdm-td0007) für weitere Informationen.
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT first_name
FROM employees
GROUP BY first_name;
| first\_name |
| ------------- |
| Mary |
| GeorgE |
| WIlle |
| John |
| Marco |
| GEORGE |
Snowflake¶
SELECT
first_name
FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY first_name;
| FIRST\_NAME |
| ------------- |
| George |
| John |
| WIlle |
| Marco |
| Mary |
| GEORGE |
| GEORGE |
| GeorgE |
| JOHN |
| JOHN |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT last_name
FROM employees
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| |
| SNOW |
| SnoW |
| Snow |
| snow |
Snowflake¶
SELECT
last_name
FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| |
| SNOW |
| SnoW |
| Snow |
| snow |
HAVING-Klausel.¶
Die HAVING-Klausel verwendet die Muster in:
Auswertungsoperationen.
Beispiel:
=, !=, <, >.
LIKE-Operation.
IN-Operation.
CAST zur Auswertungsoperation.
CAST zur LIKE-Operation.
Das folgende Beispiel zeigt ein Muster mit Auswertungsoperation.
Beispiel: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT first_name
FROM employees
GROUP BY first_name
HAVING first_name = 'GEORGE';
GEORGE
Snowflake¶
SELECT
first_name
FROM
employees
GROUP BY first_name
HAVING
RTRIM(first_name) = RTRIM('GEORGE');
GEORGE
CASEWHEN-Anweisung¶
Die Anweisung CASE WHEN verwendet die Muster, beschrieben in der
Auswertungsoperationen.
Beispiel:
=, !=, <, >.
LIKE-Operation.
IN-Operation.
CAST zur Auswertungsoperation.
CAST zur LIKE-Operation.
Das folgende Beispiel zeigt ein Muster mit Auswertungsoperation.
Teradata¶
SELECT first_name,
last_name,
CASE
WHEN department = 'SaleS ' THEN 'GLOBAL SALES'
WHEN first_name = 'GEORGE ' THEN 'Department Full Name'
ELSE 'Other'
END AS department_full_name
FROM employees
WHERE last_name = ' ';
| first_name | last_name | department_full_name |
|---|---|---|
| GEORGE | Department Full Name | |
| Mary | GLOBAL SALES | |
| GeorgE | Other | |
| GEORGE | Department Full Name |
Snowflake¶
SELECT first_name,
last_name,
CASE
WHEN RTRIM(department) = RTRIM('SaleS ') THEN 'GLOBAL SALES'
WHEN RTRIM(first_name) = RTRIM('GEORGE ') THEN 'Department Full Name'
ELSE 'Other'
END AS department_full_name
FROM employees
WHERE RTRIM(last_name) = RTRIM(' ');
| first_name | last_name | department_full_name |
|---|---|---|
| GEORGE | Department Full Name | |
| Mary | GLOBAL SALES | |
| GeorgE | Other | |
| GEORGE | Department Full Name |
JOIN-Klausel.¶
Warnung
Einfache Szenarien werden unterstützt.
Die Anweisung JOIN verwendet die
Auswertungsoperationen.
Beispiel:
=, !=, <, >.
LIKE-Operation.
IN-Operation.
CAST zur Auswertungsoperation.
CAST zur LIKE-Operation.
Das folgende Beispiel zeigt ein Muster mit Auswertungsoperation.
Beispiel: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist ANSI-Modus¶
Teradata¶
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name
FROM
employees e
JOIN
departments d
ON
e.department = d.department_name;
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ----------- | ---------- | ---------------- |
| 1 | George | Snow | Sales |
| 10 | JOHN | snow | Finance |
Snowflake¶
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name
FROM
employees e
JOIN
departments d
ON RTRIM(e.department) = RTRIM(d.department_name);
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ----------- | ---------- | ---------------- |
| 1 | George | Snow | Sales |
| 10 | JOHN | snow | Finance |
Zugehörige EWIs¶
[SSC-EWI-TD0007](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/teradataFDM. md#ssc-fdm-td0007): GROUP BY IS NOT EQUIVALENT IN TERADATA MODE
TERA Modus für den Vergleich von Zeichenfolgen - COLLATE¶
Beschreibung ¶
Tera-Modus für den Vergleich von Zeichenfolgen und die Verwendung von COLLATE¶
Der Vergleich von Zeichenfolgen im Tera-Modus wendet die Einschränkung COLLATE wie erforderlich auf die Spalten oder Anweisungen an. Die Standardeinstellung für die Groß-/Kleinschreibung für die TRIM-Funktion ist zu berücksichtigt. Die Standardeinstellung für Groß-/Kleinschreibung in Teradata für den Modus TERA ist NOT CASESPECIFIC. Die Spalten ohne Einstellung für die Groß-/Kleinschreibung haben also COLLATE('en-ci')-Beschränkungen.
Beispielhafte Quellcode-Muster ¶
Datenkonfiguration¶
CREATE TABLE employees (
employee_id INTEGER NOT NULL,
first_name VARCHAR(50) NOT CASESPECIFIC,
last_name VARCHAR(50) CASESPECIFIC,
department VARCHAR(50)
);
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (1, 'George', 'Snow', 'Sales');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (2, 'John', 'SNOW', 'Engineering');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (3, 'WIlle', 'SNOW', 'Human resources ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (4, 'Marco', 'SnoW ', 'EngineerinG');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (5, 'Mary', ' ', 'SaleS ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (6, 'GEORGE', ' ', 'sales ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (7, 'GEORGE ', ' ', 'salEs ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (8, ' GeorgE ', ' ', 'SalEs ');
INSERT INTO employees (employee_id, first_name, last_name, department) VALUES (9, 'JOHN', ' SnoW', 'IT');
INSERT INTO employees (employee_id, first_name, last_name, department) VALUES (10, 'JOHN ', 'snow', 'Finance ');
CREATE TABLE departments (
department_id INTEGER NOT NULL,
department_name VARCHAR(50) NOT CASESPECIFIC,
location VARCHAR(100) CASESPECIFIC,
PRIMARY KEY (department_id)
);
INSERT INTO departments (department_id, department_name, location) VALUES (101, 'Information Technology', 'New York');
INSERT INTO departments (department_id, department_name, location) VALUES (102, 'Human Resources', 'Chicago');
INSERT INTO departments (department_id, department_name, location) VALUES (103, 'Sales', 'San Francisco');
INSERT INTO departments (department_id, department_name, location) VALUES (104, 'Finance', 'Boston');
CREATE OR REPLACE TABLE employees (
employee_id INTEGER NOT NULL,
first_name VARCHAR(50) COLLATE 'en-ci',
last_name VARCHAR(50),
department VARCHAR(50) COLLATE 'en-ci'
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "teradata", "convertedOn": "11/01/2024", "domain": "test" }}'
;
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (1, 'George', 'Snow', 'Sales');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (2, 'John', 'SNOW', 'Engineering');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (3, 'WIlle', 'SNOW', 'Human resources ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (4, 'Marco', 'SnoW ', 'EngineerinG');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (5, 'Mary', ' ', 'SaleS ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (6, 'GEORGE', ' ', 'sales ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (7, 'GEORGE ', ' ', 'salEs ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (8, ' GeorgE ', ' ', 'SalEs ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (9, 'JOHN', ' SnoW', 'IT');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (10, 'JOHN ', 'snow', 'Finance ');
CREATE OR REPLACE TABLE departments (
department_id INTEGER NOT NULL,
department_name VARCHAR(50) COLLATE 'en-ci',
location VARCHAR(100),
PRIMARY KEY (department_id)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "teradata", "convertedOn": "11/01/2024", "domain": "test" }}'
;
INSERT INTO departments (department_id, department_name, location)
VALUES (101, 'Information Technology', 'New York');
INSERT INTO departments (department_id, department_name, location)
VALUES (102, 'Human Resources', 'Chicago');
INSERT INTO departments (department_id, department_name, location)
VALUES (103, 'Sales', 'San Francisco');
INSERT INTO departments (department_id, department_name, location)
VALUES (104, 'Finance', 'Boston');
Vergleichsoperation¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE first_name = 'GEorge ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) = RTRIM('GEorge ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE last_name = 'SNOW ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) = RTRIM('SNOW ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Fall 3: CAST NOT CASESPECIFIC-Spalte zu CASESPECIFIC und Datenbankmodus ist TERA-Modus¶
Bemerkung
Beachten Sie, dass die folgenden Abfragen
SELECT * FROM employees WHERE first_name = 'JOHN ' (CASESPECIFIC)SELECT * FROM employees WHERE first_name (CASESPECIFIC) = 'JOHN '
die gleichen Werte zurückgeben.
Teradata¶
SELECT * FROM employees WHERE first_name = 'JOHN ' (CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 9 | JOHN | SnoW | IT |
| 10 | JOHN | snow | Finance |
Snowflake¶
SELECT
*
FROM
employees
WHERE
COLLATE(first_name, 'en-cs-rtrim') = 'JOHN ' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 9 | JOHN | SnoW | IT |
| 10 | JOHN | snow | Finance |
Fall 4: CAST CASESPECIFIC-Spalte zu NOT CASESPECIFIC und Datenbankmodus ist TERA-Modus¶
Bemerkung
CAST zu einer Spalte auf der linken Seite des Vergleichs hat Priorität.
\ Zum Beispiel:
SELECT * FROM employees WHERE last_name (NOT CASESPECIFIC) = 'snoW';_gibt 5 Zeilen zurück. _SELECT * FROM employees WHERE last_name = 'snoW' (NOT CASESPECIFIC);_gibt 0 Zeilen mit dieser Datenkonfiguration zurück.
Teradata¶
SELECT * FROM employees WHERE last_name (NOT CASESPECIFIC) = 'snoW' ;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 1 | George | Snow | Sales |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
| 4 | Marco | SnoW | EngineerinG |
| 10 | JOHN | snow | Finance |
Snowflake¶
SELECT
*
FROM
employees
WHERE
COLLATE(last_name /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/, 'en-ci-rtrim') = 'snoW' ;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 1 | George | Snow | Sales |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
| 4 | Marco | SnoW | EngineerinG |
| 10 | JOHN | snow | Finance |
LIKE-Operation¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'GeorgE';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) LIKE RTRIM('GeorgE');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) LIKE RTRIM('Snow');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Fall 3: CAST NOT CASESPECIFIC-Spalte zu CASESPECIFIC und Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'George' (CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
COLLATE(first_name, 'en-cs-rtrim') LIKE 'George';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Fall 4: CAST CASESPECIFIC-Spalte zu NOT CASESPECIFIC und Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE last_name LIKE 'SNO%' (NOT CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) LIKE RTRIM('SNO%' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
IN-Operation¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE first_name IN ('George ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) IN (RTRIM('George '));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Fall 2: Die Spalteneinschränkung ist nicht definiert und der Datenbankmodus ist TERA-Modus¶
Bemerkung
Im Tera-Modus bedeutet eine nicht definierte Einstellung für die Groß-/Kleinschreibung NOT CASESPECIFIC.
Teradata¶
SELECT *
FROM employees
WHERE department IN ('Sales ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ------------ | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 5 | Mary | | SaleS |
| 6 | GEORGE | | sales |
| 7 | GEORGE | | salEs |
| 8 | GeorgE | | SalEs |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(department) IN (RTRIM('Sales '));
| employee\_id | first\_name | last\_name | department |
| ------------ | ------------ | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 5 | Mary | | SaleS |
| 6 | GEORGE | | sales |
| 7 | GEORGE | | salEs |
| 8 | GeorgE | | SalEs |
Fall 3: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE last_name IN ('SNOW ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) IN (RTRIM('SNOW '));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
ORDER BY-Klausel.¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT employee_id, first_name
FROM employees
ORDER BY employee_id, first_name;
| employee_id | first_name |
|---|---|
| 1 | George |
| 2 | John |
| 3 | WIlle |
| 4 | Marco |
| 5 | Mary |
| 6 | GEORGE |
| 7 | GEORGE |
| 8 | GeorgE |
| 9 | JOHN |
| 10 | JOHN |
Snowflake¶
SELECT employee_id, first_name
FROM employees
ORDER BY employee_id, first_name;
| employee_id | first_name |
|---|---|
| 1 | George |
| 2 | John |
| 3 | WIlle |
| 4 | Marco |
| 5 | Mary |
| 6 | GEORGE |
| 7 | GEORGE |
| 8 | GeorgE |
| 9 | JOHN |
| 10 | JOHN |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id, last_name;
| employee_id | last_name |
|---|---|
| 1 | Snow |
| 2 | SNOW |
| 3 | SNOW |
| 4 | SnoW |
| 5 | |
| 6 | |
| 7 | |
| 8 | |
| 9 | SnoW |
| 10 | snow |
Snowflake¶
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id, last_name;
| employee_id | last_name |
|---|---|
| 1 | Snow |
| 2 | SNOW |
| 3 | SNOW |
| 4 | SnoW |
| 5 | |
| 6 | |
| 7 | |
| 8 | |
| 9 | SnoW |
| 10 | snow |
GROUP BY-Klausel.¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT first_name
FROM employees
GROUP BY first_name;
| first\_name |
| ------------ |
| Mary |
| GeorgE |
| WIlle |
| **JOHN** |
| Marco |
| **GEORGE** |
Snowflake¶
Warnung
Die Einstellung für die Groß-/Kleinschreibung in der Ausgabe kann je nach Anzahl der ausgewählten Spalten variieren.
SELECT
first_name
FROM
employees
GROUP BY first_name;
| first\_name |
| ------------ |
| **John** |
| Marco |
| **George** |
| GeorgE |
| WIlle |
| Mary |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT last_name
FROM employees
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| |
| SNOW |
| SnoW |
| Snow |
| snow |
Snowflake¶
SELECT
last_name
FROM
employees
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| |
| SNOW |
| SnoW |
| Snow |
| snow |
HAVING-Klausel.¶
Die HAVING-Klausel verwendet die Muster in:
Auswertungsoperationen.
Beispiel:
=, !=, <, >.
LIKE-Operation.
IN-Operation.
CAST zur Auswertungsoperation.
CAST zur LIKE-Operation.
Das folgende Beispiel zeigt ein Muster mit Auswertungsoperation.
Beispiel: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
Bemerkung
Die Einstellung für die Groß-/Kleinschreibung in der Ausgabe kann je nach Anzahl der ausgewählten Spalten variieren. Dies hängt auch mit der GROUP BY-Klausel zusammen.
SELECT first_name
FROM employees
GROUP BY first_name
HAVING first_name = 'George ';
| employee_id | first_name |
|---|---|
| 7 | GEORGE |
| 1 | George |
| 6 | GEORGE |
Snowflake¶
SELECT
employee_id,
first_name
FROM
employees
GROUP BY employee_id, first_name
HAVING
RTRIM(first_name) = RTRIM('George ');
| employee_id | first_name |
|---|---|
| 7 | GEORGE |
| 1 | George |
| 6 | GEORGE |
CASEWHEN-Anweisung¶
Die Anweisung CASE WHEN verwendet die Muster, beschrieben in der
Auswertungsoperationen.
Beispiel:
=, !=, <, >.
LIKE-Operation.
IN-Operation.
CAST zur Auswertungsoperation.
CAST zur LIKE-Operation.
Das folgende Beispiel zeigt ein Muster mit Auswertungsoperation.
Teradata¶
SELECT first_name,
last_name,
CASE
WHEN department = 'Engineering' THEN 'Information Technology'
WHEN first_name = 'GeorgE' THEN 'GLOBAL SALES'
ELSE 'Other'
END AS department_full_name
FROM employees
WHERE last_name = '';
| first\_name | last\_name | department\_full\_name |
| ------------ | ---------- | ---------------------- |
| GEORGE | | GLOBAL SALES |
| Mary | | Other |
| GeorgE | | Other |
| GEORGE | | GLOBAL SALES |
Snowflake¶
SELECT
first_name,
last_name,
CASE
WHEN RTRIM(department) = RTRIM('Engineering')
THEN 'Information Technology'
WHEN RTRIM(first_name) = RTRIM('GeorgE')
THEN 'GLOBAL SALES'
ELSE 'Other'
END AS department_full_name
FROM
employees
WHERE
RTRIM( last_name) = RTRIM('');
| first\_name | last\_name | department\_full\_name |
| ------------ | ---------- | ---------------------- |
| GEORGE | | GLOBAL SALES |
| Mary | | Other |
| GeorgE | | Other |
| GEORGE | | GLOBAL SALES |
JOIN-Klausel.¶
Warnung
Einfache Szenarien mit Auswertungsoperationen werden unterstützt.
Die Anweisung JOIN verwendet die
EvaluaComparisonComparisontion-Operationen
Beispiel:
=, !=, <, >.
LIKE-Operation.
IN-Operation.
CAST zur Auswertungsoperation.
CAST zur LIKE-Operation.
Das folgende Beispiel zeigt ein Muster mit Auswertungsoperation.
Beispiel: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name
FROM
employees e
JOIN
departments d
ON
e.department = d.department_name;
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ------------ | ---------- | ---------------- |
| 1 | George | Snow | Sales |
| 3 | WIlle | SNOW | Human Resources |
| 5 | Mary | | Sales |
| 6 | GEORGE | | Sales |
| 7 | GEORGE | | Sales |
| 8 | GeorgE | | Sales |
| 10 | JOHN | snow | Finance |
Snowflake¶
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name
FROM
employees e
JOIN
departments d
ON RTRIM(e.department) = RTRIM(d.department_name);
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ------------ | ---------- | ---------------- |
| 1 | George | Snow | Sales |
| 3 | WIlle | SNOW | Human Resources |
| 5 | Mary | | Sales |
| 6 | GEORGE | | Sales |
| 7 | GEORGE | | Sales |
| 8 | GeorgE | | Sales |
| 10 | JOHN | snow | Finance |
Zugehörige EWIs¶
[SSC-EWI-TD0007](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/teradataFDM. md#ssc-fdm-td0007): GROUP BY REQUIRED COLLATE FOR CASE INSENSITIVE COLUMNS
[SC-FDM-TD0032](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/teradataFDM. md#ssc-fdm-td0032) : [NOT] CASESPECIFIC CLAUSE WAS REMOVED
TERA Modus für den Vergleich von Zeichenfolgen - NO COLLATE¶
Beschreibung ¶
Tera-Modus für den Vergleich von Zeichenfolgen und die Verwendung von NOCOLLATE¶
Der Vergleich von Zeichenfolgen im Tera-Modus ohne die Verwendung von COLLATE wendet RTRIM und UPPER nach Bedarf an. Die Standardeinstellung für die Groß-/Kleinschreibung für die TRIM-Funktion ist zu berücksichtigt.
Beispielhafte Quellcode-Muster ¶
Datenkonfiguration¶
CREATE TABLE employees (
employee_id INTEGER NOT NULL,
first_name VARCHAR(50) NOT CASESPECIFIC,
last_name VARCHAR(50) CASESPECIFIC,
department VARCHAR(50)
);
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (1, 'George', 'Snow', 'Sales');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (2, 'John', 'SNOW', 'Engineering');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (3, 'WIlle', 'SNOW', 'Human resources ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (4, 'Marco', 'SnoW ', 'EngineerinG');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (5, 'Mary', ' ', 'SaleS ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (6, 'GEORGE', ' ', 'sales ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (7, 'GEORGE ', ' ', 'salEs ');
INSERT INTO employees(employee_id, first_name, last_name, department) VALUES (8, ' GeorgE ', ' ', 'SalEs ');
INSERT INTO employees (employee_id, first_name, last_name, department) VALUES (9, 'JOHN', ' SnoW', 'IT');
INSERT INTO employees (employee_id, first_name, last_name, department) VALUES (10, 'JOHN ', 'snow', 'Finance ');
CREATE TABLE departments (
department_id INTEGER NOT NULL,
department_name VARCHAR(50) NOT CASESPECIFIC,
location VARCHAR(100) CASESPECIFIC,
PRIMARY KEY (department_id)
);
INSERT INTO departments (department_id, department_name, location) VALUES (101, 'Information Technology', 'New York');
INSERT INTO departments (department_id, department_name, location) VALUES (102, 'Human Resources', 'Chicago');
INSERT INTO departments (department_id, department_name, location) VALUES (103, 'Sales', 'San Francisco');
INSERT INTO departments (department_id, department_name, location) VALUES (104, 'Finance', 'Boston');
CREATE OR REPLACE TABLE 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": "10/30/2024", "domain": "test" }}'
;
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (1, 'George', 'Snow', 'Sales');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (2, 'John', 'SNOW', 'Engineering');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (3, 'WIlle', 'SNOW', 'Human resources ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (4, 'Marco', 'SnoW ', 'EngineerinG');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (5, 'Mary', ' ', 'SaleS ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (6, 'GEORGE', ' ', 'sales ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (7, 'GEORGE ', ' ', 'salEs ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (8, ' GeorgE ', ' ', 'SalEs ');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (9, 'JOHN', ' SnoW', 'IT');
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (10, 'JOHN ', 'snow', 'Finance ');
CREATE OR REPLACE TABLE 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": "10/30/2024", "domain": "test" }}'
;
INSERT INTO departments (department_id, department_name, location)
VALUES (101, 'Information Technology', 'New York');
INSERT INTO departments (department_id, department_name, location)
VALUES (102, 'Human Resources', 'Chicago');
INSERT INTO departments (department_id, department_name, location)
VALUES (103, 'Sales', 'San Francisco');
INSERT INTO departments (department_id, department_name, location)
VALUES (104, 'Finance', 'Boston');
Vergleichsoperation¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Dieses Beispiel zeigt die Verwendung einer Spalte, die als NOT CASESPECIFIC eingerichtet ist, da es sich um eine first_name Spalte handelt. Auch bei der Abfrage der Zeichenfolge 'GEorge', werden bei der Ausführung der Abfrage Ergebnisse in Teradata abgerufen, da die Einstellung für Groß-/Kleinschreibung nicht berücksichtigt wird.
Um dieses Szenario in Snowflake nachzubilden, sind zwei Funktionen implementiert: RTRIM(UPPER(string_evaluation)), UPPER ist in diesem Szenario erforderlich, da die Zeichenfolge die Einstellung für Groß-/Kleinschreibung nicht überprüft.
Teradata¶
SELECT *
FROM employees
WHERE first_name = 'GEorge ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(UPPER(first_name)) = RTRIM(UPPER('GEorge '));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
In diesem Beispiel ist die Spalteneinschränkung CASESPECIFIC, für die das Beispiel keine Zeilen in Teradata abruft, weil Snow nicht gleich SNOW ist.
In Snowflake weist die daraus resultierende Migration nur auf die Verwendung der Funktion RTRIM hin, da die Einstellung für Groß-/Kleinschreibung wichtig ist.
Teradata¶
SELECT *
FROM employees
WHERE last_name = 'SNOW ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) = RTRIM('SNOW ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Fall 3: CAST CASESPECIFIC-Spalte zu NOT CASESPECIFIC und Datenbankmodus ist TERA-Modus¶
Teradata¶
Warnung
(CASESPECIFIC) setzt die Spalteneinschränkung in der Tabellendefinition außer Kraft.
SELECT * FROM employees WHERE first_name = 'GEORGE ' (CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 6 | GEORGE | | sales |
Snowflake¶
Bemerkung
RTRIM ist auf der linken Seite erforderlich und RTRIM auf der rechten Seite.
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) = RTRIM('GEORGE ' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 6 | GEORGE | | sales |
Fall 4: CAST NOT CASESPECIFIC-Spalte zu NOT CASESPECIFIC und Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT * FROM employees WHERE first_name = 'GEorge ' (NOT CASESPECIFIC) ;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
UPPER(RTRIM(first_name)) = UPPER(RTRIM('GEorge ' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Fall 5: Leerzeichen. Die Spalteneinschränkung ist NOT CASESPECIFIC, der Datenbankmodus ist TERA-Modus und es wird die Gleichheitsoperation verwendet.¶
Teradata¶
SELECT *
FROM employees
WHERE last_name = ' ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ------------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 5 | Mary | | SaleS |
| 8 | GeorgE | | SalEs |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) = RTRIM(' ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ------------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 5 | Mary | | SaleS |
| 8 | GeorgE | | SalEs |
| 6 | GEORGE | | sales |
LIKE-Operation¶
Bemerkung
Diese Operation funktioniert anders als eine andere. Die Leerzeichen müssen die gleiche Anzahl haben, um Informationen abrufen zu können.
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
In diesem Beispiel wird voraussichtlich eine Zeile angezeigt, da die Einstellung für die Groß-/Kleinschreibung nicht relevant ist.
Bemerkung
In Snowflake verwendet die Migration die ILIKE-Operation. Dies führt einen Vergleich ohne Berücksichtigung der Groß- und Kleinschreibung durch.
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'GeorgE';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT *
FROM employees
WHERE first_name ILIKE 'GeorgE';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
| first\_name | last\_name | department |
| ----------- | ---------- | ----------- |
| George | Snow | Sales |
| Jonh | Snow | Engineering |
Snowflake¶
SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
| first\_name | last\_name | department |
| ----------- | ---------- | ----------- |
| George | Snow | Sales |
| Jonh | Snow | Engineering |
Fall 3: CAST CASESPECIFIC-Spalte zu NOT CASESPECIFIC und Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'George' (NOT CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
first_name ILIKE 'George' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Fall 4: CAST NOT CASESPECIFIC-Spalte zu NOT CASESPECIFIC und Datenbankmodus ist ANSI-Modus¶
Bemerkung
In diesem Fall ist die Übersetzung nach ILIKE erforderlich.
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'GE%' (NOT CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
first_name ILIKE 'GE%' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
IN-Operation¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT *
FROM employees
WHERE first_name IN ('GeorgE');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT *
FROM employees
WHERE RTRIM(UPPER(first_name)) IN (RTRIM(UPPER('GeorgE')));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Fall 2: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
In diesem Beispiel ist die Verwendung der Funktion UPPER nicht erforderlich, da in der Teradata-Datenbank die Fallangabe für die Ergebnisse relevant ist.
Teradata¶
SELECT *
FROM employees
WHERE last_name IN ('SnoW');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ----------- |
| 4 | Marco | SnoW | EngineerinG |
Snowflake¶
SELECT *
FROM employees
WHERE RTRIM(last_name) IN (RTRIM('SnoW'));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ----------- |
| 4 | Marco | SnoW | EngineerinG |
ORDER BY-Klausel.¶
Fall 1: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Beachten Sie, dass diese Ausgabereihenfolge unterschiedlich sein kann.
Teradata
SELECT department
FROM employees
ORDER BY department;
| department |
| --------------- |
| EngineerinG |
| Engineering |
| Finance |
| Human resources |
| IT |
| sales |
| SalEs |
| Sales |
| SaleS |
| salEs |
Snowflake
SELECT department
FROM employees
ORDER BY UPPER(department);
| department |
| --------------- |
| EngineerinG |
| Engineering |
| Finance |
| Human resources |
| IT |
| sales |
| SalEs |
| Sales |
| SaleS |
| salEs |
Case 2: Column constraint is CASESPECIFIC and database mode is TERA Mode
Beachten Sie, dass diese Ausgabe in der Reihenfolge abweichen kann.
Teradata¶
SELECT last_name
FROM employees
ORDER BY last_name;
| last\_name |
| ---------- |
| |
| |
| |
| |
| SnoW |
| SNOW |
| SNOW |
| SnoW |
| Snow |
| snow |
Snowflake¶
SELECT last_name
FROM employees
ORDER BY last_name;
| last\_name |
| ---------- |
| |
| |
| |
| |
| SnoW |
| SNOW |
| SNOW |
| SnoW |
| Snow |
| snow |
GROUP BY-Klausel.¶
Warnung
Beachten Sie, dass diese Ausgabe unterschiedlich ausfallen kann. Um eine Funktionsäquivalenz zu gewährleisten, ist es erforderlich, den Ausdruck COLLATE zu verwenden.
Bitte lesen Sie SSC-EWI-TD0007 für weitere Informationen.
_Das Folgende könnte eine Umgehungsmöglichkeit sein, ohne collate: _
SELECTRTRIM(UPPER(first_name))
FROM employees
GROUP BY RTRIM(UPPER(first_name));
Über die Verhaltensweise der Spalte
Bitte überprüfen Sie das Einfügen von Daten in Snowflake. Snowflake erlaubt das Einfügen von Werten wie GEORGE und georges, ohne Fehler anzuzeigen, da die Einstellung für die Groß-/Kleinschreibung nicht explizit mit der Spalte verbunden ist.
Nehmen Sie eine Tabelle und die folgenden Daten an:
CREATE TABLE students (
first_name VARCHAR(50) NOT CASESPECIFIC
);
INSERT INTO students(first_name) VALUES ('George');
INSERT INTO students(first_name) VALUES (' George');
Beachten Sie, dass in diesem Beispiel keine Werte mit Groß- und Kleinbuchstaben in die Spalte NOT CASESPECIFIC eingefügt werden können, da es sich um denselben Wert handelt. Da die Spalte die Einstellung für die Groß-/Kleinschreibung nicht überwacht, werden die Werte „GEORGE“ und „george“ als die gleiche Information geprüft.
Die folgenden Zeilen werden als duplizierte Zeilenfehler übernommen:
INSERT INTO students(first_name) VALUES ('GEORGE');
INSERT INTO students(first_name) VALUES ('GeorGe');
INSERT INTO students(first_name) VALUES ('George ');
INSERT INTO students(first_name) VALUES ('GeOrge');
INSERT INTO students(first_name) VALUES ('GEorge');
INSERT INTO students(first_name) VALUES ('George');
Case 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode
Teradata
SELECT first_name
FROM employees
GROUP BY first_name;
| first\_name |
| ------------ |
| Mary |
| GeorgE |
| WIlle |
| JOHN |
| Marco |
| GEORGE |
Snowflake
SELECT
first_name
FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY first_name;
| first\_name |
| ------------ |
| George |
| John |
| WIlle |
| Marco |
| Mary |
| GEORGE |
| GEORGE |
| GeorgE |
| JOHN |
| JOHN |
Case 2: Column constraint is CASESPECIFIC and database mode is TERA Mode
Teradata
SELECT last_name
FROM employees
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| |
| SNOW |
| SnoW |
| Snow |
| snow |
Snowflake
SELECT
last_name
FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| SNOW |
| SnoW |
| |
| |
| Snow |
| snow |
HAVING clause
Die HAVING-Klausel verwendet die Muster in:
Auswertungsoperationen.
Beispiel:
=, !=, <, >.
LIKE-Operation.
IN-Operation.
CAST zur Auswertungsoperation.
CAST zur LIKE-Operation.
Das folgende Beispiel zeigt ein Muster mit Auswertungsoperation.
Beispiel: Die Spalteneinschränkung ist CASESPECIFIC und der Datenbankmodus ist TERA-Modus
Teradata
SELECT last_name
FROM employees
GROUP BY last_name
HAVING last_name = 'Snow';
| last\_name |
| ---------- |
| Snow |
Snowflake
SELECT last_name
FROM employees
GROUP BY last_name
HAVING RTRIM(last_name) = RTRIM('Snow');
| last\_name |
| ---------- |
| Snow |
CASE WHEN statement
Die Anweisung CASE WHEN verwendet die Muster, beschrieben in der
Auswertungsoperationen.
Beispiel:
=, !=, <, >.
LIKE-Operation.
IN-Operation.
CAST zur Auswertungsoperation.
CAST zur LIKE-Operation.
Das folgende Beispiel zeigt ein Muster mit Auswertungsoperation.
Teradata
SELECT first_name,
last_name,
CASE
WHEN department = 'EngineerinG' THEN 'Information Technology'
WHEN last_name = 'SNOW' THEN 'GLOBAL COOL SALES'
ELSE 'Other'
END AS department_full_name
FROM employees;
| first\_name | last\_name | department\_full\_name |
| ------------ | ---------- | ---------------------- |
| GEORGE | | Other |
| JOHN | SnoW | Other |
| Mary | | Other |
| JOHN | snow | Other |
| WIlle | SNOW | GLOBAL COOL SALES |
| George | Snow | Other |
| GeorgE | | Other |
| GEORGE | | Other |
| Marco | SnoW | Information Technology |
| John | SNOW | Information Technology |
Snowflake
SELECT
first_name,
last_name,
CASE
WHEN UPPER(RTRIM(department)) = UPPER(RTRIM('EngineerinG'))
THEN 'Information Technology'
WHEN RTRIM(last_name) = RTRIM('SNOW')
THEN 'GLOBAL COOL SALES'
ELSE 'Other'
END AS department_full_name
FROM
employees;
| first\_name | last\_name | department\_full\_name |
| ------------ | ---------- | ---------------------- |
| GEORGE | | Other |
| JOHN | SnoW | Other |
| Mary | | Other |
| JOHN | snow | Other |
| WIlle | SNOW | GLOBAL COOL SALES |
| George | Snow | Other |
| GeorgE | | Other |
| GEORGE | | Other |
| Marco | SnoW | Information Technology |
| John | SNOW | Information Technology |
JOIN clause
Warnung
Einfache Szenarien werden unterstützt.
Die Anweisung JOIN verwendet die
Auswertungsoperationen.
Beispiel:
=, !=, <, >.
LIKE-Operation.
IN-Operation.
CAST zur Auswertungsoperation.
CAST zur LIKE-Operation.
Das folgende Beispiel zeigt ein Muster mit Auswertungsoperation.
Beispiel: Die Spalteneinschränkung ist NOT CASESPECIFIC und der Datenbankmodus ist TERA-Modus¶
Teradata¶
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name
FROM
employees e
JOIN
departments d
ON
e.department = d.department_name;
| employee_id | first_name | last_name | department_name |
|---|---|---|---|
| 1 | George | Snow | Sales |
| 3 | WIlle | SNOW | Human Resources |
| 5 | Mary | Sales | |
| 6 | GEORGE | Sales | |
| 7 | GEORGE | Sales | |
| 8 | GeorgE | Sales | |
| 10 | JOHN | snow | Finance |
Snowflake¶
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name
FROM
employees e
JOIN
departments d
ON UPPER(RTRIM(e.department)) = UPPER(RTRIM(d.department_name));
| employee_id | first_name | last_name | department_name |
|---|---|---|---|
| 1 | George | Snow | Sales |
| 3 | WIlle | SNOW | Human Resources |
| 5 | Mary | Sales | |
| 6 | GEORGE | Sales | |
| 7 | GEORGE | Sales | |
| 8 | GeorgE | Sales | |
| 10 | JOHN | snow | Finance |
Bekannte Probleme¶
Es gibt es einige modusspezifische Einschränkungen für SQL-Anweisungen:
BEGIN TRANSACTION,END TRANSACTION,COMMIT [WORK].Die Dateneinfügung kann in Snowflake abweichen, da die Einstellung für die Groß-/Kleinschreibung nicht an die Spaltendeklaration gebunden ist.
GROUP BYkann sich in der Reihenfolge unterscheiden, gruppiert jedoch die richtigen Werte.ORDER BYverhält sich in Snowflake anders.Wenn eine Funktion ein TRIM() aus dem Quellcode hat, fügt diese Umgehungsmöglichkeit die erforderlichen Funktionen zum Quellcode hinzu. RTRIM wird also auf die Quellfunktion TRIM() angewendet.
Zugehörige EWIs¶
[SSC-EWI-TD0007](../../general/technical-documentation/issues-and-troubleshooting/functional-difference/teradataFDM. md#ssc-fdm-td0007): GROUP BY IS NOT EQUIVALENT IN TERADATA MODE