SnowConvert : Modes de session Teradata¶
Description des modes de session Teradata¶
La base de données Teradata dispose de différents modes d’exécution des requêtes : Mode ANSI (règles basées sur les spécifications ANSI SQL : 2011) et mode TERA (règles définies par Teradata). Pour plus d’informations, veuillez consulter la documentation Teradata suivante.
Mode Teradata pour les tables d’informations sur les chaînes¶
Pour les chaînes, le mode Teradata fonctionne différemment. Comme l’explique la table suivante, basée sur la documentation Teradata :
| Feature | ANSI mode | Teradata mode |
| ------------------------------------------- | --------------- | ---------------- |
| Default attribute for character comparisons | CASESPECIFIC | NOT CASESPECIFIC |
| Default TRIM behavior | TRIM(BOTH FROM) | TRIM(BOTH FROM) |
Résumé des spécifications de traduction¶
| 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 )) |
Options de spécification de traduction disponibles¶
Mode ANSI pour la comparaison des chaînes - COLLATE¶
Description ¶
Mode ANSI pour la comparaison des chaînes et l’utilisation de COLLATE¶
La comparaison de chaînes en mode ANSI appliquera la contrainte COLLATE aux colonnes ou aux instructions selon les exigences. Le comportement de suppression des espaces par défaut dans la définition des cas peut être pris en compte.
Notez que dans Teradata, la spécification de casse par défaut est “CASESPECIFIC”, la même valeur par défaut que dans Snowflake “case-sensitive'. Ainsi, ces casses ne seront pas traduits avec un COLLATE car il sera redondant.
Échantillons de modèles de sources ¶
Données de configuration¶
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');
Opération de comparaison¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Cas 3 : La colonne CAST NOT CASESPECIFIC est CASESPECIFIC et le mode de la base de données est ANSI¶
Teradata¶
SELECT * FROM employees WHERE first_name = 'George ' (CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
Note
COLLATE “en-cs” est exigé pour l’équivalence fonctionnelle.
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 |
Cas 4 : La colonne CAST CASESPECIFIC est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Cas 5 : la colonne CAST NOT CASESPECIFIC est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
Teradata¶
SELECT * FROM employees WHERE first_name (NOT CASESPECIFIC) = 'George ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
Note
Requiert COLLATE.
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 |
Fonctionnement LIKE¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Cas 3 : La colonne CAST NOT CASESPECIFIC est CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Cas 4 : La colonne CAST CASESPECIFC est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Fonctionnement IN¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
Teradata¶
SELECT *
FROM employees
WHERE first_name IN ('George ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
Note
Ce cas nécessite 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 |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est ANSI¶
Teradata¶
Note
Dans ce cas, la colonne n’a pas de contrainte de colonne, mais la contrainte par défaut en mode Teradata ANSI est 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 |
Clause ORDER BY¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
Teradata¶
SELECT first_name
FROM employees
ORDER BY first_name;
| first\_name |
| ------------ |
| GeorgE |
| GEORGE |
| GEORGE |
| **George** |
| John |
| JOHN |
| JOHN |
| Marco |
| Mary |
| WIlle |
Snowflake¶
Avertissement
Veuillez consulter FDM. _ En attente d’ajout. _
SELECT
first_name
FROM
employees
ORDER BY first_name;
| first\_name |
| ------------ |
| GeorgE |
| **George** |
| GEORGE |
| GEORGE |
| John |
| JOHN |
| JOHN |
| Marco |
| Mary |
| WIlle |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Clause GROUP BY¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
Teradata¶
SELECT first_name
FROM employees
GROUP BY first_name;
| first\_name |
| ------------ |
| Mary |
| GeorgE |
| WIlle |
| **JOHN** |
| Marco |
| GEORGE |
Snowflake¶
Avertissement
La casse ou l’ordre peut générer une sortie différente.
Note
RTRIM est exigé dans les colonnes sélectionnées.
SELECT
first_name
FROM
employees
GROUP BY first_name;
| first\_name |
| ------------ |
| **John** |
| Marco |
| **George** |
| GeorgE |
| WIlle |
| Mary |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est ANSI¶
Teradata¶
SELECT last_name
FROM employees
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| |
| SNOW |
| SnoW |
| Snow |
| snow |
Snowflake¶
Note
L’ordre peut différer.
SELECT
last_name
FROM
employees
GROUP BY last_name;
| first\_name |
| ----------- |
| Snow |
| SNOW |
| SnoW |
| |
| SnoW |
| snow |
Clause HAVING¶
La clause HAVING utilisera les modèles dans :
Opérations d’évaluation.
Par exemple :
=, !=, <, >.
Opération LIKE.
Opération IN.
Opération CAST pour évaluation.
Opération CAST vers LIKE.
L’échantillon suivant présente un modèle avec une opération d’évaluation.
Échantillon : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
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
Instruction CASE WHEN¶
L’instruction CASE WHEN utilisera les modèles décrits dans :
Opérations d’évaluation.
Par exemple :
=, !=, <, >.
Opération LIKE.
Opération IN.
Opération CAST pour évaluation.
Opération CAST vers LIKE.
L’échantillon suivant présente un modèle avec une opération d’évaluation.
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 |
Clause JOIN¶
Avertissement
Les scénarios simples avec des opérations d’évaluation sont pris en charge.
L’instruction JOIN utilisera les modèles décrits dans :
Opérations d’évaluation.
Par exemple :
=, !=, <, >.
Opération LIKE.
Opération IN.
Opération CAST pour évaluation.
Opération CAST vers LIKE.
L’échantillon suivant présente un modèle avec une opération d’évaluation.
Échantillon : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
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¶
Note
d.department_name est NOT CASESPECIFIC, il faut donc 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 |
Mode ANSI pour la comparaison des chaînes - NOCOLLATE¶
Description ¶
Mode ANSI pour la comparaison des chaînes et les utilisations de NOCOLATE¶
La comparaison de chaînes en mode ANSI sans l’utilisation de COLLATE appliquera RTRIM et UPPER si nécessaire. Le comportement de suppression des espaces par défaut dans la définition des casses peut être pris en compte. Ainsi, si une colonne n’a pas de spécification de casse en mode Teradata ANSI, Teradata aura comme valeur par défaut CASESPECIFIC.
Échantillons de modèles de sources ¶
Données de configuration¶
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');
Opération de comparaison¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Cas 3 : la colonne CAST NOT CASESPECIFIC est CASESPECIFIC et le mode de la base de données est ANSI¶
Avertissement
(CASESPECIFIC) remplace la contrainte de colonne dans la définition de la table.
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 |
Cas 4 : La colonne CAST CASESPECIFIC est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Fonctionnement LIKE¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Cas 3 : la colonne CAST NOT CASESPECIFIC est NOTCASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Cas 4 : La colonne CAST CASESPECIFIC est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Fonctionnement IN¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Clause ORDER BY¶
Note
Notez que cette équivalence fonctionnelle peut être différente.
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
Teradata¶
SELECT department_name
FROM departments
ORDER BY department_name;
| department |
| --------------- |
| EngineerinG |
| Engineering |
| Finance |
| Human resources |
| IT |
| SalEs |
| SaleS |
| Sales |
| salEs |
| sales |
Snowflake¶
Note
Veuillez consulter FDM. L’ordre diffère selon l’ordre d’insertion des données.
SELECT
department_name
FROM
departments
ORDER BY
UPPER(department_name);
| department |
| --------------- |
| EngineerinG |
| Engineering |
| Finance |
| Human resources |
| IT |
| SalEs |
| SaleS |
| Sales |
| salEs |
| sales |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Clause GROUP BY¶
Avertissement
Pour garantir une équivalence fonctionnelle, il est nécessaire d’utiliser l’expression COLLATE.
Veuillez consulter SSC-EWI-TD0007 pour plus d’informations.
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
Clause HAVING¶
La clause HAVING utilisera les modèles dans :
Opérations d’évaluation.
Par exemple :
=, !=, <, >.
Opération LIKE.
Opération IN.
Opération CAST pour évaluation.
Opération CAST vers LIKE.
L’échantillon suivant présente un modèle avec une opération d’évaluation.
Échantillon : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
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
Instruction CASE WHEN¶
L’instruction CASE WHEN utilisera les modèles décrits dans :
Opérations d’évaluation.
Par exemple :
=, !=, <, >.
Opération LIKE.
Opération IN.
Opération CAST pour évaluation.
Opération CAST vers LIKE.
L’échantillon suivant présente un modèle avec une opération d’évaluation.
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 |
Clause JOIN¶
Avertissement
Des scénarios simples sont pris en charge.
L’instruction JOIN utilisera les modèles décrits dans :
Opérations d’évaluation.
Par exemple :
=, !=, <, >.
Opération LIKE.
Opération IN.
Opération CAST pour évaluation.
Opération CAST vers LIKE.
L’échantillon suivant présente un modèle avec une opération d’évaluation.
Échantillon : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est ANSI¶
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 |
EWIs connexes¶
SSC-EWI-TD0007 : GROUP BY IS NOT EQUIVALENT IN TERADATA MODE
Mode TERA pour la comparaison des chaînes - COLLATE¶
Description ¶
Mode Tera pour la comparaison des chaînes et l’utilisation de COLLATE¶
La comparaison de chaînes en mode Tera appliquera la contrainte COLLATE aux colonnes ou aux instructions, selon les besoins. Le comportement de suppression des espaces par défaut dans la définition des cas peut être pris en compte. La spécification de casse par défaut dans Teradata pour le mode TERA est NOT CASESPECIFIC. Ainsi, les colonnes sans spécification de casse auront des contraintes COLLATE('en-ci').
Échantillons de modèles de sources ¶
Données de configuration¶
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');
Opération de comparaison¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Cas 3 : La colonne CAST NOT CASESPECIFIC est CASESPECIFIC et le mode de la base de données est TERA¶
Note
Remarquez que les requêtes suivantes
SELECT * FROM employees WHERE first_name = 'JOHN ' (CASESPECIFIC)SELECT * FROM employees WHERE first_name (CASESPECIFIC) = 'JOHN '
renverront les mêmes valeurs.
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 |
Cas 4 : La colonne CAST CASESPECIFIC est NOT CASESPECIFIC et le mode de la base de données est TERA¶
Note
CAST vers une colonne de la partie gauche de la comparaison est prioritaire.
\ Par exemple :
SELECT * FROM employees WHERE last_name (NOT CASESPECIFIC) = 'snoW';_renverra 5 lignes. _SELECT * FROM employees WHERE last_name = 'snoW' (NOT CASESPECIFIC);renverra 0 lignes avec ces données de configuration.
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 |
Fonctionnement LIKE¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Cas 3 : La colonne CAST NOT CASESPECIFIC est CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Cas 4 : La colonne CAST CASESPECIFIC est NOT CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Fonctionnement IN¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Cas 2 : La contrainte de colonne n’est pas définie et le mode de la base de données est TERA¶
Note
En mode Tera, une spécification de casse non définie signifie 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 |
Cas 3 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Clause ORDER BY¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Clause GROUP BY¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est TERA¶
Teradata¶
SELECT first_name
FROM employees
GROUP BY first_name;
| first\_name |
| ------------ |
| Mary |
| GeorgE |
| WIlle |
| **JOHN** |
| Marco |
| **GEORGE** |
Snowflake¶
Avertissement
La spécification des casses dans la sortie peut varier en fonction du nombre de colonnes sélectionnées.
SELECT
first_name
FROM
employees
GROUP BY first_name;
| first\_name |
| ------------ |
| **John** |
| Marco |
| **George** |
| GeorgE |
| WIlle |
| Mary |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Clause HAVING¶
La clause HAVING utilisera les modèles dans :
Opérations d’évaluation.
Par exemple :
=, !=, <, >.
Opération LIKE.
Opération IN.
Opération CAST pour évaluation.
Opération CAST vers LIKE.
L’échantillon suivant présente un modèle avec une opération d’évaluation.
Échantillon : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est TERA¶
Teradata¶
Note
La spécification des casses dans la sortie peut varier en fonction du nombre de colonnes sélectionnées. Ceci est également lié à la clause GROUP BY.
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 |
Instruction CASE WHEN¶
L’instruction CASE WHEN utilisera les modèles décrits dans :
Opérations d’évaluation.
Par exemple :
=, !=, <, >.
Opération LIKE.
Opération IN.
Opération CAST pour évaluation.
Opération CAST vers LIKE.
L’échantillon suivant présente un modèle avec une opération d’évaluation.
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 |
Clause JOIN¶
Avertissement
Les scénarios simples avec des opérations d’évaluation sont pris en charge.
L’instruction JOIN utilisera les modèles décrits dans :
EvaluaComparisonComparisontion opérations.
Par exemple :
=, !=, <, >.
Opération LIKE.
Opération IN.
Opération CAST pour évaluation.
Opération CAST vers LIKE.
L’échantillon suivant présente un modèle avec une opération d’évaluation.
Échantillon : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est TERA¶
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 |
EWIs connexes¶
SSC-EWI-TD0007 : GROUP BY REQUIRED COLLATE FOR CASE INSENSITIVE COLUMNS
SC-FDM-TD0032 : [NOT] CASESPECIFIC CLAUSE WAS REMOVED
Mode TERA pour la comparaison des chaînes - NOCOLLATE¶
Description ¶
Mode Tera pour la comparaison des chaînes et les utilisations de NOCOLLATE¶
La comparaison de chaînes en mode Tera sans l’utilisation de COLLATE appliquera RTRIM et UPPER si nécessaire. Le comportement de suppression des espaces par défaut dans la définition des cas peut être pris en compte.
Échantillons de modèles de sources ¶
Données de configuration¶
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');
Opération de comparaison¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est TERA¶
Cet exemple montre l’utilisation d’une colonne définie comme NOT CASESPECIFIC car il s’agit d’une colonne first_name. Même si vous demandez la chaîne 'GEorge',, l’exécution de la requête récupérera des résultats dans Teradata parce que la spécification de casse n’est pas prise en compte.
Pour reproduire ce scénario dans Snowflake, deux fonctions sont mises en œuvre : RTRIM(UPPER(string_evaluation)), UPPER est nécessaire dans ce scénario parce que la chaîne ne passe pas en revue la spécification de casse.
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 |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est TERA¶
Pour cet exemple, la contrainte de colonne est CASESPECIFIC, pour laquelle l’exemple ne récupère pas de lignes dans Teradata parce que “Snow” n’est pas égal à “SNOW”.
Dans Snowflake, la migration qui en résulte n’indique que l’utilisation de la fonction RTRIM puisque la spécification de la casse est importante.
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 |
Cas 3 : la colonne CAST CASESPECIFIC est NOTCASESPECIFIC et le mode de la base de données est TERA¶
Teradata¶
Avertissement
(CASESPECIFIC) remplace la contrainte de colonne dans la définition de la table.
SELECT * FROM employees WHERE first_name = 'GEORGE ' (CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 6 | GEORGE | | sales |
Snowflake¶
Note
RTRIM est exigé du côté gauche, et RTRIM est exigé du côté droit.
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 |
Cas 4 : La colonne CAST NOTCASESPECIFIC est NOT CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Cas 5 : Cas des espaces vides. La contrainte de colonne est NOT CASESPECIFIC, le mode de la base de données est TERA et l’opération equal est utilisée¶
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 |
Fonctionnement LIKE¶
Note
Cette opération fonctionne différemment d’une autre. Les espaces vides doivent être de la même quantité pour récupérer l’information.
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est TERA¶
Cet exemple ne devrait afficher qu’une seule ligne, car la spécification de la casse n’est pas pertinente.
Note
Dans Snowflake, la migration utilise l’opération ILIKE. Cette opération permet d’effectuer une comparaison insensible à la casse.
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 |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Cas 3 : la colonne CAST CASESPECIFIC est NOTCASESPECIFIC et le mode de la base de données est TERA¶
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 |
Cas 4 : La colonne CAST NOTCASESPECIFIC est NOT CASESPECIFIC et le mode de la base de données est ANSI¶
Note
Cette casse nécessite la traduction en ILIKE.
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 |
Fonctionnement IN¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Cas 2 : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est TERA¶
Pour cet exemple, l’utilisation de la fonction UPPER n’est pas nécessaire puisque, dans la base de données Teradata, la spécification de la casse est pertinente pour les résultats.
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 |
Clause ORDER BY¶
Cas 1 : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est TERA¶
Notez que l’ordre de sortie peut être différent.
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
Notez que cette sortie peut être différente dans l’ordre.
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 |
Clause GROUP BY¶
Avertissement
Notez que cette sortie peut être différente. Pour garantir une équivalence fonctionnelle, il est nécessaire d’utiliser l’expression COLLATE.
Veuillez consulter SSC-EWI-TD0007 pour de plus amples informations.
Ce qui suit pourrait être une solution de contournement sans collate :
SELECTRTRIM(UPPER(first_name))
FROM employees
GROUP BY RTRIM(UPPER(first_name));
À propos du comportement de la colonne
Veuillez revoir l’insertion des données dans Snowflake. Snowflake autorise l’insertion de valeurs telles que « GEORGE » et « georges » sans afficher d’erreurs parce que la spécification de la casse n’est pas liée explicitement à la colonne.
Supposons une table et les données suivantes :
CREATE TABLE students (
first_name VARCHAR(50) NOT CASESPECIFIC
);
INSERT INTO students(first_name) VALUES ('George');
INSERT INTO students(first_name) VALUES (' George');
Notez que cet échantillon ne permet pas d’insérer des valeurs avec des casses majuscules et minuscules dans la colonne NOT CASESPECIFIC car il considère qu’il s’agit de la même valeur. Comme la colonne ne supervise pas la spécification de la casse, les valeurs « GEORGE » et « george » sont vérifiées comme étant la même information.
Les lignes suivantes sont considérées comme des erreurs de lignes dupliquées :
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
La clause HAVING utilisera les modèles dans :
Opérations d’évaluation.
Par exemple :
=, !=, <, >.
Opération LIKE.
Opération IN.
Opération CAST pour évaluation.
Opération CAST vers LIKE.
L’échantillon suivant présente un modèle avec une opération d’évaluation.
Échantillon : La contrainte de colonne est CASESPECIFIC et le mode de la base de données est TERA
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
L’instruction CASE WHEN utilisera les modèles décrits dans :
Opérations d’évaluation.
Par exemple :
=, !=, <, >.
Opération LIKE.
Opération IN.
Opération CAST pour évaluation.
Opération CAST vers LIKE.
L’échantillon suivant présente un modèle avec une opération d’évaluation.
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
Avertissement
Des scénarios simples sont pris en charge.
L’instruction JOIN utilisera les modèles décrits dans :
Opérations d’évaluation.
Par exemple :
=, !=, <, >.
Opération LIKE.
Opération IN.
Opération CAST pour évaluation.
Opération CAST vers LIKE.
L’échantillon suivant présente un modèle avec une opération d’évaluation.
Échantillon : La contrainte de colonne est NOT CASESPECIFIC et le mode de la base de données est TERA¶
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 |
Problèmes connus¶
il existe des restrictions d’instruction SQL spécifiques à certains modes :
BEGIN TRANSACTION,END TRANSACTION,COMMIT [WORK].L’insertion de données peut être différente dans Snowflake puisque la spécification de casse n’est pas liée à la déclaration de colonne.
GROUP BYpeut différer dans l’ordre, mais regroupe les valeurs correctes.ORDER BYse comporte différemment dans Snowflake.Si une fonction comporte un TRIM() dans le code source, cette solution de contournement ajoutera les fonctions requises au code source. Ainsi, RTRIM sera appliqué à la fonction source TRIM().
EWIs connexes¶
SSC-EWI-TD0007 : GROUP BY IS NOT EQUIVALENT IN TERADATA MODE