SnowConvert: Modos de sessão do Teradata

Descrição dos modos de sessão do Teradata

O banco de dados do Teradata tem diferentes modos de execução de consultas: modo ANSI (regras baseadas nas especificações ANSI SQL: 2011) e modo TERA (regras definidas pelo Teradata). Para obter mais informações, consulte a seguinte documentação do Teradata.

Modo Teradata para tabela informativa de cadeias de caracteres

Para cadeias de caracteres, o Modo Teradata funciona de forma diferente. Conforme explicado na tabela a seguir, com base na documentação do Teradata:

| Feature                                     | ANSI mode       | Teradata mode    |
| ------------------------------------------- | --------------- | ---------------- |
| Default attribute for character comparisons | CASESPECIFIC    | NOT CASESPECIFIC |
| Default TRIM behavior                       | TRIM(BOTH FROM) | TRIM(BOTH FROM)  |
Copy

Resumo da especificação da conversão

ModeColumn constraint valuesTeradata behaviorSC expected behavior
ANSI ModeCASESPECIFICCASESPECIFICNo constraint added.
NOT CASESPECIFICCASESPECIFICAdd COLLATE 'en-cs' in column definition.
Teradata ModeCASESPECIFICCASESPECIFICIn most cases, do not add COLLATE, and convert its usages of string comparison to RTRIM( expression )
NOT CASESPECIFICNOT CASESPECIFICIn most cases, do not add COLLATE, and convert its usages of string comparison to RTRIM(UPPER( expression ))

Opções de especificação de conversão disponíveis

Modo ANSI para comparação de cadeia de caracteres - COLLATE

Descrição

Modo ANSI para comparação de cadeia de caracteres e uso de COLLATE

A comparação de cadeia de caracteres no modo ANSI aplicará a restrição COLLATE às colunas ou instruções, conforme necessário. O comportamento de corte da especificação de maiúsculas e minúsculas padrão pode ser levado em consideração.

Observe que, no Teradata, a especificação de maiúsculas e minúsculas padrão é “CASESPECIFIC”, o mesmo padrão que no Snowflake “case-sensitive'. Portanto, esses casos não serão convertidos com um COLLATE porque serão redundantes.

Amostra de padrões de origem

Dados de configuração

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

Operação de comparação

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE first_name = 'GEorge ';
Copy
```none
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Snowflake
 SELECT
    *
FROM
    employees
WHERE
    COLLATE(first_name, 'en-cs-rtrim') = RTRIM('George');
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE last_name = 'SNOW ';
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 3            | WIlle       | SNOW       | Human resources |
| 2            | John        | SNOW       | Engineering     |
Copy
Snowflake
SELECT
 *
FROM
 employees
WHERE
 RTRIM(last_name) = RTRIM('SNOW ');
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 3            | WIlle       | SNOW       | Human resources |
| 2            | John        | SNOW       | Engineering     |
Copy
Caso 3: Coluna CAST NOT CASESPECIFIC para CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT * FROM employees WHERE first_name = 'George   ' (CASESPECIFIC);
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Snowflake

Nota

COLLATE “en-cs” é necessário para a equivalência funcional.

 SELECT
    *
FROM
    employees
WHERE
    COLLATE(first_name, 'en-cs-rtrim') = 'George   ' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/;
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Caso 4: Coluna CAST CASESPECIFIC para NOT CASESPECIFIC e o modo do banco de dados é modo ANSI
Teradata
 SELECT * FROM employees WHERE first_name = 'GEorge   ' (NOT CASESPECIFIC) ;
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
| 7            | GEORGE      |            | salEs      |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   RTRIM(first_name) = RTRIM('GEorge   ' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/);
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
| 7            | GEORGE      |            | salEs      |
Copy
Caso 5: Coluna CAST NOT CASESPECIFIC para NOT CASESPECIFIC e o modo do banco de dados é modo ANSI
Teradata
 SELECT * FROM employees WHERE first_name (NOT CASESPECIFIC)  = 'George    ';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Snowflake

Nota

É necessário COLLATE.

 SELECT
   * 
FROM
   employees
WHERE
   COLLATE(first_name /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/, 'en-cs-rtrim') = 'George    ';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy

Operação LIKE

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE first_name LIKE 'George';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Snowflake
 SELECT *
FROM employees
WHERE COLLATE(first_name, 'en-cs-rtrim') LIKE 'George';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 2            | John        | SNOW       | Engineering     |
| 3            | WIlle       | SNOW       | Human resources |
Copy
Snowflake
 SELECT *
FROM employees
WHERE RTRIM(last_name) LIKE RTRIM('Snow');
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 2            | John        | SNOW       | Engineering     |
| 3            | WIlle       | SNOW       | Human resources |
Copy
Caso 3: Coluna CAST NOT CASESPECIFIC para CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE first_name LIKE 'Mary' (CASESPECIFIC);
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 5            | Mary        |            | SaleS      |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   COLLATE(first_name, 'en-cs-rtrim') LIKE 'Mary' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/;
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 5            | Mary        |            | SaleS      |
Copy
Caso 4: Coluna CAST CASESPECIFC para NOT CASESPECIFIC e o modo do banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE last_name LIKE 'SNO%' (NOT CASESPECIFIC);
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 2            | John        | SNOW       | Engineering     |
| 3            | WIlle       | SNOW       | Human resources |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   RTRIM(last_name) LIKE RTRIM('SNO%' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/);
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 2            | John        | SNOW       | Engineering     |
| 3            | WIlle       | SNOW       | Human resources |
Copy

Operação IN

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE first_name IN ('George   ');
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Snowflake

Nota

Esse caso requer COLLATE(_ column_name_ , 'en-cs-rtrim')

 SELECT
   *
FROM
   employees
WHERE
   RTRIM(first_name) IN (COLLATE('George   ', 'en-cs-rtrim'));
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata

Nota

Nesse caso, a coluna não tem uma restrição de coluna, mas a restrição padrão no modo Teradata ANSI é CASESPECIFIC.

 SELECT *
FROM employees
WHERE department IN ('EngineerinG    ');
Copy
| employee\_id | first\_name | last\_name | department  |
| ------------ | ----------- | ---------- | ----------- |
| 4            | Marco       | SnoW       | EngineerinG |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   RTRIM(department) IN (RTRIM('EngineerinG    '));
Copy
| employee\_id | first\_name | last\_name | department  |
| ------------ | ----------- | ---------- | ----------- |
| 4            | Marco       | SnoW       | EngineerinG |
Copy

Cláusula ORDER BY

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT first_name
FROM employees
ORDER BY first_name;
Copy
| first\_name  |
| ------------ |
|    GeorgE    |
| GEORGE       |
| GEORGE       |
| **George**   |
| John         |
| JOHN         |
| JOHN         |
| Marco        |
| Mary         |
| WIlle        |
Copy
Snowflake

Aviso

Consulte FDM. _ Pendente para adicionar. _

 SELECT
   first_name
FROM
   employees
ORDER BY first_name;
Copy
| first\_name  |
| ------------ |
|    GeorgE    |
| **George**   |
| GEORGE       |
| GEORGE       |
| John         |
| JOHN         |
| JOHN         |
| Marco        |
| Mary         |
| WIlle        |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT last_name
FROM employees
ORDER BY last_name;
Copy
| department      |
| --------------- |
| EngineerinG     |
| Engineering     |
| Finance         |
| Human resources |
| IT              |
| SalEs           |
| SaleS           |
| Sales           |
| salEs           |
| sales           |
Copy
Snowflake
 SELECT
   last_name
FROM
   employees
ORDER BY last_name;
Copy
| department      |
| --------------- |
| EngineerinG     |
| Engineering     |
| Finance         |
| Human resources |
| IT              |
| SalEs           |
| SaleS           |
| Sales           |
| salEs           |
| sales           |
Copy

Cláusula GROUP BY

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT first_name
FROM employees
GROUP BY first_name;
Copy
| first\_name  |
| ------------ |
| Mary         |
|    GeorgE    |
| WIlle        |
| **JOHN**     |
| Marco        |
| GEORGE       |
Copy
Snowflake

Aviso

O caso ou a ordem pode diferir na saída.

Nota

RTRIM é necessário em colunas selecionadas.

   SELECT
   first_name
  FROM
   employees
  GROUP BY first_name;
Copy
| first\_name  |
| ------------ |
| **John**     |
| Marco        |
| **George**   |
|    GeorgE    |
| WIlle        |
| Mary         |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT last_name
FROM employees
GROUP BY last_name;
Copy
| last\_name |
| ---------- |
| SnoW       |
|            |
| SNOW       |
| SnoW       |
| Snow       |
| snow       |
Copy
Snowflake

Nota

A ordem pode ser diferente.

 SELECT
   last_name
  FROM
   employees
  GROUP BY last_name;
Copy
| first\_name |
| ----------- |
| Snow        |
| SNOW        |
| SnoW        |
|             |
| SnoW        |
| snow        |
Copy

Cláusula HAVING

A cláusula HAVING usará os padrões em:

  • Operações de avaliação.

    • Por exemplo: =, !=, <, >.

  • Operação LIKE.

  • Operação IN.

  • Operação CAST para avaliação.

  • Operação CAST para LIKE.

O exemplo a seguir mostra um padrão com operação de avaliação.

Amostra: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT first_name
FROM employees
GROUP BY first_name
HAVING first_name = 'Mary';
Copy
Mary
Copy
Snowflake
 SELECT
  first_name
FROM
  employees
GROUP BY first_name
HAVING
   COLLATE(first_name, 'en-cs-rtrim') = 'Mary';
Copy
Mary
Copy

Instrução CASEWHEN

A instrução CASE WHEN usará os padrões descritos em:

  • Operações de avaliação.

    • Por exemplo: =, !=, <, >.

  • Operação LIKE.

  • Operação IN.

  • Operação CAST para avaliação.

  • Operação CAST para LIKE.

O exemplo a seguir mostra um padrão com operação de avaliação.

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 = '';
Copy
| first\_name   | last\_name | department\_full\_name |
| ------------- | ---------- | ---------------------- |
| GEORGE        |            | Other                  |
| Mary          |            | Other                  |
|     GeorgE    |            | GLOBAL SALES           |
| GEORGE        |            | Other                  |
Copy
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('');
Copy
| first\_name   | last\_name | department\_full\_name |
| ------------- | ---------- | ---------------------- |
| Mary          |            | Other                  |
| GEORGE        |            | Other                  |
| GEORGE        |            | Other                  |
|     GeorgE    |            | GLOBAL SALES           |
Copy

Cláusula JOIN

Aviso

Há suporte para cenários simples com operações de avaliação.

A instrução JOIN usará os padrões descritos em:

  • Operações de avaliação.

    • Por exemplo: =, !=, <, >.

  • Operação LIKE.

  • Operação IN.

  • Operação CAST para avaliação.

  • Operação CAST para LIKE.

O exemplo a seguir mostra um padrão com operação de avaliação.

Amostra: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo 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;
Copy
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ----------- | ---------- | ---------------- |
| 1            | George      | Snow       | Sales            |
| 10           | JOHN        | snow       | Finance          |
Copy
Snowflake

Nota

d.department_name é NOT CASESPECIFIC, portanto, requer 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;
Copy
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ----------- | ---------- | ---------------- |
| 1            | George      | Snow       | Sales            |
| 10           | JOHN        | snow       | Finance          |
Copy

Modo ANSI para comparação de cadeia de caracteres - NOCOLLATE

Descrição

Modo ANSI para comparação de cadeia de caracteres e usos de NOCOLATE.

A comparação de cadeia de caracteres no modo ANSI sem o uso de COLLATE aplicará RTRIM e UPPER conforme necessário. O comportamento padrão do trim da especificação de caso pode ser levado em conta, portanto, se uma coluna não tiver uma especificação de caso no modo Teradata ANSI, o Teradata terá como padrão CASESPECIFIC.

Amostra de padrões de origem

Dados de configuração

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

Operação de comparação

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE first_name = 'George      ';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Snowflake
 SELECT
 *
FROM
employees
WHERE
RTRIM(first_name) = RTRIM('George      ');
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE last_name = 'SNOW ';
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 3            | WIlle       | SNOW       | Human resources |
| 2            | John        | SNOW       | Engineering     |
Copy
Snowflake
 SELECT
 *
FROM
employees
WHERE
 RTRIM(last_name) = RTRIM('SNOW ');
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 3            | WIlle       | SNOW       | Human resources |
| 2            | John        | SNOW       | Engineering     |
Copy
Caso 3: Coluna CAST NOT CASESPECIFIC para CASESPECIFIC e o modo de banco de dados é modo ANSI

Aviso

O (CASESPECIFIC) substitui a restrição de coluna na definição da tabela.

Teradata
 SELECT * FROM employees WHERE first_name = 'GEorge   ' (CASESPECIFIC);
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 6            | GEORGE      |            | sales      |
Copy
Snowflake
 SELECT * FROM workers
WHERE RTRIM(first_name) = RTRIM(UPPER('GEorge   '));
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 6            | GEORGE      |            | sales      |
Copy
Caso 4: Coluna CAST CASESPECIFIC para NOT CASESPECIFIC e o modo do banco de dados é modo ANSI
Teradata
 SELECT * FROM employees
WHERE last_name = 'SnoW   ' (NOT CASESPECIFIC) ;
Copy
| employee\_id | first\_name | last\_name | department  |
| ------------ | ----------- | ---------- | ----------- |
| 4            | Marco       | SnoW       | EngineerinG |
Copy
Snowflake
 SELECT * FROM employees
WHERE RTRIM(last_name) = RTRIM('SnoW   ');
Copy
| employee\_id | first\_name | last\_name | department  |
| ------------ | ----------- | ---------- | ----------- |
| 4            | Marco       | SnoW       | EngineerinG |
Copy

Operação LIKE

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE first_name LIKE 'Georg%';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Snowflake
 SELECT *
FROM employees
WHERE first_name LIKE 'Georg%';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Snowflake
 SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Caso 3: Coluna CAST NOT CASESPECIFIC para NOTCASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE first_name LIKE 'George' (NOT CASESPECIFIC);
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   first_name ILIKE 'George' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Caso 4: Coluna CAST CASESPECIFIC para NOT CASESPECIFIC e o modo do banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE last_name LIKE 'SNO%' (NOT CASESPECIFIC);
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 2            | John        | SNOW       | Engineering     |
| 3            | WIlle       | SNOW       | Human resources |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   last_name LIKE 'SNO%' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 2            | John        | SNOW       | Engineering     |
| 3            | WIlle       | SNOW       | Human resources |
Copy

Operação IN

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE first_name IN ('GEORGE   ');
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 6            | GEORGE      |            | sales      |
| 7            | GEORGE      |            | salEs      |
Copy
Snowflake
 SELECT *
FROM employees
WHERE RTRIM(first_name) IN (RTRIM('GEORGE   '));
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 6            | GEORGE      |            | sales      |
| 7            | GEORGE      |            | salEs      |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT *
FROM employees
WHERE department IN ('SaleS');
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 5            | Mary        |            | SaleS      |
Copy
Snowflake
 SELECT *
FROM employees
WHERE RTRIM(department) IN (RTRIM('SaleS'));
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 5            | Mary        |            | SaleS      |
Copy

Cláusula ORDER BY

Nota

Observe que essa equivalência funcional pode ser diferente.

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT department_name
FROM departments
ORDER BY department_name;
Copy
| department      |
| --------------- |
| EngineerinG     |
| Engineering     |
| Finance         |
| Human resources |
| IT              |
| SalEs           |
| SaleS           |
| Sales           |
| salEs           |
| sales           |
Copy
Snowflake

Nota

Consulte FDM. A ordem difere na ordem de inserção dos dados.

 SELECT
   department_name
FROM
   departments
ORDER BY
   UPPER(department_name);
Copy
| department      |
| --------------- |
| EngineerinG     |
| Engineering     |
| Finance         |
| Human resources |
| IT              |
| SalEs           |
| SaleS           |
| Sales           |
| salEs           |
| sales           |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT last_name
FROM employees
ORDER BY last_name;
Copy
| department             |
| ---------------------- |
| Finance                |
| Human Resources        |
| Information Technology |
| Sales                  |
Copy
Snowflake
 SELECT last_name
FROM employees
ORDER BY last_name;
Copy
| department             |
| ---------------------- |
| Finance                |
| Human Resources        |
| Information Technology |
| Sales                  |
Copy

Cláusula GROUP BY

Aviso

Para garantir uma equivalência funcional, é necessário usar a expressão COLLATE.

Consulte SSC-EWI-TD0007 para obter mais informações.

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT first_name
FROM employees
GROUP BY first_name;
Copy
| first\_name   |
| ------------- |
| Mary          |
|     GeorgE    |
| WIlle         |
| John          |
| Marco         |
| GEORGE        |
Copy
Snowflake
 SELECT
   first_name
FROM
   employees
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY first_name;
Copy
| FIRST\_NAME   |
| ------------- |
| George        |
| John          |
| WIlle         |
| Marco         |
| Mary          |
| GEORGE        |
| GEORGE        |
|     GeorgE    |
| JOHN          |
| JOHN          |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT last_name
FROM employees
GROUP BY last_name;
Copy
| last\_name |
| ---------- |
| SnoW       |
|            |
| SNOW       |
| SnoW       |
| Snow       |
| snow       |
Copy
Snowflake
 SELECT
   last_name
FROM
   employees
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY last_name;
Copy
| last\_name |
| ---------- |
| SnoW       |
|            |
| SNOW       |
| SnoW       |
| Snow       |
| snow       |
Copy

Cláusula HAVING

A cláusula HAVING usará os padrões em:

  • Operações de avaliação.

    • Por exemplo: =, !=, <, >.

  • Operação LIKE.

  • Operação IN.

  • Operação CAST para avaliação.

  • Operação CAST para LIKE.

O exemplo a seguir mostra um padrão com operação de avaliação.

Amostra: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo ANSI
Teradata
 SELECT first_name
FROM employees
GROUP BY first_name
HAVING first_name = 'GEORGE';
Copy
GEORGE
Copy
Snowflake
 SELECT
   first_name
FROM
   employees
GROUP BY first_name
HAVING
   RTRIM(first_name) = RTRIM('GEORGE');
Copy
GEORGE
Copy

Instrução CASEWHEN

A instrução CASE WHEN usará os padrões descritos em:

  • Operações de avaliação.

    • Por exemplo: =, !=, <, >.

  • Operação LIKE.

  • Operação IN.

  • Operação CAST para avaliação.

  • Operação CAST para LIKE.

O exemplo a seguir mostra um padrão com operação de avaliação.

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 = '   ';
Copy
first_namelast_namedepartment_full_name
GEORGEDepartment Full Name
MaryGLOBAL SALES
GeorgE Other
GEORGEDepartment 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('   ');
Copy
first_namelast_namedepartment_full_name
GEORGEDepartment Full Name
MaryGLOBAL SALES
GeorgE Other
GEORGEDepartment Full Name

Cláusula JOIN

Aviso

Há suporte para cenários simples.

A instrução JOIN usará os padrões descritos em:

  • Operações de avaliação.

    • Por exemplo: =, !=, <, >.

  • Operação LIKE.

  • Operação IN.

  • Operação CAST para avaliação.

  • Operação CAST para LIKE.

O exemplo a seguir mostra um padrão com operação de avaliação.

Amostra: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo 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;
Copy
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ----------- | ---------- | ---------------- |
| 1            | George      | Snow       | Sales            |
| 10           | JOHN        | snow       | Finance          |
Copy
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);
Copy
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ----------- | ---------- | ---------------- |
| 1            | George      | Snow       | Sales            |
| 10           | JOHN        | snow       | Finance          |
Copy

EWIs relacionados

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

Modo TERA para comparação de cadeia de caracteres - COLLATE

Descrição

Modo Tera para comparação de cadeia de caracteres e uso de COLLATE

A comparação de cadeia de caracteres no modo Tera aplicará a restrição COLLATE às colunas ou instruções, conforme necessário. O comportamento de corte da especificação de maiúsculas e minúsculas padrão pode ser levado em consideração. A especificação de caso padrão no Teradata para o modo TERA é NOT CASESPECIFIC. Assim, as colunas sem especificação de caso terão restrições COLLATE('en-ci').

Amostra de padrões de origem

Dados de configuração

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

Operação de comparação

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT *
FROM employees
WHERE first_name = 'GEorge ';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Snowflake
 SELECT
 *
FROM
 employees
WHERE
 RTRIM(first_name) = RTRIM('GEorge ');
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT *
FROM employees
WHERE last_name = 'SNOW ';
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 3            | WIlle       | SNOW       | Human resources |
| 2            | John        | SNOW       | Engineering     |
Copy
Snowflake
SELECT
 *
FROM
 employees
WHERE
 RTRIM(last_name) = RTRIM('SNOW ');
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 3            | WIlle       | SNOW       | Human resources |
| 2            | John        | SNOW       | Engineering     |
Copy
Caso 3: Coluna CAST NOT CASESPECIFIC para CASESPECIFIC e o modo de banco de dados é modo TERA

Nota

Observe que as seguintes consultas

  • SELECT * FROM employees WHERE first_name = 'JOHN ' (CASESPECIFIC)

  • SELECT * FROM employees WHERE first_name (CASESPECIFIC) = 'JOHN '

retornarão os mesmos valores.

Teradata
 SELECT * FROM employees WHERE first_name = 'JOHN   ' (CASESPECIFIC);
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 9            | JOHN        | SnoW       | IT         |
| 10           | JOHN        | snow       | Finance    |
Copy
Snowflake
 SELECT
    *
FROM
    employees
WHERE 
    COLLATE(first_name, 'en-cs-rtrim') = 'JOHN   ' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/;
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 9            | JOHN        | SnoW       | IT         |
| 10           | JOHN        | snow       | Finance    |
Copy
Caso 4: Coluna CAST CASESPECIFIC para NOT CASESPECIFIC e o modo do banco de dados é modo TERA

Nota

CAST para uma coluna no lado esquerdo da comparação tem prioridade.

\ Por exemplo:

  • SELECT * FROM employees WHERE last_name (NOT CASESPECIFIC) = 'snoW'; _retornará 5 linhas. _

  • SELECT * FROM employees WHERE last_name = 'snoW' (NOT CASESPECIFIC); retornará 0 linhas com esses dados de configuração.

Teradata
 SELECT * FROM employees WHERE last_name (NOT CASESPECIFIC)  = 'snoW' ;
Copy
| 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         |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   COLLATE(last_name /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/, 'en-ci-rtrim') = 'snoW' ;
Copy
| 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         |
Copy

Operação LIKE

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT *
FROM employees
WHERE first_name LIKE 'GeorgE';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   RTRIM(first_name) LIKE RTRIM('GeorgE');
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   RTRIM(last_name) LIKE RTRIM('Snow');
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Caso 3: Coluna CAST NOT CASESPECIFIC para CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT *
FROM employees
WHERE first_name LIKE 'George' (CASESPECIFIC);
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Snowflake
 SELECT
    *
FROM
    employees
WHERE
    COLLATE(first_name, 'en-cs-rtrim') LIKE 'George';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Caso 4: Coluna CAST CASESPECIFIC para NOT CASESPECIFIC e o modo do banco de dados é modo TERA
Teradata
 SELECT *
FROM employees
WHERE last_name LIKE 'SNO%' (NOT CASESPECIFIC);
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 3            | WIlle       | SNOW       | Human resources |
| 2            | John        | SNOW       | Engineering     |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   RTRIM(last_name) LIKE RTRIM('SNO%' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/);
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 3            | WIlle       | SNOW       | Human resources |
| 2            | John        | SNOW       | Engineering     |
Copy

Operação IN

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT *
FROM employees
WHERE first_name IN ('George   ');
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   RTRIM(first_name) IN (RTRIM('George   '));
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Caso 2: A restrição de coluna não é definida e o modo de banco de dados é modo TERA

Nota

No modo Tera, a especificação de caso não definida significa NOT CASESPECIFIC.

Teradata
 SELECT *
FROM employees
WHERE department IN ('Sales    ');
Copy
| employee\_id | first\_name  | last\_name | department |
| ------------ | ------------ | ---------- | ---------- |
| 1            | George       | Snow       | Sales      |
| 5            | Mary         |            | SaleS      |
| 6            | GEORGE       |            | sales      |
| 7            | GEORGE       |            | salEs      |
| 8            |    GeorgE    |            | SalEs      |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   RTRIM(department) IN (RTRIM('Sales    '));
Copy
| employee\_id | first\_name  | last\_name | department |
| ------------ | ------------ | ---------- | ---------- |
| 1            | George       | Snow       | Sales      |
| 5            | Mary         |            | SaleS      |
| 6            | GEORGE       |            | sales      |
| 7            | GEORGE       |            | salEs      |
| 8            |    GeorgE    |            | SalEs      |
Copy
Caso 3: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT *
FROM employees
WHERE last_name IN ('SNOW   ');
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 3            | WIlle       | SNOW       | Human resources |
| 2            | John        | SNOW       | Engineering     |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   RTRIM(last_name) IN (RTRIM('SNOW   '));
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 3            | WIlle       | SNOW       | Human resources |
| 2            | John        | SNOW       | Engineering     |
Copy

Cláusula ORDER BY

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT employee_id, first_name
FROM employees
ORDER BY employee_id, first_name;
Copy
employee_idfirst_name
1George
2John
3WIlle
4Marco
5Mary
6GEORGE
7GEORGE
8 GeorgE
9JOHN
10JOHN
Snowflake
 SELECT employee_id, first_name
FROM employees
ORDER BY employee_id, first_name;
Copy
employee_idfirst_name
1George
2John
3WIlle
4Marco
5Mary
6GEORGE
7GEORGE
8 GeorgE
9JOHN
10JOHN
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT employee_id, last_name
FROM employees
ORDER BY employee_id, last_name;
Copy
employee_idlast_name
1Snow
2SNOW
3SNOW
4SnoW
5
6
7
8
9SnoW
10snow
Snowflake
 SELECT employee_id, last_name
FROM employees
ORDER BY employee_id, last_name;
Copy
employee_idlast_name
1Snow
2SNOW
3SNOW
4SnoW
5
6
7
8
9SnoW
10snow

Cláusula GROUP BY

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT first_name
FROM employees
GROUP BY first_name;
Copy
| first\_name  |
| ------------ |
| Mary         |
|    GeorgE    |
| WIlle        |
| **JOHN**     |
| Marco        |
| **GEORGE**   |
Copy
Snowflake

Aviso

A especificação de caso na saída pode variar de acordo com o número de colunas selecionadas.

 SELECT
   first_name
FROM
   employees
GROUP BY first_name;
Copy
| first\_name  |
| ------------ |
| **John**     |
| Marco        |
| **George**   |
|    GeorgE    |
| WIlle        |
| Mary         |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT last_name
FROM employees
GROUP BY last_name;
Copy
| last\_name |
| ---------- |
| SnoW       |
|            |
| SNOW       |
| SnoW       |
| Snow       |
| snow       |
Copy
Snowflake
 SELECT
   last_name
FROM
   employees
GROUP BY last_name;
Copy
| last\_name |
| ---------- |
| SnoW       |
|            |
| SNOW       |
| SnoW       |
| Snow       |
| snow       |
Copy

Cláusula HAVING

A cláusula HAVING usará os padrões em:

  • Operações de avaliação.

    • Por exemplo: =, !=, <, >.

  • Operação LIKE.

  • Operação IN.

  • Operação CAST para avaliação.

  • Operação CAST para LIKE.

O exemplo a seguir mostra um padrão com operação de avaliação.

Amostra: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata

Nota

A especificação de caso na saída pode variar de acordo com o número de colunas selecionadas. Isso também está relacionado à cláusula GROUP BY.

 SELECT first_name
FROM employees
GROUP BY first_name
HAVING first_name = 'George  ';
Copy
employee_idfirst_name
7GEORGE
1George
6GEORGE
Snowflake
 SELECT
  employee_id,
  first_name
FROM
  employees
GROUP BY employee_id, first_name
HAVING
   RTRIM(first_name) = RTRIM('George  ');
Copy
employee_idfirst_name
7GEORGE
1George
6GEORGE

Instrução CASEWHEN

A instrução CASE WHEN usará os padrões descritos em:

  • Operações de avaliação.

    • Por exemplo: =, !=, <, >.

  • Operação LIKE.

  • Operação IN.

  • Operação CAST para avaliação.

  • Operação CAST para LIKE.

O exemplo a seguir mostra um padrão com operação de avaliação.

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 = '';
Copy
| first\_name  | last\_name | department\_full\_name |
| ------------ | ---------- | ---------------------- |
| GEORGE       |            | GLOBAL SALES           |
| Mary         |            | Other                  |
|    GeorgE    |            | Other                  |
| GEORGE       |            | GLOBAL SALES           |
Copy
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('');
Copy
| first\_name  | last\_name | department\_full\_name |
| ------------ | ---------- | ---------------------- |
| GEORGE       |            | GLOBAL SALES           |
| Mary         |            | Other                  |
|    GeorgE    |            | Other                  |
| GEORGE       |            | GLOBAL SALES           |
Copy

Cláusula JOIN

Aviso

Há suporte para cenários simples com operações de avaliação.

A instrução JOIN usará os padrões descritos em:

  • operações EvaluaComparisonComparisontion.

    • Por exemplo: =, !=, <, >.

  • Operação LIKE.

  • Operação IN.

  • Operação CAST para avaliação.

  • Operação CAST para LIKE.

O exemplo a seguir mostra um padrão com operação de avaliação.

Amostra: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo 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;
Copy
| 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          |
Copy
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);
Copy
| 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          |
Copy

EWIs relacionados

SSC-EWI-TD0007: GROUP BY REQUIRED COLLATE FOR CASE INSENSITIVE COLUMNS

SC-FDM-TD0032 : [NOT] CASESPECIFIC CLAUSE WAS REMOVED

Modo TERA para comparação de cadeia de caracteres - NOCOLLATE

Descrição

Modo Tera para comparação de cadeia de caracteres e usos de NOCOLLATE

A comparação de cadeia de caracteres no modo Tera sem o uso de COLLATE aplicará RTRIM e UPPER conforme necessário. O comportamento de corte da especificação de maiúsculas e minúsculas padrão pode ser levado em consideração.

Amostra de padrões de origem

Dados de configuração

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

Operação de comparação

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo TERA

Este exemplo demonstra o uso de uma coluna configurada como NOT CASESPECIFIC, pois é uma coluna first_name. Mesmo ao solicitar a cadeia de caracteres 'GEorge', a execução da consulta recuperará resultados no Teradata porque a especificação de caso não é considerada.

Para emular esse cenário no Snowflake, são implementadas duas funções: RTRIM(UPPER(string_evaluation)), UPPER é necessário nesse cenário porque a cadeia de caracteres não analisa a especificação do caso.

Teradata
 SELECT *
FROM employees
WHERE first_name = 'GEorge ';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Snowflake
 SELECT
 *
FROM
 employees
WHERE
 RTRIM(UPPER(first_name)) = RTRIM(UPPER('GEorge '));
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo TERA

Para este exemplo, a restrição de coluna é CASESPECIFIC, para a qual o exemplo não recupera linhas no Teradata porque “Snow” não é igual a “SNOW”.

No Snowflake, a migração resultante aponta apenas para o uso da função RTRIM, pois a especificação do caso é importante.

Teradata
 SELECT *
FROM employees
WHERE last_name = 'SNOW ';
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 3            | WIlle       | SNOW       | Human resources |
| 2            | John        | SNOW       | Engineering     |
Copy
Snowflake
SELECT
 *
FROM
 employees
WHERE
 RTRIM(last_name) = RTRIM('SNOW ');
Copy
| employee\_id | first\_name | last\_name | department      |
| ------------ | ----------- | ---------- | --------------- |
| 3            | WIlle       | SNOW       | Human resources |
| 2            | John        | SNOW       | Engineering     |
Copy
Caso 3: Coluna CAST CASESPECIFIC para NOTCASESPECIFIC e o modo de banco de dados é modo TERA
Teradata

Aviso

O (CASESPECIFIC) substitui a restrição de coluna na definição da tabela.

 SELECT * FROM employees WHERE first_name = 'GEORGE   ' (CASESPECIFIC);
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 6            | GEORGE      |            | sales      |
Copy
Snowflake

Nota

RTRIM é necessário no lado esquerdo, e RTRIM é necessário no lado direito.

 SELECT
   * 
FROM
   employees
WHERE
   RTRIM(first_name) = RTRIM('GEORGE   ' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/);
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 6            | GEORGE      |            | sales      |
Copy
Caso 4: Coluna CAST NOTCASESPECIFIC para NOT CASESPECIFIC e o modo do banco de dados é modo TERA
Teradata
 SELECT * FROM employees WHERE first_name = 'GEorge   ' (NOT CASESPECIFIC) ;
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Snowflake
 SELECT
   * 
FROM
   employees
WHERE
   UPPER(RTRIM(first_name)) = UPPER(RTRIM('GEorge   ' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/));
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Caso 5: Caso de espaços em branco. A restrição de coluna é NOT CASESPECIFIC, o modo de banco de dados é modo TERA Mode e o uso da operação igual
Teradata
 SELECT *
FROM employees
WHERE last_name = '   ';
Copy
| employee\_id | first\_name   | last\_name | department |
| ------------ | ------------- | ---------- | ---------- |
| 7            | GEORGE        |            | salEs      |
| 5            | Mary          |            | SaleS      |
| 8            |     GeorgE    |            | SalEs      |
| 6            | GEORGE        |            | sales      |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   RTRIM(last_name) = RTRIM('   ');
Copy
| employee\_id | first\_name   | last\_name | department |
| ------------ | ------------- | ---------- | ---------- |
| 7            | GEORGE        |            | salEs      |
| 5            | Mary          |            | SaleS      |
| 8            |     GeorgE    |            | SalEs      |
| 6            | GEORGE        |            | sales      |
Copy

Operação LIKE

Nota

Essa operação funciona de forma diferente de outra. Os espaços em branco devem ter a mesma quantidade para recuperar as informações.

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo TERA

Espera-se que este exemplo exiba uma linha porque a especificação do caso não é relevante.

Nota

No Snowflake, a migração usa a operação ILIKE. Isso realiza uma comparação sem distinção entre maiúsculas e minúsculas.

Teradata
 SELECT *
FROM employees
WHERE first_name LIKE 'GeorgE';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Snowflake
 SELECT *
FROM employees
WHERE first_name ILIKE 'GeorgE';
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
Copy
| first\_name | last\_name | department  |
| ----------- | ---------- | ----------- |
| George      | Snow       | Sales       |
| Jonh        | Snow       | Engineering |
Copy
Snowflake
 SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
Copy
| first\_name | last\_name | department  |
| ----------- | ---------- | ----------- |
| George      | Snow       | Sales       |
| Jonh        | Snow       | Engineering |
Copy
Caso 3: Coluna CAST CASESPECIFIC para NOTCASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT *
FROM employees
WHERE first_name LIKE 'George' (NOT CASESPECIFIC);
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE 
   first_name ILIKE 'George' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Caso 4: Coluna CAST NOTCASESPECIFIC para NOT CASESPECIFIC e o modo do banco de dados é modo ANSI

Nota

Esse caso exige a conversão para ILIKE.

Teradata
 SELECT *
FROM employees
WHERE first_name LIKE 'GE%' (NOT CASESPECIFIC);
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Snowflake
 SELECT
   *
FROM
   employees
WHERE
   first_name ILIKE 'GE%' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy

Operação IN

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT *
FROM employees
WHERE first_name IN ('GeorgE');
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Snowflake
 SELECT *
FROM employees
WHERE RTRIM(UPPER(first_name)) IN (RTRIM(UPPER('GeorgE')));
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7            | GEORGE      |            | salEs      |
| 1            | George      | Snow       | Sales      |
| 6            | GEORGE      |            | sales      |
Copy
Caso 2: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo TERA

Neste exemplo, o uso da função UPPER não é necessário, pois, no banco de dados Teradata, a especificação do caso é relevante para os resultados.

Teradata
 SELECT *
FROM employees
WHERE last_name IN ('SnoW');
Copy
| employee\_id | first\_name | last\_name | department  |
| ------------ | ----------- | ---------- | ----------- |
| 4            | Marco       | SnoW       | EngineerinG |
Copy
Snowflake
 SELECT *
FROM employees
WHERE RTRIM(last_name) IN (RTRIM('SnoW'));
Copy
| employee\_id | first\_name | last\_name | department  |
| ------------ | ----------- | ---------- | ----------- |
| 4            | Marco       | SnoW       | EngineerinG |
Copy

Cláusula ORDER BY

Caso 1: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo TERA

Observe que essa ordem de saída pode ser diferente.

Teradata
 SELECT department
FROM employees
ORDER BY department;
Copy
| department      |
| --------------- |
| EngineerinG     |
| Engineering     |
| Finance         |
| Human resources |
| IT              |
| sales           |
| SalEs           |
| Sales           |
| SaleS           |
| salEs           |
Copy
Snowflake
 SELECT department
FROM employees
ORDER BY UPPER(department);
Copy
| department      |
| --------------- |
| EngineerinG     |
| Engineering     |
| Finance         |
| Human resources |
| IT              |
| sales           |
| SalEs           |
| Sales           |
| SaleS           |
| salEs           |
Copy
Case 2: Column constraint is CASESPECIFIC and database mode is TERA Mode

Observe que essa saída pode diferir na ordem.

Teradata
 SELECT last_name
FROM employees
ORDER BY last_name;
Copy
| last\_name |
| ---------- |
|            |
|            |
|            |
|            |
|     SnoW   |
| SNOW       |
| SNOW       |
| SnoW       |
| Snow       |
| snow       |
Copy
Snowflake
 SELECT last_name
FROM employees
ORDER BY last_name;
Copy
| last\_name |
| ---------- |
|            |
|            |
|            |
|            |
|     SnoW   |
| SNOW       |
| SNOW       |
| SnoW       |
| Snow       |
| snow       |
Copy

Cláusula GROUP BY

Aviso

Observe que essa saída pode ser diferente. Para garantir uma equivalência funcional, é necessário usar a expressão COLLATE.

Consulte SSC-EWI-TD0007 para obter mais informações.

_O seguinte pode ser uma solução alternativa sem collate: _

SELECTRTRIM(UPPER(first_name))

FROM employees

GROUPBYRTRIM(UPPER(first_name));

Sobre o comportamento da coluna

Revise a inserção de dados no Snowflake. O Snowflake permite a inserção de valores como “GEORGE” e “georges” sem mostrar erros porque a especificação do caso não está vinculada explicitamente à coluna.

Suponha uma tabela e os dados a seguir:

 CREATE TABLE students (
   first_name VARCHAR(50) NOT CASESPECIFIC
);

INSERT INTO students(first_name) VALUES ('George');
INSERT INTO students(first_name) VALUES ('   George');
Copy

Observe que esse exemplo não permite a inserção de valores com letras maiúsculas e minúsculas na coluna NOT CASESPECIFIC porque ele considera o mesmo valor. Como a coluna não supervisiona a especificação do caso, os valores “GEORGE” e “george” são verificados como a mesma informação.

As linhas a seguir são consideradas como erros de linhas duplicadas :

 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');
Copy
Case 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode
Teradata
 SELECT first_name
FROM employees
GROUP BY first_name;
Copy
| first\_name  |
| ------------ |
| Mary         |
|    GeorgE    |
| WIlle        |
| JOHN         |
| Marco        |
| GEORGE       |
Copy
Snowflake
 SELECT
   first_name
FROM
   employees
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY first_name;
Copy
| first\_name  |
| ------------ |
| George       |
| John         |
| WIlle        |
| Marco        |
| Mary         |
| GEORGE       |
| GEORGE       |
|    GeorgE    |
| JOHN         |
| JOHN         |
Copy
Case 2: Column constraint is CASESPECIFIC and database mode is TERA Mode
Teradata
 SELECT last_name
FROM employees
GROUP BY last_name;
Copy
| last\_name |
| ---------- |
|    SnoW    |
|            |
| SNOW       |
| SnoW       |
| Snow       |
| snow       |
Copy
Snowflake
 SELECT
   last_name
FROM
   employees
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY last_name;
Copy
| last\_name |
| ---------- |
|    SnoW    |
| SNOW       |
| SnoW       |
|            |
|            |
| Snow       |
| snow       |
Copy

HAVING clause

A cláusula HAVING usará os padrões em:

  • Operações de avaliação.

    • Por exemplo: =, !=, <, >.

  • Operação LIKE.

  • Operação IN.

  • Operação CAST para avaliação.

  • Operação CAST para LIKE.

O exemplo a seguir mostra um padrão com operação de avaliação.

Amostra: A restrição de coluna é CASESPECIFIC e o modo de banco de dados é modo TERA
Teradata
 SELECT last_name
FROM employees
GROUP BY last_name
HAVING last_name = 'Snow';
Copy
| last\_name |
| ---------- |
| Snow       |
Copy
Snowflake
 SELECT last_name
FROM employees
GROUP BY last_name
HAVING RTRIM(last_name) = RTRIM('Snow');
Copy
| last\_name |
| ---------- |
| Snow       |
Copy

CASE WHEN statement

A instrução CASE WHEN usará os padrões descritos em:

  • Operações de avaliação.

    • Por exemplo: =, !=, <, >.

  • Operação LIKE.

  • Operação IN.

  • Operação CAST para avaliação.

  • Operação CAST para LIKE.

O exemplo a seguir mostra um padrão com operação de avaliação.

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

JOIN clause

Aviso

Há suporte para cenários simples.

A instrução JOIN usará os padrões descritos em:

  • Operações de avaliação.

    • Por exemplo: =, !=, <, >.

  • Operação LIKE.

  • Operação IN.

  • Operação CAST para avaliação.

  • Operação CAST para LIKE.

O exemplo a seguir mostra um padrão com operação de avaliação.

Amostra: A restrição de coluna é NOT CASESPECIFIC e o modo de banco de dados é modo 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;
Copy
employee_idfirst_namelast_namedepartment_name
1GeorgeSnowSales
3WIlleSNOWHuman Resources
5MarySales
6GEORGESales
7GEORGESales
8 GeorgE Sales
10JOHNsnowFinance
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));
Copy
employee_idfirst_namelast_namedepartment_name
1GeorgeSnowSales
3WIlleSNOWHuman Resources
5MarySales
6GEORGESales
7GEORGESales
8 GeorgE Sales
10JOHNsnowFinance

Problemas conhecidos

  1. Existem algumas restrições de instrução SQL específicas de modo: BEGIN TRANSACTION, END TRANSACTION, COMMIT [WORK].

  2. A inserção de dados pode ser diferente no Snowflake, pois a especificação do caso não está vinculada à instrução da coluna.

  3. GROUP BY pode diferir na ordem, mas agrupa os valores corretos.

  4. ORDER BY se comporta de forma diferente no Snowflake.

  5. Se uma função tiver um TRIM() no código-fonte, essa solução alternativa adicionará as funções necessárias ao código-fonte. Portanto, RTRIM será aplicado à função de origem TRIM().

EWIs relacionados

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

Linguagem: Português