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) |
변환 사양 요약¶
Mode | Column constraint values | Teradata behavior | SC expected behavior |
---|---|---|---|
ANSI Mode | CASESPECIFIC | CASESPECIFIC | No constraint added. |
NOT CASESPECIFIC | CASESPECIFIC | Add COLLATE 'en-cs' in column definition. | |
Teradata Mode | CASESPECIFIC | CASESPECIFIC | In most cases, do not add COLLATE, and convert its usages of string comparison to RTRIM( expression ) |
NOT CASESPECIFIC | NOT CASESPECIFIC | In most cases, do not add COLLATE, and convert its usages of string comparison to RTRIM(UPPER( expression )) |
사용 가능한 변환 사양 옵션¶
문자열 비교를 위한 ANSI 모드 - COLLATE¶
설명 ¶
문자열 비교 및 COLLATE 사용을 위한 ANSI 모드¶
ANSI 모드 문자열 비교는 필수에 따라 열 또는 문에 COLLATE 제약 조건을 적용합니다. 기본 대/소문자 사양 트림 동작을 고려할 수 있습니다.
Teradata에서 기본 대/소문자 사양은 ‘CASESPECIFIC
’이며, 이는 Snowflake ‘대/소문자 구분'
과 동일한 기본값입니다. 따라서 이러한 경우에는 중복되므로 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');
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');
비교 연산¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE first_name = 'GEorge ';
```none
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
COLLATE(first_name, 'en-cs-rtrim') = RTRIM('George');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE last_name = 'SNOW ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) = RTRIM('SNOW ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
케이스 3: CAST NOT CASESPECIFIC 열을 CASESPECIFIC 으로, 데이터베이스 모드는 ANSI 모드¶
Teradata¶
SELECT * FROM employees WHERE first_name = 'George ' (CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
참고
COLLATE ‘en-cs’는 기능적 동등성을 위해 필요합니다.
SELECT
*
FROM
employees
WHERE
COLLATE(first_name, 'en-cs-rtrim') = 'George ' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
케이스 4: CAST CASESPECIFIC 열을 NOT CASESPECIFIC 으로, 데이터베이스 모드는 ANSI 모드¶
Teradata¶
SELECT * FROM employees WHERE first_name = 'GEorge ' (NOT CASESPECIFIC) ;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
| 7 | GEORGE | | salEs |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) = RTRIM('GEorge ' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
| 7 | GEORGE | | salEs |
케이스 5: CAST NOT CASESPECIFIC 열을 NOT CASESPECIFIC 으로, 데이터베이스 모드는 ANSI 모드¶
Teradata¶
SELECT * FROM employees WHERE first_name (NOT CASESPECIFIC) = 'George ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
참고
여기에는 COLLATE 가 필요합니다.
SELECT
*
FROM
employees
WHERE
COLLATE(first_name /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/, 'en-cs-rtrim') = 'George ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
LIKE 작업¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'George';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
SELECT *
FROM employees
WHERE COLLATE(first_name, 'en-cs-rtrim') LIKE 'George';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
Snowflake¶
SELECT *
FROM employees
WHERE RTRIM(last_name) LIKE RTRIM('Snow');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
케이스 3: CAST NOT CASESPECIFIC 열을 CASESPECIFIC 으로, 데이터베이스 모드는 ANSI 모드¶
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'Mary' (CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 5 | Mary | | SaleS |
Snowflake¶
SELECT
*
FROM
employees
WHERE
COLLATE(first_name, 'en-cs-rtrim') LIKE 'Mary' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 5 | Mary | | SaleS |
케이스 4: CAST CASESPECIFC 열을 NOT CASESPECIFIC 으로, 데이터베이스 모드는 ANSI 모드¶
Teradata¶
SELECT *
FROM employees
WHERE last_name LIKE 'SNO%' (NOT CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) LIKE RTRIM('SNO%' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
IN 작업¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE first_name IN ('George ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
참고
이 경우 COLLATE(
_ column_name
_ , 'en-cs-rtrim')
가 필요합니다
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) IN (COLLATE('George ', 'en-cs-rtrim'));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
참고
이 경우 열에 열 제약 조건이 없지만 Teradata ANSI 모드의 기본 제약 조건은 CASESPECIFIC
입니다.
SELECT *
FROM employees
WHERE department IN ('EngineerinG ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ----------- |
| 4 | Marco | SnoW | EngineerinG |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(department) IN (RTRIM('EngineerinG '));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ----------- |
| 4 | Marco | SnoW | EngineerinG |
ORDER BY 절¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT first_name
FROM employees
ORDER BY first_name;
| first\_name |
| ------------ |
| GeorgE |
| GEORGE |
| GEORGE |
| **George** |
| John |
| JOHN |
| JOHN |
| Marco |
| Mary |
| WIlle |
Snowflake¶
경고
FDM 을 검토하십시오. _ 추가할 보류 중. _
SELECT
first_name
FROM
employees
ORDER BY first_name;
| first\_name |
| ------------ |
| GeorgE |
| **George** |
| GEORGE |
| GEORGE |
| John |
| JOHN |
| JOHN |
| Marco |
| Mary |
| WIlle |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT last_name
FROM employees
ORDER BY last_name;
| department |
| --------------- |
| EngineerinG |
| Engineering |
| Finance |
| Human resources |
| IT |
| SalEs |
| SaleS |
| Sales |
| salEs |
| sales |
Snowflake¶
SELECT
last_name
FROM
employees
ORDER BY last_name;
| department |
| --------------- |
| EngineerinG |
| Engineering |
| Finance |
| Human resources |
| IT |
| SalEs |
| SaleS |
| Sales |
| salEs |
| sales |
GROUP BY 절¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT first_name
FROM employees
GROUP BY first_name;
| first\_name |
| ------------ |
| Mary |
| GeorgE |
| WIlle |
| **JOHN** |
| Marco |
| GEORGE |
Snowflake¶
경고
케이스 또는 순서에 따라 출력 내용이 다를 수 있습니다.
참고
RTRIM
은 선택한 열에 필수입니다.
SELECT
first_name
FROM
employees
GROUP BY first_name;
| first\_name |
| ------------ |
| **John** |
| Marco |
| **George** |
| GeorgE |
| WIlle |
| Mary |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT last_name
FROM employees
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| |
| SNOW |
| SnoW |
| Snow |
| snow |
Snowflake¶
참고
순서는 다를 수 있습니다.
SELECT
last_name
FROM
employees
GROUP BY last_name;
| first\_name |
| ----------- |
| Snow |
| SNOW |
| SnoW |
| |
| SnoW |
| snow |
HAVING 절¶
HAVING 절은 다음에서 이 패턴을 사용합니다.
평가 연산.
예:
=, !=, <, >.
LIKE 작업.
IN 작업.
CAST 를 평가 연산으로
CAST 를 LIKE 연산으로
다음 샘플은 평가 작업이 포함된 패턴을 보여줍니다.
샘플: 열 제약 조건은 NOT CASESPECIFIC , 데이터베이스 모드는 ANSI 모드입니다¶
Teradata¶
SELECT first_name
FROM employees
GROUP BY first_name
HAVING first_name = 'Mary';
Mary
Snowflake¶
SELECT
first_name
FROM
employees
GROUP BY first_name
HAVING
COLLATE(first_name, 'en-cs-rtrim') = 'Mary';
Mary
CASE WHEN 문¶
CASE WHEN
문은 설명된 패턴을 사용합니다.
평가 연산.
예:
=, !=, <, >.
LIKE 작업.
IN 작업.
CAST 를 평가 연산으로
CAST 를 LIKE 연산으로
다음 샘플은 평가 작업이 포함된 패턴을 보여줍니다.
Teradata¶
SELECT first_name,
last_name,
CASE
WHEN department = 'EngineerinG' THEN 'Information Technology'
WHEN first_name = ' GeorgE ' THEN 'GLOBAL SALES'
ELSE 'Other'
END AS department_full_name
FROM employees
WHERE last_name = '';
| first\_name | last\_name | department\_full\_name |
| ------------- | ---------- | ---------------------- |
| GEORGE | | Other |
| Mary | | Other |
| GeorgE | | GLOBAL SALES |
| GEORGE | | Other |
Snowflake¶
SELECT
first_name,
last_name,
CASE
WHEN RTRIM(department) = RTRIM('EngineerinG')
THEN 'Information Technology'
WHEN COLLATE(first_name, 'en-cs-rtrim') = ' GeorgE '
THEN 'GLOBAL SALES'
ELSE 'Other'
END AS department_full_name
FROM
employees
WHERE RTRIM(last_name) = RTRIM('');
| first\_name | last\_name | department\_full\_name |
| ------------- | ---------- | ---------------------- |
| Mary | | Other |
| GEORGE | | Other |
| GEORGE | | Other |
| GeorgE | | GLOBAL SALES |
JOIN 절¶
경고
평가 작업이 포함된 간단한 시나리오가 지원됩니다.
JOIN
문은 설명된 패턴을 사용합니다.
평가 연산.
예:
=, !=, <, >.
LIKE 작업.
IN 작업.
CAST 를 평가 연산으로
CAST 를 LIKE 연산으로
다음 샘플은 평가 작업이 포함된 패턴을 보여줍니다.
샘플: 열 제약 조건은 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;
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ----------- | ---------- | ---------------- |
| 1 | George | Snow | Sales |
| 10 | JOHN | snow | Finance |
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;
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ----------- | ---------- | ---------------- |
| 1 | George | Snow | Sales |
| 10 | JOHN | snow | Finance |
문자열 비교를 위한 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');
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');
비교 연산¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE first_name = 'George ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) = RTRIM('George ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE last_name = 'SNOW ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) = RTRIM('SNOW ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
케이스 3: CAST NOT CASESPECIFIC 열을 CASESPECIFIC 으로, 데이터베이스 모드는 ANSI 모드¶
경고
(CASESPECIFIC
)는 테이블 정의의 열 제약 조건을 덮어씁니다.
Teradata¶
SELECT * FROM employees WHERE first_name = 'GEorge ' (CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT * FROM workers
WHERE RTRIM(first_name) = RTRIM(UPPER('GEorge '));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 6 | GEORGE | | sales |
케이스 4: CAST CASESPECIFIC 열을 NOT CASESPECIFIC 으로, 데이터베이스 모드는 ANSI 모드¶
Teradata¶
SELECT * FROM employees
WHERE last_name = 'SnoW ' (NOT CASESPECIFIC) ;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ----------- |
| 4 | Marco | SnoW | EngineerinG |
Snowflake¶
SELECT * FROM employees
WHERE RTRIM(last_name) = RTRIM('SnoW ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ----------- |
| 4 | Marco | SnoW | EngineerinG |
LIKE 작업¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'Georg%';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
SELECT *
FROM employees
WHERE first_name LIKE 'Georg%';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
케이스 3: CAST NOT CASESPECIFIC 열을 NOT CASESPECIFIC 으로, 데이터베이스 모드는 ANSI 모드¶
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'George' (NOT CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
first_name ILIKE 'George' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
케이스 4: CAST CASESPECIFIC 열을 NOT CASESPECIFIC 으로, 데이터베이스 모드는 ANSI 모드¶
Teradata¶
SELECT *
FROM employees
WHERE last_name LIKE 'SNO%' (NOT CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
Snowflake¶
SELECT
*
FROM
employees
WHERE
last_name LIKE 'SNO%' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
IN 작업¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE first_name IN ('GEORGE ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 6 | GEORGE | | sales |
| 7 | GEORGE | | salEs |
Snowflake¶
SELECT *
FROM employees
WHERE RTRIM(first_name) IN (RTRIM('GEORGE '));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 6 | GEORGE | | sales |
| 7 | GEORGE | | salEs |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE department IN ('SaleS');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 5 | Mary | | SaleS |
Snowflake¶
SELECT *
FROM employees
WHERE RTRIM(department) IN (RTRIM('SaleS'));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 5 | Mary | | SaleS |
ORDER BY 절¶
참고
이 기능적 동등성은 다를 수 있다는 점에 유의하십시오.
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT department_name
FROM departments
ORDER BY department_name;
| department |
| --------------- |
| EngineerinG |
| Engineering |
| Finance |
| Human resources |
| IT |
| SalEs |
| SaleS |
| Sales |
| salEs |
| sales |
Snowflake¶
참고
FDM 을 검토하십시오. 데이터 삽입 순서에 따라 순서가 달라집니다.
SELECT
department_name
FROM
departments
ORDER BY
UPPER(department_name);
| department |
| --------------- |
| EngineerinG |
| Engineering |
| Finance |
| Human resources |
| IT |
| SalEs |
| SaleS |
| Sales |
| salEs |
| sales |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT last_name
FROM employees
ORDER BY last_name;
| department |
| ---------------------- |
| Finance |
| Human Resources |
| Information Technology |
| Sales |
Snowflake¶
SELECT last_name
FROM employees
ORDER BY last_name;
| department |
| ---------------------- |
| Finance |
| Human Resources |
| Information Technology |
| Sales |
GROUP BY 절¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT first_name
FROM employees
GROUP BY first_name;
| first\_name |
| ------------- |
| Mary |
| GeorgE |
| WIlle |
| John |
| Marco |
| GEORGE |
Snowflake¶
SELECT
first_name
FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY first_name;
| FIRST\_NAME |
| ------------- |
| George |
| John |
| WIlle |
| Marco |
| Mary |
| GEORGE |
| GEORGE |
| GeorgE |
| JOHN |
| JOHN |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 ANSI 모드인 경우¶
Teradata¶
SELECT last_name
FROM employees
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| |
| SNOW |
| SnoW |
| Snow |
| snow |
Snowflake¶
SELECT
last_name
FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| |
| SNOW |
| SnoW |
| Snow |
| snow |
HAVING 절¶
HAVING 절은 다음에서 이 패턴을 사용합니다.
평가 연산.
예:
=, !=, <, >.
LIKE 작업.
IN 작업.
CAST 를 평가 연산으로
CAST 를 LIKE 연산으로
다음 샘플은 평가 작업이 포함된 패턴을 보여줍니다.
샘플: 열 제약 조건은 NOT CASESPECIFIC , 데이터베이스 모드는 ANSI 모드입니다¶
Teradata¶
SELECT first_name
FROM employees
GROUP BY first_name
HAVING first_name = 'GEORGE';
GEORGE
Snowflake¶
SELECT
first_name
FROM
employees
GROUP BY first_name
HAVING
RTRIM(first_name) = RTRIM('GEORGE');
GEORGE
CASE WHEN 문¶
CASE WHEN
문은 설명된 패턴을 사용합니다.
평가 연산.
예:
=, !=, <, >.
LIKE 작업.
IN 작업.
CAST 를 평가 연산으로
CAST 를 LIKE 연산으로
다음 샘플은 평가 작업이 포함된 패턴을 보여줍니다.
Teradata¶
SELECT first_name,
last_name,
CASE
WHEN department = 'SaleS ' THEN 'GLOBAL SALES'
WHEN first_name = 'GEORGE ' THEN 'Department Full Name'
ELSE 'Other'
END AS department_full_name
FROM employees
WHERE last_name = ' ';
first_name | last_name | department_full_name |
---|---|---|
GEORGE | Department Full Name | |
Mary | GLOBAL SALES | |
GeorgE | Other | |
GEORGE | Department Full Name |
Snowflake¶
SELECT first_name,
last_name,
CASE
WHEN RTRIM(department) = RTRIM('SaleS ') THEN 'GLOBAL SALES'
WHEN RTRIM(first_name) = RTRIM('GEORGE ') THEN 'Department Full Name'
ELSE 'Other'
END AS department_full_name
FROM employees
WHERE RTRIM(last_name) = RTRIM(' ');
first_name | last_name | department_full_name |
---|---|---|
GEORGE | Department Full Name | |
Mary | GLOBAL SALES | |
GeorgE | Other | |
GEORGE | Department Full Name |
JOIN 절¶
경고
간단한 시나리오가 지원됩니다.
JOIN
문은 설명된 패턴을 사용합니다.
평가 연산.
예:
=, !=, <, >.
LIKE 작업.
IN 작업.
CAST 를 평가 연산으로
CAST 를 LIKE 연산으로
다음 샘플은 평가 작업이 포함된 패턴을 보여줍니다.
샘플: 열 제약 조건은 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;
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ----------- | ---------- | ---------------- |
| 1 | George | Snow | Sales |
| 10 | JOHN | snow | Finance |
Snowflake¶
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name
FROM
employees e
JOIN
departments d
ON RTRIM(e.department) = RTRIM(d.department_name);
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ----------- | ---------- | ---------------- |
| 1 | George | Snow | Sales |
| 10 | JOHN | snow | Finance |
관련 EWIs¶
SSC-EWI-TD0007: GROUP BY IS 는 TERADATA 모드와 동일하지 않습니다.
문자열 비교를 위한 TERA 모드 - COLLATE¶
설명 ¶
문자열 비교 및 COLLATE 사용을 위한 Tera 모드¶
Tera Mode 문자열 비교는 필수에 따라 열 또는 문에 COLLATE 제약 조건을 적용합니다. 기본 대/소문자 사양 트림 동작을 고려할 수 있습니다. TERA 모드에 대한 Teradata의 기본 대/소문자 사양은 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');
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');
비교 연산¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE first_name = 'GEorge ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) = RTRIM('GEorge ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE last_name = 'SNOW ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) = RTRIM('SNOW ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
케이스 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);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 9 | JOHN | SnoW | IT |
| 10 | JOHN | snow | Finance |
Snowflake¶
SELECT
*
FROM
employees
WHERE
COLLATE(first_name, 'en-cs-rtrim') = 'JOHN ' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 9 | JOHN | SnoW | IT |
| 10 | JOHN | snow | Finance |
케이스 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' ;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 1 | George | Snow | Sales |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
| 4 | Marco | SnoW | EngineerinG |
| 10 | JOHN | snow | Finance |
Snowflake¶
SELECT
*
FROM
employees
WHERE
COLLATE(last_name /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/, 'en-ci-rtrim') = 'snoW' ;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 1 | George | Snow | Sales |
| 2 | John | SNOW | Engineering |
| 3 | WIlle | SNOW | Human resources |
| 4 | Marco | SnoW | EngineerinG |
| 10 | JOHN | snow | Finance |
LIKE 작업¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'GeorgE';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) LIKE RTRIM('GeorgE');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) LIKE RTRIM('Snow');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
케이스 3: CAST NOT CASESPECIFIC 열을 CASESPECIFIC 으로, 데이터베이스 모드는 TERA 모드¶
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'George' (CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
COLLATE(first_name, 'en-cs-rtrim') LIKE 'George';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
케이스 4: CAST CASESPECIFIC 열을 NOT CASESPECIFIC 으로, 데이터베이스 모드는 TERA 모드¶
Teradata¶
SELECT *
FROM employees
WHERE last_name LIKE 'SNO%' (NOT CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) LIKE RTRIM('SNO%' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
IN 작업¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE first_name IN ('George ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) IN (RTRIM('George '));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
케이스 2: 열 제약 조건이 정의되지 않았고 데이터베이스 모드가 TERA 모드인 경우¶
참고
Tera 모드에서 정의되지 않은 대/소문자 사양은 NOT CASESPECIFIC
을 의미합니다.
Teradata¶
SELECT *
FROM employees
WHERE department IN ('Sales ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ------------ | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 5 | Mary | | SaleS |
| 6 | GEORGE | | sales |
| 7 | GEORGE | | salEs |
| 8 | GeorgE | | SalEs |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(department) IN (RTRIM('Sales '));
| employee\_id | first\_name | last\_name | department |
| ------------ | ------------ | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 5 | Mary | | SaleS |
| 6 | GEORGE | | sales |
| 7 | GEORGE | | salEs |
| 8 | GeorgE | | SalEs |
케이스 3: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE last_name IN ('SNOW ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) IN (RTRIM('SNOW '));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
ORDER BY 절¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
Teradata¶
SELECT employee_id, first_name
FROM employees
ORDER BY employee_id, first_name;
employee_id | first_name |
---|---|
1 | George |
2 | John |
3 | WIlle |
4 | Marco |
5 | Mary |
6 | GEORGE |
7 | GEORGE |
8 | GeorgE |
9 | JOHN |
10 | JOHN |
Snowflake¶
SELECT employee_id, first_name
FROM employees
ORDER BY employee_id, first_name;
employee_id | first_name |
---|---|
1 | George |
2 | John |
3 | WIlle |
4 | Marco |
5 | Mary |
6 | GEORGE |
7 | GEORGE |
8 | GeorgE |
9 | JOHN |
10 | JOHN |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
Teradata¶
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id, last_name;
employee_id | last_name |
---|---|
1 | Snow |
2 | SNOW |
3 | SNOW |
4 | SnoW |
5 | |
6 | |
7 | |
8 | |
9 | SnoW |
10 | snow |
Snowflake¶
SELECT employee_id, last_name
FROM employees
ORDER BY employee_id, last_name;
employee_id | last_name |
---|---|
1 | Snow |
2 | SNOW |
3 | SNOW |
4 | SnoW |
5 | |
6 | |
7 | |
8 | |
9 | SnoW |
10 | snow |
GROUP BY 절¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
Teradata¶
SELECT first_name
FROM employees
GROUP BY first_name;
| first\_name |
| ------------ |
| Mary |
| GeorgE |
| WIlle |
| **JOHN** |
| Marco |
| **GEORGE** |
Snowflake¶
경고
출력의 대/소문자 지정은 선택한 열 수에 따라 달라질 수 있습니다.
SELECT
first_name
FROM
employees
GROUP BY first_name;
| first\_name |
| ------------ |
| **John** |
| Marco |
| **George** |
| GeorgE |
| WIlle |
| Mary |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
Teradata¶
SELECT last_name
FROM employees
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| |
| SNOW |
| SnoW |
| Snow |
| snow |
Snowflake¶
SELECT
last_name
FROM
employees
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| |
| SNOW |
| SnoW |
| Snow |
| snow |
HAVING 절¶
HAVING 절은 다음에서 이 패턴을 사용합니다.
평가 연산.
예:
=, !=, <, >.
LIKE 작업.
IN 작업.
CAST 를 평가 연산으로
CAST 를 LIKE 연산으로
다음 샘플은 평가 작업이 포함된 패턴을 보여줍니다.
샘플: 열 제약 조건은 NOT CASESPECIFIC , 데이터베이스 모드는 TERA 모드입니다¶
Teradata¶
참고
출력의 대/소문자 지정은 선택한 열 수에 따라 달라질 수 있습니다. 이는 GROUP BY
절과도 관련이 있습니다.
SELECT first_name
FROM employees
GROUP BY first_name
HAVING first_name = 'George ';
employee_id | first_name |
---|---|
7 | GEORGE |
1 | George |
6 | GEORGE |
Snowflake¶
SELECT
employee_id,
first_name
FROM
employees
GROUP BY employee_id, first_name
HAVING
RTRIM(first_name) = RTRIM('George ');
employee_id | first_name |
---|---|
7 | GEORGE |
1 | George |
6 | GEORGE |
CASE WHEN 문¶
CASE WHEN
문은 설명된 패턴을 사용합니다.
평가 연산.
예:
=, !=, <, >.
LIKE 작업.
IN 작업.
CAST 를 평가 연산으로
CAST 를 LIKE 연산으로
다음 샘플은 평가 작업이 포함된 패턴을 보여줍니다.
Teradata¶
SELECT first_name,
last_name,
CASE
WHEN department = 'Engineering' THEN 'Information Technology'
WHEN first_name = 'GeorgE' THEN 'GLOBAL SALES'
ELSE 'Other'
END AS department_full_name
FROM employees
WHERE last_name = '';
| first\_name | last\_name | department\_full\_name |
| ------------ | ---------- | ---------------------- |
| GEORGE | | GLOBAL SALES |
| Mary | | Other |
| GeorgE | | Other |
| GEORGE | | GLOBAL SALES |
Snowflake¶
SELECT
first_name,
last_name,
CASE
WHEN RTRIM(department) = RTRIM('Engineering')
THEN 'Information Technology'
WHEN RTRIM(first_name) = RTRIM('GeorgE')
THEN 'GLOBAL SALES'
ELSE 'Other'
END AS department_full_name
FROM
employees
WHERE
RTRIM( last_name) = RTRIM('');
| first\_name | last\_name | department\_full\_name |
| ------------ | ---------- | ---------------------- |
| GEORGE | | GLOBAL SALES |
| Mary | | Other |
| GeorgE | | Other |
| GEORGE | | GLOBAL SALES |
JOIN 절¶
경고
평가 작업이 포함된 간단한 시나리오가 지원됩니다.
JOIN
문은 설명된 패턴을 사용합니다.
EvaluaComparisonComparisontion 작업.
예:
=, !=, <, >.
LIKE 작업.
IN 작업.
CAST 를 평가 연산으로
CAST 를 LIKE 연산으로
다음 샘플은 평가 작업이 포함된 패턴을 보여줍니다.
샘플: 열 제약 조건은 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;
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ------------ | ---------- | ---------------- |
| 1 | George | Snow | Sales |
| 3 | WIlle | SNOW | Human Resources |
| 5 | Mary | | Sales |
| 6 | GEORGE | | Sales |
| 7 | GEORGE | | Sales |
| 8 | GeorgE | | Sales |
| 10 | JOHN | snow | Finance |
Snowflake¶
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name
FROM
employees e
JOIN
departments d
ON RTRIM(e.department) = RTRIM(d.department_name);
| employee\_id | first\_name | last\_name | department\_name |
| ------------ | ------------ | ---------- | ---------------- |
| 1 | George | Snow | Sales |
| 3 | WIlle | SNOW | Human Resources |
| 5 | Mary | | Sales |
| 6 | GEORGE | | Sales |
| 7 | GEORGE | | Sales |
| 8 | GeorgE | | Sales |
| 10 | JOHN | snow | Finance |
관련 EWIs¶
SSC-EWI-TD0007: GROUP BY REQUIRED COLLATE FOR CASE INSENSITIVE COLUMNS
SC-FDM-TD0032 : [NOT] 특정 절이 제거되었습니다.
문자열 비교를 위한 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');
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');
비교 연산¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
이 예제는 NOT CASESPECIFIC
으로 설정된 열을 first_name
열로 사용하는 방법을 보여 줍니다. 'GEorge',
문자열을 요청하는 경우에도 대/소문자 사양이 고려되지 않기 때문에 쿼리 실행 시 Teradata에서 결과를 검색합니다.
Snowflake에는 이 시나리오를 에뮬레이션하기 위해 두 가지 함수가 구현되어 있습니다. 이 시나리오에서는 문자열이 대/소문자 구분을 검토하지 않기 때문에 RTRIM(UPPER(string_evaluation))
, UPPER
가 필요합니다.
Teradata¶
SELECT *
FROM employees
WHERE first_name = 'GEorge ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(UPPER(first_name)) = RTRIM(UPPER('GEorge '));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
이 예의 경우 열 제약 조건은 CASESPECIFIC
이며, ‘Snow
’는 ‘SNOW
’와 같지 않기 때문에 이 예제에서는 Teradata에서 행을 검색하지 않습니다.
Snowflake에서는 대/소문자 사양이 중요하므로 결과 마이그레이션은 RTRIM
함수의 사용만을 가리킵니다.
Teradata¶
SELECT *
FROM employees
WHERE last_name = 'SNOW ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) = RTRIM('SNOW ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | --------------- |
| 3 | WIlle | SNOW | Human resources |
| 2 | John | SNOW | Engineering |
케이스 3: CAST CASESPECIFIC 열을 NOT CASESPECIFIC 으로, 데이터베이스 모드는 TERA 모드¶
Teradata¶
경고
(CASESPECIFIC
)은 테이블 정의에서 열 제약 조건을 재정의합니다.
SELECT * FROM employees WHERE first_name = 'GEORGE ' (CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 6 | GEORGE | | sales |
Snowflake¶
참고
RTRIM 은 왼쪽에, RTRIM 은 오른쪽에 입력해야 합니다.
SELECT
*
FROM
employees
WHERE
RTRIM(first_name) = RTRIM('GEORGE ' /*** SSC-FDM-TD0032 - CASESPECIFIC CLAUSE WAS REMOVED ***/);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 6 | GEORGE | | sales |
케이스 4: CAST NOT CASESPECIFIC 열을 NOT CASESPECIFIC 으로, 데이터베이스 모드는 TERA 모드¶
Teradata¶
SELECT * FROM employees WHERE first_name = 'GEorge ' (NOT CASESPECIFIC) ;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
UPPER(RTRIM(first_name)) = UPPER(RTRIM('GEorge ' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
케이스 5: 공백 케이스. 열 제약 조건은 NOT CASESPECIFIC, 데이터베이스 모드는 TERA 모드이며, 동일 연산을 사용합니다.¶
Teradata¶
SELECT *
FROM employees
WHERE last_name = ' ';
| employee\_id | first\_name | last\_name | department |
| ------------ | ------------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 5 | Mary | | SaleS |
| 8 | GeorgE | | SalEs |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
RTRIM(last_name) = RTRIM(' ');
| employee\_id | first\_name | last\_name | department |
| ------------ | ------------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 5 | Mary | | SaleS |
| 8 | GeorgE | | SalEs |
| 6 | GEORGE | | sales |
LIKE 작업¶
참고
이 작업은 다른 작업과 다르게 작동합니다. 정보를 검색하려면 빈 칸이 같은 수여야 합니다.
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
이 예제에서는 대/소문자 사양이 관련이 없으므로 한 행이 표시될 것으로 예상됩니다.
참고
Snowflake에서 마이그레이션은 ILIKE 연산을 사용합니다. 대/소문자를 구분하지 않는 비교 성능을 수행합니다.
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'GeorgE';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT *
FROM employees
WHERE first_name ILIKE 'GeorgE';
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
| first\_name | last\_name | department |
| ----------- | ---------- | ----------- |
| George | Snow | Sales |
| Jonh | Snow | Engineering |
Snowflake¶
SELECT *
FROM employees
WHERE last_name LIKE 'Snow';
| first\_name | last\_name | department |
| ----------- | ---------- | ----------- |
| George | Snow | Sales |
| Jonh | Snow | Engineering |
케이스 3: CAST CASESPECIFIC 열을 NOT CASESPECIFIC 으로, 데이터베이스 모드는 TERA 모드¶
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'George' (NOT CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
first_name ILIKE 'George' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
케이스 4: CAST NOT CASESPECIFIC 열을 NOT CASESPECIFIC 으로, 데이터베이스 모드는 ANSI 모드¶
참고
이 경우 ILIKE
로 변환해야 합니다.
Teradata¶
SELECT *
FROM employees
WHERE first_name LIKE 'GE%' (NOT CASESPECIFIC);
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT
*
FROM
employees
WHERE
first_name ILIKE 'GE%' /*** SSC-FDM-TD0032 - NOT CASESPECIFIC CLAUSE WAS REMOVED ***/;
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
IN 작업¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
Teradata¶
SELECT *
FROM employees
WHERE first_name IN ('GeorgE');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
Snowflake¶
SELECT *
FROM employees
WHERE RTRIM(UPPER(first_name)) IN (RTRIM(UPPER('GeorgE')));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ---------- |
| 7 | GEORGE | | salEs |
| 1 | George | Snow | Sales |
| 6 | GEORGE | | sales |
케이스 2: 열 제약 조건이 CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
이 예제에서는 Teradata 데이터베이스에서 대/소문자 사양이 결과와 관련이 있으므로 UPPER 함수를 사용할 필요가 없습니다.
Teradata¶
SELECT *
FROM employees
WHERE last_name IN ('SnoW');
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ----------- |
| 4 | Marco | SnoW | EngineerinG |
Snowflake¶
SELECT *
FROM employees
WHERE RTRIM(last_name) IN (RTRIM('SnoW'));
| employee\_id | first\_name | last\_name | department |
| ------------ | ----------- | ---------- | ----------- |
| 4 | Marco | SnoW | EngineerinG |
ORDER BY 절¶
케이스 1: 열 제약 조건이 NOT CASESPECIFIC 이고 데이터베이스 모드가 TERA 모드인 경우¶
이 출력 순서는 다를 수 있습니다.
Teradata
SELECT department
FROM employees
ORDER BY department;
| department |
| --------------- |
| EngineerinG |
| Engineering |
| Finance |
| Human resources |
| IT |
| sales |
| SalEs |
| Sales |
| SaleS |
| salEs |
Snowflake
SELECT department
FROM employees
ORDER BY UPPER(department);
| department |
| --------------- |
| EngineerinG |
| Engineering |
| Finance |
| Human resources |
| IT |
| sales |
| SalEs |
| Sales |
| SaleS |
| salEs |
Case 2: Column constraint is CASESPECIFIC and database mode is TERA Mode
이 출력은 순서가 다를 수 있습니다.
Teradata¶
SELECT last_name
FROM employees
ORDER BY last_name;
| last\_name |
| ---------- |
| |
| |
| |
| |
| SnoW |
| SNOW |
| SNOW |
| SnoW |
| Snow |
| snow |
Snowflake¶
SELECT last_name
FROM employees
ORDER BY last_name;
| last\_name |
| ---------- |
| |
| |
| |
| |
| SnoW |
| SNOW |
| SNOW |
| SnoW |
| Snow |
| snow |
GROUP BY 절¶
경고
이 출력은 다를 수 있습니다. 기능적 동등성을 보장하기 위해 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');
이 샘플에서는 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');
Case 1: Column constraint is NOT CASESPECIFIC and database mode is TERA Mode
Teradata
SELECT first_name
FROM employees
GROUP BY first_name;
| first\_name |
| ------------ |
| Mary |
| GeorgE |
| WIlle |
| JOHN |
| Marco |
| GEORGE |
Snowflake
SELECT
first_name
FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY first_name;
| first\_name |
| ------------ |
| George |
| John |
| WIlle |
| Marco |
| Mary |
| GEORGE |
| GEORGE |
| GeorgE |
| JOHN |
| JOHN |
Case 2: Column constraint is CASESPECIFIC and database mode is TERA Mode
Teradata
SELECT last_name
FROM employees
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| |
| SNOW |
| SnoW |
| Snow |
| snow |
Snowflake
SELECT
last_name
FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0007 - GROUP BY IS NOT EQUIVALENT IN TERADATA MODE ***/!!!
GROUP BY last_name;
| last\_name |
| ---------- |
| SnoW |
| SNOW |
| SnoW |
| |
| |
| Snow |
| snow |
HAVING clause
HAVING 절은 다음에서 이 패턴을 사용합니다.
평가 연산.
예:
=, !=, <, >.
LIKE 작업.
IN 작업.
CAST 를 평가 연산으로
CAST 를 LIKE 연산으로
다음 샘플은 평가 작업이 포함된 패턴을 보여줍니다.
샘플: 열 제약 조건은 CASESPECIFIC, 데이터베이스 모드는 TERA 모드입니다
Teradata
SELECT last_name
FROM employees
GROUP BY last_name
HAVING last_name = 'Snow';
| last\_name |
| ---------- |
| Snow |
Snowflake
SELECT last_name
FROM employees
GROUP BY last_name
HAVING RTRIM(last_name) = RTRIM('Snow');
| last\_name |
| ---------- |
| Snow |
CASE WHEN statement
CASE WHEN
문은 설명된 패턴을 사용합니다.
평가 연산.
예:
=, !=, <, >.
LIKE 작업.
IN 작업.
CAST 를 평가 연산으로
CAST 를 LIKE 연산으로
다음 샘플은 평가 작업이 포함된 패턴을 보여줍니다.
Teradata
SELECT first_name,
last_name,
CASE
WHEN department = 'EngineerinG' THEN 'Information Technology'
WHEN last_name = 'SNOW' THEN 'GLOBAL COOL SALES'
ELSE 'Other'
END AS department_full_name
FROM employees;
| first\_name | last\_name | department\_full\_name |
| ------------ | ---------- | ---------------------- |
| GEORGE | | Other |
| JOHN | SnoW | Other |
| Mary | | Other |
| JOHN | snow | Other |
| WIlle | SNOW | GLOBAL COOL SALES |
| George | Snow | Other |
| GeorgE | | Other |
| GEORGE | | Other |
| Marco | SnoW | Information Technology |
| John | SNOW | Information Technology |
Snowflake
SELECT
first_name,
last_name,
CASE
WHEN UPPER(RTRIM(department)) = UPPER(RTRIM('EngineerinG'))
THEN 'Information Technology'
WHEN RTRIM(last_name) = RTRIM('SNOW')
THEN 'GLOBAL COOL SALES'
ELSE 'Other'
END AS department_full_name
FROM
employees;
| first\_name | last\_name | department\_full\_name |
| ------------ | ---------- | ---------------------- |
| GEORGE | | Other |
| JOHN | SnoW | Other |
| Mary | | Other |
| JOHN | snow | Other |
| WIlle | SNOW | GLOBAL COOL SALES |
| George | Snow | Other |
| GeorgE | | Other |
| GEORGE | | Other |
| Marco | SnoW | Information Technology |
| John | SNOW | Information Technology |
JOIN clause
경고
간단한 시나리오가 지원됩니다.
JOIN
문은 설명된 패턴을 사용합니다.
평가 연산.
예:
=, !=, <, >.
LIKE 작업.
IN 작업.
CAST 를 평가 연산으로
CAST 를 LIKE 연산으로
다음 샘플은 평가 작업이 포함된 패턴을 보여줍니다.
샘플: 열 제약 조건은 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;
employee_id | first_name | last_name | department_name |
---|---|---|---|
1 | George | Snow | Sales |
3 | WIlle | SNOW | Human Resources |
5 | Mary | Sales | |
6 | GEORGE | Sales | |
7 | GEORGE | Sales | |
8 | GeorgE | Sales | |
10 | JOHN | snow | Finance |
Snowflake¶
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name
FROM
employees e
JOIN
departments d
ON UPPER(RTRIM(e.department)) = UPPER(RTRIM(d.department_name));
employee_id | first_name | last_name | department_name |
---|---|---|---|
1 | George | Snow | Sales |
3 | WIlle | SNOW | Human Resources |
5 | Mary | Sales | |
6 | GEORGE | Sales | |
7 | GEORGE | Sales | |
8 | GeorgE | Sales | |
10 | JOHN | snow | Finance |
Known Issues¶
몇 가지 모드별 SQL 문 제한 사항:
BEGIN TRANSACTION
,END TRANSACTION
,COMMIT [WORK]
.대/소문자 사양이 열 선언에 바인딩되지 않기 때문에 데이터 삽입은 Snowflake에서 다를 수 있습니다.
GROUP BY
의 순서는 다를 수 있지만 올바른 값을 그룹화합니다.ORDER BY
는 Snowflake에서 다르게 동작합니다.함수에 소스 코드의 TRIM()가 있는 경우 이 해결 방법은 소스 코드에 필수 함수를 추가합니다. 따라서 TRIM() 소스 함수에 RTRIM 을 적용합니다.
관련 EWIs¶
SSC-EWI-TD0007: GROUP BY IS NOT EQUIVALENT IN TERADATA MODE