SnowConvert: Teradataセッションモード

Teradataセッションモードの説明

Teradataデータベースには、クエリを実行するためのさまざまなモードがあります: ANSI モード（ANSI SQL: 2011仕様に基づくルール）と TERA モード（Teradataが定義したルール）。詳細情報については、以下の Teradataドキュメント を参照してください。

文字列情報テーブルのTeradataモード

文字列の場合、Teradataモードは異なる動作をします。Teradataのドキュメント に基づき、以下のテーブルで説明されています。

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

翻訳仕様概要

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

利用可能な翻訳仕様オプション

文字列比較の ANSI モード - COLLATE

説明

文字列比較と COLLATE 使用の ANSI モード

ANSI モードの文字列比較は、必要に応じて、COLLATE 制約を列またはステートメントに適用します。デフォルトのケース仕様トリム動作が考慮される場合があります。

Teradataでは、デフォルトのケース仕様は'CASESPECIFIC'であり、Snowflakeの'case-sensitive' と同じデフォルトであることに注意してください。したがって、これらのケースは冗長になるため、COLLATE を使用して翻訳されることはありません。

サンプルソースパターン

セットアップデータ

 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

比較操作

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが 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
ケース2: 列制約が CASESPECIFIC で、データベースモードが 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
ケース3: CAST NOT CASESPECIFIC 列を CASESPECIFIC にし、データベースモードが ANSI モード
Teradata
 SELECT * FROM employees WHERE first_name = 'George   ' (CASESPECIFIC);
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Snowflake

注釈

COLLATE 'en-cs'は機能的等価性のために必要です。

 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
ケース4: CAST CASESPECIFIC 列を NOT CASESPECIFIC にし、データベースモードが 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
ケース5: CAST NOT CASESPECIFIC 列を NOT CASESPECIFIC にし、データベースモードが 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

注釈

それには 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

LIKE 操作

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが 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
ケース2: 列制約が CASESPECIFIC で、データベースモードが 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
ケース3: CAST NOT CASESPECIFIC 列を CASESPECIFIC にし、データベースモードが 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
ケース4: CAST CASESPECIFC 列を NOT CASESPECIFIC にし、データベースモードが 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

IN 操作

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが ANSI モード
Teradata
 SELECT *
FROM employees
WHERE first_name IN ('George   ');
Copy
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1            | George      | Snow       | Sales      |
Copy
Snowflake

注釈

このケースには 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
ケース2: 列制約が CASESPECIFIC で、データベースモードが ANSI モード
Teradata

注釈

このケースの場合、列には列制約がありませんが、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

ORDER BY 句

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが 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

警告

FDM をご確認ください。_ 追加を保留中です。 _

 SELECT
   first_name
FROM
   employees
ORDER BY first_name;
Copy
| first\_name  |
| ------------ |
|    GeorgE    |
| **George**   |
| GEORGE       |
| GEORGE       |
| John         |
| JOHN         |
| JOHN         |
| Marco        |
| Mary         |
| WIlle        |
Copy
ケース2: 列制約が CASESPECIFIC で、データベースモードが 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

GROUP BY 句

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが ANSI モード
Teradata
 SELECT first_name
FROM employees
GROUP BY first_name;
Copy
| first\_name  |
| ------------ |
| Mary         |
|    GeorgE    |
| WIlle        |
| **JOHN**     |
| Marco        |
| GEORGE       |
Copy
Snowflake

警告

ケースや順序により出力が異なる場合があります。

注釈

RTRIM は選択された列で必要です。

   SELECT
   first_name
  FROM
   employees
  GROUP BY first_name;
Copy
| first\_name  |
| ------------ |
| **John**     |
| Marco        |
| **George**   |
|    GeorgE    |
| WIlle        |
| Mary         |
Copy
ケース2: 列制約が CASESPECIFIC で、データベースモードが 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
  GROUP BY last_name;
Copy
| first\_name |
| ----------- |
| Snow        |
| SNOW        |
| SnoW        |
|             |
| SnoW        |
| snow        |
Copy

HAVING 句

HAVING 句は、以下のパターンを使用します。

  • 評価操作。

    • 例: =、!=、<、>。

  • LIKE 操作。

  • IN 操作。

  • 評価操作への CAST。

  • LIKE 操作への CAST。

次のサンプルは、評価操作のパターンを示しています。

サンプル: 列制約が NOT CASESPECIFIC で、データベースモードが 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

CASE WHEN ステートメント

CASE WHEN ステートメントでは、以下で説明するパターンを使用します。

  • 評価操作。

    • 例: =、!=、<、>。

  • LIKE 操作。

  • IN 操作。

  • 評価操作への CAST。

  • LIKE 操作への CAST。

次のサンプルは、評価操作のパターンを示しています。

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

JOIN 句

警告

評価操作による単純なシナリオをサポートします。

JOIN ステートメントでは、以下で説明するパターンを使用します。

  • 評価操作。

    • 例: =、!=、<、>。

  • LIKE 操作。

  • IN 操作。

  • 評価操作への CAST。

  • LIKE 操作への CAST。

次のサンプルは、評価操作のパターンを示しています。

サンプル: 列制約が NOT CASESPECIFIC で、データベースモードが 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

注釈

d.department_name は NOT CASESPECIFIC ですので、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

文字列比較の ANSI モード - NO COLLATE

説明

文字列比較と NO COLATE 使用の ANSI モード

COLLATE を使用しない ANSI モードの文字列比較では、必要に応じて RTRIM と UPPER が適用されます。デフォルトのケース仕様トリム動作が考慮されるため、Teradata ANSI モードで列がケース仕様を持っていない場合、Teradataはデフォルトとして CASESPECIFIC を持ちます。

サンプルソースパターン

セットアップデータ

 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

比較操作

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが 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
ケース2: 列制約が CASESPECIFIC で、データベースモードが 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
ケース3: CAST NOT CASESPECIFIC 列を CASESPECIFIC にし、データベースモードが ANSI モード

警告

（CASESPECIFIC）はテーブル定義の列制約を上書きします。

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
ケース4: CAST CASESPECIFIC 列を NOT CASESPECIFIC にし、データベースモードが 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

LIKE 操作

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが 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
ケース2: 列制約が CASESPECIFIC で、データベースモードが 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
ケース3: CAST NOT CASESPECIFIC 列を NOT CASESPECIFIC にし、データベースモードが 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
ケース4: CAST CASESPECIFIC 列を NOT CASESPECIFIC にし、データベースモードが 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

IN 操作

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが 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
ケース2: 列制約が CASESPECIFIC で、データベースモードが 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

ORDER BY 句

注釈

この機能的等価性は異なる可能性があることに注意してください。

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが 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

注釈

FDM をご確認ください。データの挿入順序により順序が異なります。

 SELECT
   department_name
FROM
   departments
ORDER BY
   UPPER(department_name);
Copy
| department      |
| --------------- |
| EngineerinG     |
| Engineering     |
| Finance         |
| Human resources |
| IT              |
| SalEs           |
| SaleS           |
| Sales           |
| salEs           |
| sales           |
Copy
ケース2: 列制約が CASESPECIFIC で、データベースモードが 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

GROUP BY 句

警告

機能的等価性を保証するためには、 COLLATE 式を使用する必要があります。

詳しくは SSC-EWI-TD0007をご覧ください。

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが 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
ケース2: 列制約が CASESPECIFIC で、データベースモードが 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

HAVING 句

HAVING 句は、以下のパターンを使用します。

  • 評価操作。

    • 例: =、!=、<、>。

  • LIKE 操作。

  • IN 操作。

  • 評価操作への CAST。

  • LIKE 操作への CAST。

次のサンプルは、評価操作のパターンを示しています。

サンプル: 列制約が NOT CASESPECIFIC で、データベースモードが 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

CASE WHEN ステートメント

CASE WHEN ステートメントでは、以下で説明するパターンを使用します。

  • 評価操作。

    • 例: =、!=、<、>。

  • LIKE 操作。

  • IN 操作。

  • 評価操作への CAST。

  • LIKE 操作への CAST。

次のサンプルは、評価操作のパターンを示しています。

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

JOIN 句

警告

単純なシナリオがサポートされています。

JOIN ステートメントでは、以下で説明するパターンを使用します。

  • 評価操作。

    • 例: =、!=、<、>。

  • LIKE 操作。

  • IN 操作。

  • 評価操作への CAST。

  • LIKE 操作への CAST。

次のサンプルは、評価操作のパターンを示しています。

サンプル: 列制約が CASESPECIFIC で、データベースモードが 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

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

文字列比較の TERA モード - COLLATE

説明

文字列比較と COLLATE 使用のTeraモード

Teraモードの文字列比較は、必要に応じて、 COLLATE 制約を列またはステートメントに適用します。デフォルトのケース仕様トリム動作が考慮される場合があります。Teradataの TERA モードのデフォルトの大文字と小文字の指定は NOT CASESPECIFIC です。したがって、大文字と小文字の指定がない列は、 COLLATE('en-ci') の制約を持つことになります。

サンプルソースパターン

セットアップデータ

 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

比較操作

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが 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
ケース2: 列制約が CASESPECIFIC で、データベースモードが 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
ケース3: CAST NOT CASESPECIFIC 列を CASESPECIFIC にし、データベースモードが TERA モード

注釈

以下のクエリに注目してください

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

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

は同じ値を返します。

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
ケース4: CAST CASESPECIFIC 列を NOT CASESPECIFIC にし、データベースモードが TERA モード

注釈

比較の左側の列への CAST が優先されます。

\ 例:

  • SELECT * FROM employees WHERE last_name (NOT CASESPECIFIC) = 'snoW'; _では、 5行が返されます。 _

  • SELECT * FROM employees WHERE last_name = 'snoW' (NOT CASESPECIFIC); では、このセットアップデータで 0行 が返されます。

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

LIKE 操作

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが 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
ケース2: 列制約が CASESPECIFIC で、データベースモードが 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
ケース3: CAST NOT CASESPECIFIC 列を CASESPECIFIC にし、データベースモードが 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
ケース4: CAST CASESPECIFIC 列を NOT CASESPECIFIC にし、データベースモードが 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

IN 操作

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが 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
ケース2: 列制約が定義されておらず、データベースモードが TERA モード

注釈

Teraモードでは、大文字と小文字の指定が定義されていない場合、 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
ケース3: 列制約が CASESPECIFIC で、データベースモードが 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

ORDER BY 句

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが 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
ケース2: 列制約が CASESPECIFIC で、データベースモードが 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

GROUP BY 句

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが TERA モード
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
GROUP BY first_name;
Copy
| first\_name  |
| ------------ |
| **John**     |
| Marco        |
| **George**   |
|    GeorgE    |
| WIlle        |
| Mary         |
Copy
ケース2: 列制約が CASESPECIFIC で、データベースモードが 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

HAVING 句

HAVING 句は、以下のパターンを使用します。

  • 評価操作。

    • 例: =、!=、<、>。

  • LIKE 操作。

  • IN 操作。

  • 評価操作への CAST。

  • LIKE 操作への CAST。

次のサンプルは、評価操作のパターンを示しています。

サンプル: 列制約が NOT CASESPECIFIC で、データベースモードが TERA モード
Teradata

注釈

出力の大文字と小文字の指定は、選択した列数によって異なる場合があります。これは 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

CASE WHEN ステートメント

CASE WHEN ステートメントでは、以下で説明するパターンを使用します。

  • 評価操作。

    • 例: =、!=、<、>。

  • LIKE 操作。

  • IN 操作。

  • 評価操作への CAST。

  • LIKE 操作への CAST。

次のサンプルは、評価操作のパターンを示しています。

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

JOIN 句

警告

評価操作による単純なシナリオをサポートします。

JOIN ステートメントでは、以下で説明するパターンを使用します。

  • EvaluaComparisonComparisontion 操作 。

    • 例: =、!=、<、>。

  • LIKE 操作。

  • IN 操作。

  • 評価操作への CAST。

  • LIKE 操作への CAST。

次のサンプルは、評価操作のパターンを示しています。

サンプル: 列制約が NOT CASESPECIFIC で、データベースモードが 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

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

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

文字列比較の TERA モード - NO COLLATE

説明

文字列比較と NO COLLATE 使用のTeraモード

COLLATE を使用しないTeraモードの文字列比較では、必要に応じて RTRIM と UPPER が適用されます。デフォルトのケース仕様トリム動作が考慮される場合があります。

サンプルソースパターン

セットアップデータ

 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

比較操作

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが TERA モード

この例では、 first_name 列であるため、 NOT CASESPECIFIC としてセットアップされた列の使用を示します。文字列 「GEorge」 を要求する場合でも、クエリの実行では大文字と小文字の指定が考慮されないため、Teradataで結果が取得されます。

Snowflakeでこのシナリオをエミュレートするために、2つの関数が実装されています。文字列では大文字と小文字の指定が確認されないため、このシナリオでは RTRIM(UPPER(string_evaluation))、 UPPER が必要です。

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
ケース2: 列制約が CASESPECIFIC で、データベースモードが TERA モード

この例では、列制約は CASESPECIFIC です。「Snow」が「SNOW」と等しくないため、この例ではTeradataの行を取得しません。

Snowflakeでは、大文字と小文字の指定が重要であるため、結果の移行は RTRIM 関数の使用のみを指します。

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
ケース3: CAST CASESPECIFIC 列を NOT CASESPECIFIC にし、データベースモードが TERA モード
Teradata

警告

（CASESPECIFIC）はテーブル定義の列制約を上書きします。

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

注釈

左側には RTRIM が必須で、右側には RTRIM が必須です。

 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
ケース4: CAST NOT CASESPECIFIC 列を NOT CASESPECIFIC にし、データベースモードが 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
ケース5: 空白スペースのケース。列制約は NOT CASESPECIFIC、データベースモードは TERA モードで、等価演算を使用しています
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

LIKE 操作

注釈

この操作は別の操作とは異なります。情報を取得するためには、空白スペースは同じ量でなければなりません。

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが TERA モード

この例では、大文字と小文字の指定は関係ないため、1行が表示されます。

注釈

Snowflakeでは、移行は ILIKE 操作を使用します。これは大文字と小文字を区別しない比較を実行します。

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
ケース2: 列制約が CASESPECIFIC で、データベースモードが 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
ケース3: CAST CASESPECIFIC 列を NOT CASESPECIFIC にし、データベースモードが 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
ケース4: CAST NOT CASESPECIFIC 列を NOT CASESPECIFIC にし、データベースモードが ANSI モード

注釈

このケースは 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

IN 操作

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが 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
ケース2: 列制約が CASESPECIFIC で、データベースモードが TERA モード

この例では、Teradataデータベースでは大文字と小文字の指定が結果に関係するため、 UPPER 関数の使用は必要ありません。

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

ORDER BY 句

ケース1: 列制約が NOT CASESPECIFIC で、データベースモードが TERA モード

この出力順序は異なる可能性があることに注意してください。

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

この出力は順序が異なる可能性があることに注意してください。

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

GROUP BY 句

警告

この出力は異なる可能性があることに注意してください。機能的等価性を保証するためには、 COLLATE 式を使用する必要があります。

詳細は SSC-EWI-TD0007 をご覧ください。

以下は、 collate を使用しない回避策です。

SELECT RTRIM(UPPER(first_name))

FROM employees

GROUP BY RTRIM(UPPER(first_name));

列動作について

Snowflakeのデータ挿入についてご確認ください。Snowflakeでは、大文字と小文字の指定が列と明示的に結合されていないため、エラーを表示することなく'GEORGE'や'georges'のような値を挿入することができます。

以下のようなテーブルとデータがあるとします。

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

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

このサンプルでは、 NOT CASESPECIFIC 列に大文字と小文字の値を挿入することができないことに注意してください。これは、同じ値とみなされるからです。列は大文字と小文字の指定を管理しないため、'GEORGE'と'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');
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

HAVING 句は、以下のパターンを使用します。

  • 評価操作。

    • 例: =、!=、<、>。

  • LIKE 操作。

  • IN 操作。

  • 評価操作への CAST。

  • LIKE 操作への CAST。

次のサンプルは、評価操作のパターンを示しています。

サンプル: 列制約が CASESPECIFIC で、データベースモードが 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

CASE WHEN ステートメントでは、以下で説明するパターンを使用します。

  • 評価操作。

    • 例: =、!=、<、>。

  • LIKE 操作。

  • IN 操作。

  • 評価操作への CAST。

  • LIKE 操作への CAST。

次のサンプルは、評価操作のパターンを示しています。

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

警告

単純なシナリオがサポートされています。

JOIN ステートメントでは、以下で説明するパターンを使用します。

  • 評価操作。

    • 例: =、!=、<、>。

  • LIKE 操作。

  • IN 操作。

  • 評価操作への CAST。

  • LIKE 操作への CAST。

次のサンプルは、評価操作のパターンを示しています。

サンプル: 列制約が NOT CASESPECIFIC で、データベースモードが 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

既知の問題

  1. モード固有の SQL ステートメント制限があります: BEGIN TRANSACTION、 END TRANSACTION、 COMMIT [WORK]。

  2. Snowflakeでは、大文字と小文字の指定が列宣言に束縛されないため、データ挿入が異なる場合があります。

  3. GROUP BY は順序が異なる場合がありますが、正しい値をグループ化します。

  4. ORDER BY はSnowflakeでは異なる動作をします。

  5. 関数がソースコードから TRIM()を持っている場合、この回避策はソースコードに必要な関数を追加します。そのため、 RTRIM は TRIM()ソース関数に適用されます。

関連 EWIs

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

言語: 日本語