Поддержка SQL

Содержание раздела

1. Партиционированные таблицы и партиции
   1. Выборки из партиционированных таблиц
   2. Выборки из партиций
   3. Удаление данных из партиционированных таблиц
2. Числовые последовательности
   1. Создание и удаление последовательностей
   2. Функции для работы с последовательностями
3. Преобразование типов
   1. Неявное преобразование
   2. CAST
4. CASE WHEN … THEN … ELSE …
5. Псевдонимы столбцов и логических сущностей
   1. Псевдонимы столбцов
   2. Псевдонимы сущностей
6. LIMIT, OFFSET, FETCH NEXT ROWS ONLY
7. Составной тип ROW
   1. SELECT ROW
   2. SELECT WHERE ROW
8. Фильтр уникальных значений в столбцах
   1. SELECT DISTINCT
   2. SELECT AGG(DISTINCT)
   3. SELECT DISTINCT ON
9. Группировка по столбцам
   1. GROUP BY
   2. GROUP BY GROUPING SETS
   3. GROUP BY CUBE
   4. GROUP BY ROLLUP
   5. GROUPING
10. Фильтры агрегационных функций
    1. SELECT AGG FILTER
    2. SELECT AGG FROM HAVING FILTER
    3. SELECT CASE WHEN (AGG FILTER) THEN (AGG FILTER) ELSE END FROM
11. Агрегационные функции над упорядоченными наборами
    1. SELECT AGG WITHIN GROUP(GROUP BY)
12. Оконные агрегационные функции
    1. SELECT AGG OVER
13. Функции и операторы соединения
    1. UNION
    2. INTERSECT
    3. EXCEPT
    4. JOIN
14. Функции и операторы сравнения в интервале
    1. ALL
    2. ANY
    3. BETWEEN
    4. IN
15. Булевы операторы
    1. AND
    2. OR
    3. NOT
    4. IS
16. Функции и операторы даты и времени
    1. CAST AS DATE
    2. CURRENT_DATE
    3. CAST AS TIME
    4. CURRENT_TIME
    5. CAST AS TIMESTAMP
    6. CURRENT_TIMESTAMP
    7. EXTRACT
    8. LOCALTIME
    9. LOCALTIMESTAMP
    10. MONTH, QUARTER, WEEK, YEAR
    11. TIMESTAMPADD
    12. TIMESTAMPDIFF
    13. PERIOD
    14. CONTAINS
    15. OVERLAPS
    16. EQUALS
    17. PRECEEDS
    18. SUCCEEDS
17. Системные функции и операторы
18. Строковые функции и операторы
    1. ASCII
    2. LIKE
    3. SIMILAR TO
    4. CHAR_LENGTH
    5. POSITION
    6. UPPER
    7. LOWER
    8. SUBSTRING
    9. IS NULL
    10. IS DISTINCT FROM
    11. NULLIF
    12. COALESCE
    13. TRIM
    14. REPLACE
    15. CONCATENATION
    16. INITCAP
    17. LISTAGG
    18. STRING_AGG
    19. Кавычки формата $$
19. Текстовый поиск
    1. TO_TSVECTOR и PLAINTO_TSQUERY
    2. TO_TSVECTOR и TO_TSQUERY
    3. TO_TSVECTOR и PHRASETO_TSQUERY
    4. TO_TSVECTOR и WEBSEARCH_TO_TSQUERY
    5. Создание индексов для текстового поиска
20. Математические функции и операторы
    1. ABS
    2. ROUND
    3. FLOOR
    4. CEIL
    5. CEILING
    6. BIT_AND
    7. BIT_OR
    8. BIT_XOR
    9. DEGREES
    10. RADIANS
    11. SIGN
    12. RAND
    13. SIN, COS, TAN, COT
    14. ASIN, ACOS, ATAN, ATAN2
    15. POWER, EXP*, LN*
    16. SQRT, CBRT
    17. TRUNCATE
    18. MOD
    19. MAX, MIN
    20. SUM, COUNT
    21. AVG
    22. COVAR
    23. VAR
    24. STDDEV
21. Запросы с подзапросами
    1. EXISTS
    2. TRUNCATE HISTORY
    3. DELETE
    4. INSERT SELECT

В SELECT-запросах можно использовать функции и операторы SQL. Некоторые функции доступны не во всех СУБД из-за особенностей этих СУБД. Наиболее полный синтаксис запросов доступен в СУБД ADB и ADP.

В этом разделе приведены примеры использования распространенных функций и операторов SQL. Для каждого примера указаны СУБД, в которых он доступен; если пример использования недоступен ни в одной из СУБД, для него указана строка «Не поддерживается».

В текущей версии SELECT-запросы и подзапросы к ADG не поддерживают все арифметические или строковые функции и операторы для столбцов таблицы. Кроме того, для ADG не поддерживаются ключевые слова ORDER BY, LIMIT и OFFSET. Для ADB и ADP не поддерживаются явные CAST-преобразования из bool в smallint, numeric, float4 и float8.

Партиционированные таблицы и партиции

Выборки из партиционированных таблиц

Выборка без условия во всех партициях SELECT * FROM partitionedTableById: ADP
SELECT * FROM parTable;

Выборка с условием по столбцу во всех партициях SELECT * FROM partitionedTableById WHERE: ADP
SELECT * FROM parTableById WHERE col1 BETWEEN val1 AND val2;
SELECT * FROM parTableById WHERE col1 >= val1 AND id <= val2;
SELECT * FROM parTableById WHERE col1 IN (val1,val2);
Для столбца col1 значения val1 < val2 могут относиться к разным интервалам партиционированной таблицы

Выборка с условием по столбцам во всех партициях SELECT * FROM partitionedTableById WHERE: ADP
SELECT * FROM parTableById WHERE col1 BETWEEN val1 AND val2 AND col2 = val3;
SELECT * FROM parTableById WHERE col1 >= val1 AND id <= val2 OR col2 = val3;
SELECT * FROM parTableById WHERE col1 IN (val1,val2) OR col2 BETWEEN val3 and val4;
Для столбцов col1 и col2 значения val1 < val2, val3 < val4 могут относиться к разным интервалам партиционированной таблицы

Выборка с подзапросом, содержащим условие SELECT FROM (SELECT * FROM partitionedTableById WHERE): ADP
SELECT * FROM (SELECT * FROM parTableById WHERE col1 = val1);
SELECT * FROM (SELECT * FROM parTableById WHERE col1 BETWEEN val1 AND val2 AND col2 = val3);
SELECT * FROM (SELECT * FROM parTableById WHERE col1 >= val1 AND id <= val2 OR col2 IN (val3, val4));
SELECT * FROM (SELECT * FROM parTableById WHERE col1 IN (val1,val2) OR col2 BETWEEN val3 AND val4);
Для столбцов col1 и col2 значения val1 < val2, val3 < val4 могут относиться к разным интервалам партиционированной таблицы

Выборка с условием и подзапросом SELECT FROM (SELECT * FROM partitionedTableById) WHERE: ADP
SELECT * FROM (SELECT * FROM parTableById) WHERE col1 = val1;
SELECT * FROM (SELECT * FROM parTableById) WHERE col1 BETWEEN val1 AND val2 AND col2 = val3;
SELECT * FROM (SELECT * FROM parTableById) WHERE col1 >= val1 AND id <= val2 OR col2 BETWEEN val3 AND val4;
SELECT * FROM (SELECT * FROM parTableById) WHERE col1 IN (val1,val2) OR col2 IN (val3, val4);
Для столбцов col1 и col2 значения val1 < val2, val3 < val4 могут относиться к разным интервалам партиционированной таблицы

Выборка с подзапросом в условии SELECT * FROM partitionedTableById WHERE IN (SELECT): ADP
SELECT * FROM parTableById WHERE col1 IN (SELECT col1 FROM parTable2);

Выборки из партиций

SELECT * FROM tablePartition1 UNION: ADP
SELECT * FROM tabPart1 WHERE col1 = val1 UNION SELECT * FROM tabPart2 WHERE col1 BETWEEN val2 AND val3;
SELECT * FROM tabPart1 WHERE col1 = val1 UNION SELECT * FROM tabPart2 WHERE col2 BETWEEN val2 AND val3 ORDER BY col2;
SELECT * FROM ( SELECT * FROM tabPart1 WHERE col1 = val1 UNION SELECT * FROM tabPart2 WHERE col2 BETWEEN val2 AND val3) ORDER BY id;
Значения val2 < val3 могут относиться к разным интервалам партиции

Удаление данных из партиционированных таблиц

Удаление всех данных DELETE FROM partitionedTableById: ADP
DELETE FROM parTableById;

Удаление данных по условию равенства DELETE FROM partitionedTableById WHERE: ADP
DELETE FROM parTableById WHERE col1 = val1;

Удаление данных по условию вхождения в интервал DELETE FROM partitionedTableById WHERE: ADP
DELETE FROM parTableById WHERE col1 BETWEEN val1 AND val2 OR col2 IN (val3, val4);
Для столбцов col1 и col2 значения val1 < val2, val3 < val4 могут относиться к разным интервалам партиционированной таблицы

Удаление данных по условию вхождения в подзапрос DELETE FROM partitionedTableById WHERE: ADP не поддерживается
DELETE FROM parTableById WHERE col1 IN (SELECT col1 FROM parTable2);

Числовые последовательности

Создание и удаление последовательностей

CREATE SEQUENCE: ADB, ADP
CREATE SEQUENCE dm1.sequence1;
CREATE SEQUENCE dm1.sequence1 DATASOURCE_TYPE ('ADP');
CREATE SEQUENCE dm1.sequence1 DATASOURCE_TYPE ('ADB');

CREATE SEQUENCE IF NOT EXISTS: ADP
CREATE SEQUENCE IF NOT EXISTS dm1.sequence1;
CREATE SEQUENCE IF NOT EXISTS dm1.sequence1 DATASOURCE_TYPE ('ADP');

CREATE SEQUENCE INCREMENT MINVALUE MAXVALUE START CYCLE: ADB, ADP
CREATE SEQUENCE dm1.sequence1 INCREMENT BY 1;
CREATE SEQUENCE dm1.sequence1 INCREMENT BY 1 DATASOURCE_TYPE ('ADB');
CREATE SEQUENCE dm1.sequence1 INCREMENT BY 1 START WITH 10 DATASOURCE_TYPE ('ADP');
CREATE SEQUENCE dm1.sequence1 INCREMENT BY 1 START 10 DATASOURCE_TYPE ('ADB');
CREATE SEQUENCE dm1.sequence1 INCREMENT BY 1 NO MAXVALUE START 10 DATASOURCE_TYPE ('ADB');
CREATE SEQUENCE dm1.sequence1 INCREMENT BY -1 MAXVALUE 2 START 2 DATASOURCE_TYPE ('ADB');
CREATE SEQUENCE dm1.sequence1 INCREMENT BY -1 NO MINVALUE MAXVALUE 2 START 2 NO CYCLE DATASOURCE_TYPE ('ADB');
CREATE SEQUENCE dm1.sequence1 INCREMENT BY -1 MINVALUE -10 MAXVALUE 2 START 2 CYCLE DATASOURCE_TYPE ('ADB');
CREATE SEQUENCE dm1.sequence1 INCREMENT BY 2 MINVALUE -2 MAXVALUE 10 START 0 CYCLE DATASOURCE_TYPE ('ADB');

DROP SEQUENCE: ADB, ADP
DROP SEQUENCE dm1.sequence1;
DROP SEQUENCE dm1.sequence1 RESTRICT;
DROP SEQUENCE dm1.sequence1 CASCADE;

DROP SEQUENCE IF EXISTS: ADP
DROP SEQUENCE IF EXISTS dm1.sequence1;
DROP SEQUENCE IF EXISTS dm1.sequence1 CASCADE;

Функции для работы с последовательностями

NEXTVAL: ADB, ADP
SELECT NEXTVAL('dm1.sequence1');
SELECT NEXTVAL('dm1.sequence1') DATASOURCE_TYPE = 'ADB';
SELECT *, NEXTVAL('dm1.sequence1') FROM table1 DATASOURCE_TYPE = 'ADB';
SELECT * FROM table1 WHERE bigint_col > NEXTVAL('dm1.sequence1') DATASOURCE_TYPE = 'ADB';
INSERT INTO table1 (bigint_col) FROM NEXTVAL('dm1.sequence1');

SETVAL: ADB, ADP
SELECT SETVAL('dm1.sequence1',0);
SELECT SETVAL('dm1.sequence1',1, TRUE);
SELECT SETVAL('dm1.sequence1',1, FALSE) DATASOURCE_TYPE = 'ADB';

CURRVAL: ADB, ADP
SELECT CURRVAL('dm1.sequence1') FROM table1;
SELECT *, CURRVAL('dm1.sequence1') FROM table1 DATASOURCE_TYPE = 'ADB';
SELECT * FROM table1 WHERE bigint_col = CURRVAL('dm1.sequence1') DATASOURCE_TYPE = 'ADB';
INSERT INTO table1 (bigint_col) FROM CURRVAL('dm1.sequence1');

Преобразование типов

Неявное преобразование

Из bigint в boolean: не поддерживается
SELECT bigint_col = true FROM table1;

Из int в boolean: не поддерживается
SELECT int_col = true FROM table1;

Из int в boolean в материализованном представлении: не поддерживается
CREATE MATERIALIZED VIEW matview1 ( id int not null, int_col int, primary key (id) ) DISTRIBUTED BY (id) DATASOURCE_TYPE ('ADG', 'ADQM') AS SELECT * FROM table1 a INNER JOIN table2 c ON a.int_col = true DATASOURCE_TYPE = 'ADB';

CAST

CAST (boolean as …): не поддерживается в ADQM
SELECT CAST(boolean_col as INT32) FROM table1 DATASOURCE_TYPE = 'ADQM';

CAST (boolean as {int64, float, double}): не поддерживается в ADB и ADP
SELECT CAST(boolean_col as INT64) FROM table1 DATASOURCE_TYPE = 'ADB';

CASE WHEN … THEN … ELSE …

CASE WHEN THEN ELSE END: ADB, ADQM, ADG, ADP
SELECT CASE WHEN col_bigint > 0 THEN 10 ELSE 5 END FROM table1;

CAST(CASE WHEN THEN ELSE END AS type): ADB, ADQM, ADG, ADP
SELECT CAST(CASE WHEN col_bigint > 0 THEN 10 ELSE 5 END AS VARCHAR) FROM table1;

Псевдонимы столбцов и логических сущностей

Псевдонимы столбцов

Слитные или с подчеркиваниями (_): ADB, ADQM, ADG, ADP
SELECT col1 [AS] columnone FROM table1;
SELECT col1 [AS] "columnone" FROM table1;
SELECT col1 [AS] column_one FROM table1;
SELECT col1 [AS] "column_one" FROM table1;

С дефисами (-): не поддерживаются
SELECT col1 [AS] col-one FROM table1;
SELECT col1 [AS] "col-one" FROM table1;
SELECT col1 [AS] "end-exec" FROM table1;

Псевдонимы сущностей

Слитные или с подчеркиваниями (_): ADB, ADQM, ADG, ADP
SELECT t1.col1 FROM table1 [AS] t1;
SELECT "t1".col1 FROM table1 [AS] "t1";
SELECT t_1.col1 FROM table1 [AS] t_1;
SELECT "t_1".col1 FROM table1 [AS] "t_1";
SELECT "t-1".col1 FROM table1 [AS] "t-1";

С дефисами (-): не поддерживаются
SELECT col1 FROM table1 [AS] t-1;

Зарезервированные слова без кавычек: не поддерживаются
SELECT col1 AS value FROM table1;

Зарезервированные слова в кавычках (без дефисов): ADB, ADQM, ADG, ADP
SELECT col1 AS "value" FROM table1;

LIMIT, OFFSET, FETCH NEXT ROWS ONLY

LIMIT: ADB, ADQM, ADP
SELECT * FROM table1 ORDER BY id_col LIMIT 1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};

FETCH { FIRST | NEXT } ROWS ONLY: ADB, ADQM, ADP
SELECT * FROM table1 ORDER BY id_col FETCH NEXT 1 ROWS ONLY DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 ORDER BY id_col FETCH FIRST 10 ROWS ONLY DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};

LIMIT OFFSET: ADB, ADQM, ADP
SELECT * FROM table1 ORDER BY id_col LIMIT 1 OFFSET 1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 ORDER BY id_col LIMIT 1 OFFSET 2 ROW DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 ORDER BY id_col LIMIT 1 OFFSET 3 ROWS DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};

OFFSET FETCH { FIRST | NEXT } ROWS ONLY: ADB, ADQM, ADP
SELECT * FROM table1 ORDER BY id_col OFFSET 1 FETCH NEXT 1 ROWS ONLY DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 ORDER BY id_col OFFSET 2 ROW FETCH NEXT 1 ROWS ONLY DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 ORDER BY id_col OFFSET 3 ROWS FETCH NEXT 1 ROWS ONLY DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 ORDER BY id_col OFFSET 10 ROWS FETCH FIRST 5 ROWS ONLY DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};

Составной тип ROW

SELECT ROW

SELECT ROW: ADB, ADP
SELECT ROW (col1, ..., colN) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT (col1, ..., colN) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT ROW (col1, ..., colN) = (val1, ..., valN) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT ROW (col1, ..., colN) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT ROW (val1, ..., valN) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT ROW (val1, ..., valN) > ROW (val_1, ..., val_N) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT ROW (val1, ..., valN) < ROW (val_1, ..., val_N) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT c1 FROM (SELECT ROW (val11, ..., val1N) as c1 UNION ALL SELECT ROW (val21, ..., val2N) as c1) ORDER BY c1 DESC DATASOURCE_TYPE = {'ADB' | 'ADP'}

SELECT WHERE ROW

SELECT WHERE ROW: ADB, ADP
SELECT * FROM table1 WHERE ROW (col1, ..., colN) = (val1, ..., valN) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT * FROM table1 WHERE (col1, ..., colN) >= (val1, ..., valN) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT * FROM table1 WHERE (col1, ..., colN) <= (val1, ..., valN) DATASOURCE_TYPE = {'ADB' | 'ADP'}

Фильтр уникальных значений в столбцах

SELECT DISTINCT

DISTINCT: ADB, ADQM, ADG, ADP
SELECT DISTINCT * FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADG' | 'ADP'}
SELECT DISTINCT col1, ..., colN FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADG' | 'ADP'}

SELECT AGG(DISTINCT)

AGG(DISTINCT): ADB, ADQM, ADP
SELECT COUNT(DISTINCT col1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT SUM(DISTINCT col1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT MAX(DISTINCT col1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT MIN(DISTINCT col1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT AVG(DISTINCT col1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT CAST(AVG(DISTINCT col1) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT STDDEV_POP(DISTINCT col1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT CAST(STDDEV_POP(DISTINCT col1) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT STDDEV_SAMP(DISTINCT col1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT CAST(STDDEV_SAMP(DISTINCT col1) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT VAR_POP(DISTINCT col1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT CAST(VAR_POP(DISTINCT col1) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT VAR_SAMP(DISTINCT col1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT CAST(VAR_SAMP(DISTINCT col1) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT COVAR_POP(DISTINCT col1, col2) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT CAST(COVAR_POP(DISTINCT col1, col2) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT COVAR_SAMP(DISTINCT col1, col2) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT CAST(COVAR_SAMP(DISTINCT col1, col2) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}

SELECT DISTINCT ON

DISTINCT ON: не поддерживается
SELECT DISTINCT ON (col1, ..., colN) col1, ..., colN FROM table1

Группировка по столбцам

GROUP BY

SELECT AGG GROUP BY: ADB, ADQM, ADP
SELECT col2, COUNT(col1) FROM table1 GROUP BY (col2) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT col2, SUM(col1) FROM table1 GROUP BY (col2) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT col2, MAX(col1) FROM table1 GROUP BY (col2) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT col2, MIN(col1) FROM table1 GROUP BY (col2) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT col2, AVG(col1) FROM table1 GROUP BY (col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, CAST(AVG(col1) AS DOUBLE) FROM table1 GROUP BY (col2) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT col2, STDDEV_POP(col1) FROM table1 GROUP BY (col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, CAST(STDDEV_POP(col1) AS DOUBLE) FROM table1 GROUP BY (col2) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT col2, STDDEV_SAMP(col1) FROM table1 GROUP BY (col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, CAST(STDDEV_SAMP(col1) AS DOUBLE) FROM table1 GROUP BY (col2) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT col2, VAR_POP(col1) FROM table1 GROUP BY (col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, CAST(VAR_POP(col1) AS DOUBLE) FROM table1 GROUP BY (col2) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT col2, VAR_SAMP(col1) FROM table1 GROUP BY (col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, CAST(VAR_SAMP(col1) AS DOUBLE) FROM table1 GROUP BY (col2) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT col3, COVAR_POP(col1, col2) FROM table1 GROUP BY (col3) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col3, CAST(COVAR_POP(col1, col2) AS DOUBLE) FROM table1 GROUP BY (col3) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}
SELECT col3, COVAR_SAMP(col1, col2) FROM table1 GROUP BY (col3) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col3, CAST(COVAR_SAMP(col1, col2) AS DOUBLE) FROM table1 GROUP BY (col3) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'}

GROUP BY GROUPING SETS

SELECT AGG GROUP BY GROUPING SETS: ADB, ADP
SELECT col2, COUNT(col1) FROM table1 GROUP BY GROUPING SETS(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, SUM(col1) FROM table1 GROUP BY GROUPING SETS(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, MAX(col1) FROM table1 GROUP BY GROUPING SETS(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, MIN(col1) FROM table1 GROUP BY GROUPING SETS(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, AVG(col1) FROM table1 GROUP BY GROUPING SETS(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}

GROUP BY CUBE

SELECT AGG GROUP BY CUBE: ADB, ADP
SELECT col2, COUNT(col1) FROM table1 GROUP BY CUBE(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, SUM(col1) FROM table1 GROUP BY CUBE(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, MAX(col1) FROM table1 GROUP BY CUBE(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, MIN(col1) FROM table1 GROUP BY CUBE(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, AVG(col1) FROM table1 GROUP BY CUBE(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}

GROUP BY ROLLUP

SELECT AGG GROUP BY ROLLUP: ADB, ADP
SELECT col2, COUNT(col1) FROM table1 GROUP BY ROLLUP(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, SUM(col1) FROM table1 GROUP BY ROLLUP(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, MAX(col1) FROM table1 GROUP BY ROLLUP(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, MIN(col1) FROM table1 GROUP BY ROLLUP(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT col2, AVG(col1) FROM table1 GROUP BY ROLLUP(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}

GROUPING

SELECT GROUPING(), AGG GROUP BY ROLLUP: ADB, ADP
SELECT GROUPING(col2), COUNT(col1) FROM table1 GROUP BY ROLLUP(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT GROUPING(col2), SUM(col1) FROM table1 GROUP BY CUBE(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}
SELECT GROUPING(col2), MAX(col1) FROM table1 GROUP BY GROUPING SETS(col2) DATASOURCE_TYPE = {'ADB' | 'ADP'}

Фильтры агрегационных функций

SELECT AGG FILTER

SELECT AGG FILTER FROM: ADB, ADP
SELECT COUNT(*) FILTER (WHERE varchar_col = 'TEST 1') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COUNT(*) FILTER (WHERE varchar_col = cast('TEST 1' as varchar)) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COUNT(*) FILTER (WHERE varchar_col = 'TEST 1' and id_cat > 1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COUNT(*) FILTER (WHERE varchar_col = cast('TEST 1' as varchar) and id_cat > cast(1 as int)) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COUNT(*) FILTER (WHERE varchar_col like 'T%1') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COUNT(*) FILTER (WHERE varchar_col like 'T%1' is false) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COUNT(*) FILTER (WHERE varchar_col like cast('T%1' as varchar)) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COUNT(*) FILTER (WHERE varchar_col like cast('T%1' as varchar) is false) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

SELECT AGG FILTER FROM GROUP BY: ADB, ADP
SELECT COUNT(*) FILTER (WHERE varchar_col = 'TEST 1') AS "COUNT_TEST" FROM table1 group by varchar_col ORDER BY "COUNT_TEST" DESC DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COUNT(*) FILTER (WHERE varchar_col = 'TEST 1') AS "COUNT_TEST", varchar_col FROM table1 group by varchar_col ORDER BY "COUNT_TEST" DESC DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COUNT(*) FILTER (WHERE varchar_col = 'TEST 1' IS FALSE) AS "COUNT_TEST", varchar_col FROM table1 group by varchar_col ORDER BY "COUNT_TEST" DESC DATASOURCE_TYPE = {'ADB' | 'ADP'};

SELECT AGG FILTER FROM HAVING AGG FILTER: ADB, ADP
SELECT COUNT(*) FILTER (WHERE varchar_col LIKE '%notfound' IS FALSE) FROM table1 HAVING COUNT(*) FILTER (WHERE varchar_col LIKE CAST('%notfound' AS VARCHAR) IS FALSE) > 0 datasource_type = {'ADB' | 'ADP'};
SELECT SUM(id) FILTER (WHERE varchar_col LIKE '%val0%' IS TRUE) FROM table1 HAVING COUNT(*) FILTER (WHERE varchar_col LIKE CAST('%val0%' AS VARCHAR) IS TRUE) > 0 datasource_type = {'ADB' | 'ADP'};
SELECT AVG(CAST(id as DOUBLE)) FILTER (WHERE varchar_col LIKE '%val0%' IS TRUE) FROM table1 HAVING COUNT(*) FILTER (WHERE varchar_col LIKE CAST('%val0%' AS VARCHAR) IS TRUE) > 0 datasource_type = {'ADB' | 'ADP'};
SELECT LISTAGG(REPLACE(varchar_col,'varchar_',''),';') FILTER (WHERE varchar_col LIKE '%val0%' IS TRUE) FROM table1 HAVING COUNT(*) FILTER (WHERE varchar_col LIKE CAST('%val0%' AS VARCHAR) IS TRUE) > 0 datasource_type = {'ADB' | 'ADP'};
SELECT LISTAGG(REPLACE(varchar_col,'varchar_',''),';') FILTER (WHERE id IN (2,3,4)) FROM table1 HAVING SUM(id) FILTER (WHERE id IN (CAST(2 AS BIGINT),CAST(3 AS BIGINT),CAST(4 AS BIGINT))) > 0 datasource_type = {'ADB' | 'ADP'};

SELECT AGG FILTER FROM GROUP BY HAVING AGG FILTER: ADB, ADP
SELECT LISTAGG(REPLACE(varchar_col,'varchar_',''),';') FILTER (WHERE varchar_col LIKE '%val0%' IS TRUE) FROM table1 GROUP BY varchar_col HAVING COUNT(*) FILTER (WHERE varchar_col LIKE CAST('%val0%' AS VARCHAR) IS TRUE) > 0 ORDER BY varchar_col datasource_type = {'ADB' | 'ADP'};
SELECT COUNT(*) filter (WHERE varchar_col = cast('TEST 1' as varchar)), id_cat FROM table1 GROUP BY id_cat HAVING COUNT(*) FILTER (WHERE id_cat = cast(1 as int)) > 0 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COUNT(*) filter (WHERE varchar_col = cast('TEST 1' as varchar)), id_cat FROM table1 GROUP BY id_cat HAVING COUNT(*) FILTER (WHERE id_cat = cast(1 as int) is false) > 0 DATASOURCE_TYPE = {'ADB' | 'ADP'};

SELECT AGG FROM HAVING FILTER

SELECT AGG FROM HAVING FILTER: ADB, ADP
SELECT COUNT(*), id_cat FROM table1 GROUP BY id_cat HAVING COUNT(*) FILTER (WHERE id_cat = cast(1 as int)) > 0 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COUNT(*), id_cat FROM table1 GROUP BY id_cat HAVING COUNT(*) FILTER (WHERE id_cat = cast(1 as int) is false) > 0 DATASOURCE_TYPE = {'ADB' | 'ADP'};

SELECT CASE WHEN (AGG FILTER) THEN (AGG FILTER) ELSE END FROM

SELECT CASE WHEN (AGG FILTER) THEN (AGG FILTER) ELSE END FROM: ADB, ADP
SELECT CASE WHEN (SUM(id) FILTER (WHERE varchar_col LIKE '%val0%' IS TRUE) < 10) THEN (SUM(id) FILTER (WHERE varchar_col LIKE '%val0%' IS TRUE)) ELSE 10 END FROM table1 HAVING COUNT(*) FILTER (WHERE varchar_col LIKE CAST('%val0%' AS VARCHAR) IS TRUE) > 0 datasource_type ={'ADB' | 'ADP'};

Агрегационные функции над упорядоченными наборами

SELECT AGG WITHIN GROUP(GROUP BY)

SELECT AGG WITHIN GROP: ADB, ADP
SELECT LISTAGG(col2) WITHIN GROUP (ORDER BY col2) FROM (SELECT 1 AS col1, '2' AS col2 UNION SELECT 3 AS col1, '4' AS col2 UNION SELECT 5 as col1, '6' as col2) DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT LISTAGG(col2) WITHIN GROUP (ORDER BY col2) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

Оконные агрегационные функции

SELECT AGG OVER

SELECT AGG OVER: ADB, ADP
SELECT col1, col2, SUM(col1) OVER () FROM (SELECT 1 AS col1, 2 AS col2 UNION SELECT 3 AS col1, 4 AS col2 UNION SELECT 5 as col1, 4 as col2) DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT col1, col2, SUM(col1) OVER () FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

SELECT AGG OVER PARTITION BY: ADB, ADP
SELECT col1, col2, SUM(col1) OVER (PARTITION BY col2) FROM (SELECT 1 AS col1, 2 AS col2 UNION SELECT 3 AS col1, 4 AS col2 UNION SELECT 5 as col1, 4 as col2) DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT col1, col2, SUM(col1) OVER (PARTITION BY col2) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

Функции и операторы соединения

UNION

UNION: ADB, ADP
SELECT * FROM ( SELECT 1 as a, '2' as b UNION SELECT 3 as a, '4' as b UNION SELECT 5 as a, '6' as b) DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT a.* FROM ( SELECT b.id FROM (SELECT id from table2) b UNION ALL SELECT id FROM table1 ) as a DATASOURCE_TYPE = {'ADB' | 'ADP'};

UNION ALL: ADB, ADQM, ADG, ADP
SELECT * FROM ( SELECT 1 as a, '2' as b UNION ALL SELECT 3 as a, '4' as b UNION ALL SELECT 5 as a, '6' as b) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT a.* FROM ( SELECT b.id FROM (SELECT id from table2) b UNION ALL SELECT id FROM table1 ) as a DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM' | 'ADG'};

UNION ALL: не поддерживается
SELECT id FROM table1 ORDER BY id LIMIT 2 UNION ALL SELECT id from table3;

INTERSECT

INTERSECT: ADB, ADP
SELECT a.* FROM ( SELECT b.id FROM (SELECT id from table2) b INTERSECT SELECT id FROM table1 ) as a DATASOURCE_TYPE = {'ADB' | 'ADP'};

EXCEPT

EXCEPT: ADB, ADG, ADP
SELECT a.* FROM ( SELECT b.id FROM (SELECT id from table2) b EXCEPT SELECT id FROM table1 ) as a DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADG'};

EXCEPT: не поддерживается
SELECT id FROM table1 ORDER BY id LIMIT 2 EXCEPT SELECT id from table3;

JOIN

SELECT-запросы с INNER JOIN поддерживаются в СУБД ADQM только в случае соединения таблиц по ключу шардирования в каждой из них.

INNER JOIN: ADB, ADP, ADQM*
SELECT * FROM table1 INNER JOIN table2 on table1.id = table2.id DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT * FROM table1 t1 INNER JOIN table2 t2 on t1.id = t2.id ORDER BY t1.id DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
* Столбец id — ключ шардирования, заданный при создании таблицы в DISTRIBUTED BY

INNER JOIN: ADB, ADP
SELECT * FROM table1 t1 INNER JOIN table2 t2 on t1.id = t2.id AND t1.num_col BETWEEN 0 AND 10 DATASOURCE_TYPE = {'ADB' | 'ADP'};

INNER JOIN: ADB, ADP, ADQM
SELECT * FROM table1 t1 INNER JOIN table2 t2 on t1.id = t2.id WHERE t1.num_col BETWEEN 0 AND 10 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

INNER JOIN: ADB, ADP
SELECT * FROM table1 t1 INNER JOIN table2 t2 on t1.id = t2.id WHERE t1.id IN (SELECT t3.id FROM table3 t3) DATASOURCE_TYPE = {'ADB' | 'ADP'};

Вышеуказанный SELECT-запрос с INNER JOIN по ключу шардирования id и подзапросом в условии WHERE для СУБД ADQM считается неподдерживаемым, так как может возвращать некорректный результат, несмотря на формальное отсутствие ошибки исполнения.

INNER JOIN: ADG*
SELECT * FROM table1 INNER JOIN table2 on table1.col1 = table2.col2 DATASOURCE_TYPE = {'ADG'};
SELECT * FROM table1 AS t1 INNER JOIN table2 AS t2 on t1.col1 = t2.col2 DATASOURCE_TYPE = {'ADG'};
* В таблицах table1 и table2 все столбцы должны быть с различающимися названиями

LEFT JOIN: ADB, ADQM, ADP
SELECT * FROM table1 LEFT JOIN (SELECT * FROM table3 t3) t2 ON table1.id = t2.id WHERE table1.id > 10 AND t2.id > 5 ORDER BY table1.id DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

LEFT JOIN: не поддерживается
SELECT * FROM table1 LEFT JOIN (SELECT * FROM table3 t3 WHERE t3.id > 5) t2 ON table1.id = t2.id WHERE table3.id > 10 ORDER BY table3.id;

RIGHT JOIN: ADB, ADP
SELECT * FROM table1 t1 RIGHT JOIN table3 t3 on t1.id = t3.id ORDER BY t1.id LIMIT 5 DATASOURCE_TYPE = {'ADB' | 'ADP'};

Вышеуказанный SELECT-запрос с RIGHT JOIN для СУБД ADQM считается неподдерживаемым, так как может возвращать некорректный результат, несмотря на формальное отсутствие ошибки исполнения.

RIGHT JOIN: не поддерживается
SELECT * FROM table1 RIGHT JOIN (SELECT * FROM table3 t3 WHERE t3.id > 5) t2 ON table1.id = t2.id WHERE table3.id > 10 ORDER BY table3.id;

FULL JOIN: ADB, ADP
SELECT * FROM table1 t1 FULL JOIN table2 t2 on t1.category_id = t2.id ORDER BY t1.id LIMIT 6 DATASOURCE_TYPE = {'ADB' | 'ADP'};

CROSS JOIN: ADB, ADP
SELECT * FROM table1 t1 CROSS JOIN table2 t2 ORDER BY t1.id, t2.category_name LIMIT 5 DATASOURCE_TYPE = {'ADB' | 'ADP'};

LEFT JOIN для нескольких таблиц: ADB, ADP
SELECT * FROM table1 LEFT JOIN table2 ON table1.territory_id = table2.territory_id LEFT JOIN table3 ON table1.territory_id = table3.territory_id WHERE table3.last_name is NOT NULL ORDER BY table1.territory_id DATASOURCE_TYPE = {'ADB' | 'ADP'};

RIGHT JOIN для нескольких таблиц: ADB, ADP
SELECT * FROM table1 RIGHT JOIN table2 ON table1.territory_id = table2.territory_id RIGHT JOIN table3 ON table1.territory_id = table3.territory_id WHERE table3.last_name is NOT NULL ORDER BY table1.territory_id DATASOURCE_TYPE = {'ADB' | 'ADP'};

Функции и операторы сравнения в интервале

ALL

ALL: ADB, ADQM, ADP
SELECT 1 >= ALL(1,2,3,4,5) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT 1 >= ALL(SELECT 1 UNION ALL SELECT 2) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM (SELECT 'a' AS varchar_col UNION ALL SELECT 'b' as varchar_col) WHERE varchar_col > ALL('1','2','3') DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 WHERE varchar_col > ALL('1','2','3') DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
WITH cte1 AS (SELECT * FROM table1 WHERE varchar_col > ALL('1','2','3') SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};

ANY

ANY: ADB, ADQM, ADP
SELECT 1 >= {ANY | SOME}(1,2,3,4,5) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT 1 >= {ANY | SOME}(SELECT 1 UNION ALL SELECT 2) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM (SELECT 'a' AS varchar_col UNION ALL SELECT 'b' as varchar_col) WHERE varchar_col > {ANY | SOME}('1','2','3') DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 WHERE varchar_col > {ANY | SOME}('1','2','3') DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
WITH cte1 AS (SELECT * FROM table1 WHERE varchar_col > {ANY | SOME}('1','2','3') SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};

BETWEEN

BETWEEN: ADB, ADQM, ADP
SELECT 2 BETWEEN 1 AND 5 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM (SELECT 'm' AS varchar_col UNION ALL SELECT 'n' as varchar_col) WHERE varchar_col > BETWEEN 'a' AND 'z' DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 WHERE varchar_col BETWEEN 'a' AND 'z' DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
WITH cte1 AS (SELECT * FROM table1 WHERE varchar_col BETWEEN 'a' AND 'z' SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};

IN

IN: ADB, ADQM, ADP
SELECT 5 IN (1,2,3,4,5,7,8,9) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT 5 IN (SELECT 1 UNION ALL SELECT 2) DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM (SELECT 'a' AS varchar_col UNION ALL SELECT 'b' as varchar_col) WHERE varchar_col IN ('1','2','3') DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 WHERE varchar_col IN ('c','b','a') DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
WITH cte1 AS (SELECT * FROM table1 WHERE varchar_col IN ('c','b','a') SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};

Булевы операторы

AND

AND: ADB, ADQM, ADP
SELECT TRUE AND FALSE DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT 'TRUE' AND 'FALSE' DATASOURCE_TYPE = 'ADQM';
SELECT boolean_col1 AND boolean_col2 FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 WHERE boolean_col1 AND boolean_col2 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
WITH cte1 AS (SELECT * FROM table1 WHERE boolean_col1 AND boolean_col2) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};

OR

OR: ADB, ADQM, ADP
SELECT TRUE OR FALSE DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT 'TRUE' OR 'FALSE' DATASOURCE_TYPE = 'ADQM';
SELECT boolean_col1 OR boolean_col2 FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 WHERE boolean_col1 OR boolean_col2 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
WITH cte1 AS (SELECT * FROM table1 WHERE boolean_col1 OR boolean_col2) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};

NOT

NOT: ADB, ADQM, ADP
SELECT NOT TRUE DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT NOT 'TRUE' DATASOURCE_TYPE = 'ADQM';
SELECT NOT boolean_col1 FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 WHERE NOT boolean_col1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
WITH cte1 AS (SELECT * FROM table1 WHERE NOT boolean_col1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};

IS

IS [NOT] {TRUE | FALSE | UNKNOWN}: ADB, ADP
SELECT FALSE IS [NOT] {TRUE | FALSE | UNKNOWN} DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT boolean_col1 IS [NOT] {TRUE | FALSE | UNKNOWN} FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT * FROM table1 WHERE boolean_col1 IS [NOT] {TRUE | FALSE | UNKNOWN} DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT * FROM table1 WHERE boolean_col1 IS [NOT] {TRUE | FALSE | UNKNOWN}) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

Функции и операторы даты и времени

CAST AS DATE

CAST AS DATE: ADB, ADQM, ADP
SELECT * FROM table1 WHERE date_col = '2021-01-02' DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
WITH cte1 AS (SELECT * FROM table1 WHERE date_col = '2021-01-02') SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT DATE '2021-01-02' AS date_col DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};

CAST AS DATE: ADG
SELECT * FROM table1 WHERE date_col = '2021-01-02' DATASOURCE_TYPE = 'ADG';

CAST AS DATE: ADB, ADP
SELECT * FROM table1 WHERE date_col = CAST('2021-01-02' AS DATE) DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT * FROM table1 WHERE date_col = CAST('2021-01-02' AS DATE)) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

CURRENT_DATE

CURRENT_DATE: ADB, ADP
SELECT CURRENT_DATE [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CURRENT_DATE [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

CAST AS TIME

CAST AS TIME: ADB, ADQM, ADP
SELECT * FROM table1 WHERE time_col = '12:12:12' DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
WITH cte1 AS (SELECT * FROM table1 WHERE time_col = '12:12:12') SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT TIME '12:12:12.121212' AS time_col DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};

CAST AS TIME: ADG
SELECT * FROM table1 WHERE time_col = '12:12:12' DATASOURCE_TYPE = 'ADG';

CAST AS TIME: ADB, ADP
SELECT * FROM table1 WHERE time_col = CAST('12:12:12' AS TIME) DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT * FROM table1 WHERE time_col = CAST('12:12:12' AS TIME)) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

CURRENT_TIME

CURRENT_TIME
SELECT CURRENT_TIME [FROM table1];
WITH cte1 AS (SELECT CURRENT_TIME [FROM table1]) SELECT * FROM cte1;
При значении DATASOURCE_TYPE = {'ADB' | 'ADP'} по умолчанию

CURRENT_TIME: ADB, ADP
SELECT CURRENT_TIME [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CURRENT_TIME [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

CAST AS TIMESTAMP

CAST AS TIMESTAMP: ADB, ADQM, ADP
SELECT * FROM table1 WHERE timestamp_col = '2021-01-02 12:12:12' DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
WITH cte1 AS (SELECT * FROM table1 WHERE timestamp_col = '2021-01-02 12:12:12') SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT TIMESTAMP '2021-01-02 12:12:12.121212' AS timestamp_col DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};

CAST AS TIMESTAMP: ADG
SELECT * FROM table1 WHERE timestamp_col = '2021-01-02 12:12:12' DATASOURCE_TYPE = 'ADG';

CAST AS TIMESTAMP: ADB, ADP
SELECT * FROM table1 WHERE time_col = CAST('2021-01-02 12:12:12' AS TIMESTAMP) DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT * FROM table1 WHERE time_col = CAST('2021-01-02 12:12:12' AS TIMESTAMP)) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP [FROM table1];
WITH cte1 AS (SELECT CURRENT_TIMESTAMP [FROM table1]) SELECT * FROM cte1;
При значении DATASOURCE_TYPE = {'ADB' | 'ADP'} по умолчанию

CURRENT_TIMESTAMP: ADB, ADP
SELECT CURRENT_TIMESTAMP [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CURRENT_TIMESTAMP [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

EXTRACT

EXTRACT(FROM DATE): ADB, ADP
SELECT EXTRACT(EPOCH FROM DATE '2001-02-16') [FROM table1] DATASOURCE_TYPE = 'ADP';
SELECT CAST(EXTRACT(EPOCH FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT EXTRACT(DOY FROM DATE '2001-02-16') [FROM table1] DATASOURCE_TYPE = 'ADP';
SELECT CAST(EXTRACT(DOY FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(DOW FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(WEEK FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(CENTURY FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(QUARTER FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(YEAR FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(MONTH FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(DAY FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(EXTRACT(...) AS INT) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

EXTRACT(FROM DATE): ADB, ADP
SELECT CAST(EXTRACT(DECADE FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(ISOYEAR FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(ISODOW FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(EXTRACT(...) AS INT) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

EXTRACT(FROM TIME): ADB, ADP
SELECT CAST(EXTRACT(HOUR FROM TIME '20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(MINUTE FROM TIME '20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(SECOND FROM TIME '20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(MILLISECOND FROM TIME '20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(MICROSECOND FROM TIME '20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(EXTRACT(...) AS INT) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

EXTRACT(FROM TIMESTAMP): ADB, ADP
SELECT CAST(EXTRACT(DOW FROM TIMESTAMP '2001-02-16 00:00:00') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 00:00:00') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 00:00:00') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 00:00:00') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(MILLISECOND FROM TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(EXTRACT(MICROSECOND FROM TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(EXTRACT(...) AS INT) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

LOCALTIME

LOCALTIME, LOCALTIME(precision): ADB, ADP
SELECT LOCALTIME [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(LOCALTIME AS TIME) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT LOCALTIME(3) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(LOCALTIME(3) AS TIME) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT LOCALTIME [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(LOCALTIME AS TIME) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT LOCALTIME(3) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(LOCALTIME(3) AS TIME) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

LOCALTIMESTAMP

LOCALTIMESTAMP, LOCALTIMESTAMP(precision): ADB, ADP
SELECT LOCALTIMESTAMP [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(LOCALTIMESTAMP AS TIMESTAMP) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT LOCALTIMESTAMP(3) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(LOCALTIMESTAMP(3) AS TIMESTAMP) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT LOCALTIMESTAMP [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(LOCALTIMESTAMP AS TIMESTAMP) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT LOCALTIMESTAMP(3) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(LOCALTIMESTAMP(3) AS TIMESTAMP) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

MONTH, QUARTER, WEEK, YEAR

MONTH, QUARTER, WEEK, YEAR: ADB, ADP
SELECT CAST(MONTH(DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(MONTH(TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(QUARTER(DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(QUARTER(DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = 'ADP';
SELECT CAST(QUARTER(TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(QUARTER(TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = 'ADP';
SELECT CAST(WEEK(DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(WEEK(TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(YEAR(DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(YEAR(TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(... AS INT) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

TIMESTAMPADD

TIMESTAMPADD(YEAR | MONTH |DAY | HOUR | MINUTE | SECOND, <integer>, <datetime>): ADB, ADP, ADQM
SELECT TIMESTAMPADD(MINUTE, 30, TIMESTAMP '2009-05-18 10:00:00') FROM table1 LIMIT 1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT TIMESTAMPADD(DAY, -10, DATE '2009-05-18') [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT *, TIMESTAMPADD(MONTH, 6, timestamp_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT *, TIMESTAMPADD(DAY, -1, TIMESTAMPADD(MONTH, -1, TIMESTAMPADD(YEAR, -1, timestamp_col))) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT TIMESTAMPADD(...) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

TIMESTAMPADD(YEAR | MONTH |DAY | HOUR | MINUTE | SECOND, <integer>, <datetime>): ADB, ADP
SELECT TIMESTAMPADD(YEAR, -1, CURRENT_TIMESTAMP) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT TIMESTAMPADD(YEAR, -1, CURRENT_TIMESTAMP) [FROM table1]) SELECT * FROM cte1b DATASOURCE_TYPE = {'ADB' | 'ADP'};

TIMESTAMPDIFF

TIMESTAMPDIFF(DAY | HOUR| MINUTE | SECOND, <datetime1>, <datetime2>): ADB, ADP, ADQM
SELECT *, TIMESTAMPDIFF(MINUTE, timestamp_col1, timestamp_col2) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT *, TIMESTAMPDIFF(DAY, TIMESTAMP '2020-01-30', timestamp_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT * FROM table1 WHERE TIMESTAMPDIFF(DAY, TIMESTAMP '2020-01-01', timestamp_col) < 180 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT TIMESTAMPDIFF(...) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

TIMESTAMPDIFF(YEAR | MONTH, <datetime1>, <datetime2>): ADB, ADP
SELECT *, TIMESTAMPDIFF(YEAR, timestamp_col1, timestamp_col2) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT *, TIMESTAMPDIFF(YEAR, timestamp_col, CURRENT_TIMESTAMP) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT * FROM table1 WHERE TIMESTAMPDIFF(YEAR, date_col, CURRENT_TIMESTAMP) >= 18 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT *, TIMESTAMPDIFF(MONTH, TIMESTAMP '2020-01-30', timestamp_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT TIMESTAMPDIFF(...) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

PERIOD

PERIOD (DATE, DATE): ADB, ADQM, ADP
SELECT [PERIOD] (DATE '2000-11-11', DATE '2000-12-12') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT [PERIOD] (date1_col, date2_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT date1_col, date2_col FROM table1 WHERE [PERIOD] (DATE '2000-11-11', DATE '2000-12-12') > [PERIOD] (DATE '2000-10-30', DATE '2000-12-31') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};

PERIOD (TIMESTAMP, TIMESTAMP): ADB, ADQM, ADP
SELECT [PERIOD] (TIMESTAMP '2000-11-11 11:11:11', TIMESTAMP '2000-12-12 12:12:12') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT [PERIOD] (timestamp1_col, timestamp2_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT timestamp1_col, timestamp2_col FROM table1 WHERE [PERIOD] (TIMESTAMP '2000-11-11 11:11:11', TIMESTAMP '2000-12-12 12:12:12') > [PERIOD] (TIMESTAMP '2000-10-30 10:10:10', TIMESTAMP '2000-12-31 12:12:12') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};

CONTAINS

PERIOD CONTAINS DATE: ADB, ADQM, ADP
SELECT [PERIOD] (DATE '2000-11-11', DATE '2000-12-12') CONTAINS DATE '2000-11-12' DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT [PERIOD] (date1_col, date2_col) CONTAINS date3_col FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT date1_col, date2_col FROM table1 WHERE [PERIOD] (DATE '2000-11-11', DATE '2000-12-12') CONTAINS DATE '2000-11-12' DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};

PERIOD CONTAINS TIMESTAMP: ADB, ADQM, ADP
SELECT [PERIOD] (TIMESTAMP '2000-11-11 11:11:11', TIMESTAMP '2000-12-12 12:12:12') CONTAINS TIMESTAMP '2000-11-12 11:11:11' DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT [PERIOD] (timestamp1_col, timestamp2_col) CONTAINS timestamp3_col FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT timestamp1_col, timestamp2_col FROM table1 WHERE [PERIOD] (TIMESTAMP '2000-11-11 11:11:11', TIMESTAMP '2000-12-12 12:12:12') CONTAINS TIMESTAMP '2000-11-12 11:11:11' DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};

PERIOD CONTAINS PERIOD (DATE,DATE): ADB, ADQM, ADP
SELECT [PERIOD] (DATE '2000-11-11', DATE '2000-12-12') CONTAINS [PERIOD] (DATE '2000-11-12', DATE '2000-12-11') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT [PERIOD] (date1_col, date2_col) CONTAINS [PERIOD] (date3_col, date4_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT date1_col, date2_col FROM table1 WHERE [PERIOD] (DATE '2000-11-11', DATE '2000-12-12') CONTAINS [PERIOD] (DATE '2000-11-12', DATE '2000-12-11') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};

PERIOD CONTAINS PERIOD (TIMESTAMP,TIMESTAMP): ADB, ADQM, ADP
SELECT [PERIOD] (TIMESTAMP '2000-11-11 11:11:11', TIMESTAMP '2000-12-12 12:12:12') CONTAINS [PERIOD] (TIMESTAMP '2000-11-12 11:11:11', TIMESTAMP '2000-12-11 12:12:12') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT [PERIOD] (timestamp1_col, timestamp2_col) CONTAINS [PERIOD] (timestamp3_col, timestamp4_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT timestamp1_col, timestamp2_col FROM table1 WHERE [PERIOD] (TIMESTAMP '2000-11-11 11:11:11', TIMESTAMP '2000-12-12 12:12:12') CONTAINS [PERIOD] (TIMESTAMP '2000-11-12 11:11:11', TIMESTAMP '2000-12-11 12:12:12') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};

OVERLAPS

PERIOD OVERLAPS PERIOD (DATE,DATE): ADB, ADQM, ADP
SELECT [PERIOD] (DATE '2000-11-11', DATE '2000-12-12') OVERLAPS [PERIOD] (DATE '2000-11-12', DATE '2000-12-11') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT [PERIOD] (date1_col, date2_col) OVERLAPS [PERIOD] (date3_col, date4_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT date1_col, date2_col FROM table1 WHERE [PERIOD] (DATE '2000-11-11', DATE '2000-12-12') OVERLAPS [PERIOD] (DATE '2000-11-12', DATE '2000-12-11') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};

PERIOD OVERLAPS PERIOD (TIMESTAMP,TIMESTAMP): ADB, ADQM, ADP
SELECT [PERIOD] (TIMESTAMP '2000-11-11 11:11:11', TIMESTAMP '2000-12-12 12:12:12') OVERLAPS [PERIOD] (TIMESTAMP '2000-11-12 11:11:11', TIMESTAMP '2000-12-11 12:12:12') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT [PERIOD] (timestamp1_col, timestamp2_col) OVERLAPS [PERIOD] (timestamp3_col, timestamp4_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT timestamp1_col, timestamp2_col FROM table1 WHERE [PERIOD] (TIMESTAMP '2000-11-11 11:11:11', TIMESTAMP '2000-12-12 12:12:12') OVERLAPS [PERIOD] (TIMESTAMP '2000-11-12 11:11:11', TIMESTAMP '2000-12-11 12:12:12') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};

EQUALS

PERIOD EQUALS PERIOD (DATE,DATE): ADB, ADQM, ADP
SELECT [PERIOD] (DATE '2000-11-11', DATE '2000-12-12') EQUALS [PERIOD] (DATE '2000-11-12', DATE '2000-12-11') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT [PERIOD] (date1_col, date2_col) EQUALS [PERIOD] (date3_col, date4_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT date1_col, date2_col FROM table1 WHERE [PERIOD] (DATE '2000-11-11', DATE '2000-12-12') EQUALS [PERIOD] (DATE '2000-11-12', DATE '2000-12-11') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};

PERIOD EQUALS PERIOD (TIMESTAMP,TIMESTAMP): ADB, ADQM, ADP
SELECT [PERIOD] (TIMESTAMP '2000-11-11 11:11:11', TIMESTAMP '2000-12-12 12:12:12') EQUALS [PERIOD] (TIMESTAMP '2000-11-12 11:11:11', TIMESTAMP '2000-12-11 12:12:12') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT [PERIOD] (timestamp1_col, timestamp2_col) EQUALS [PERIOD] (timestamp3_col, timestamp4_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT timestamp1_col, timestamp2_col FROM table1 WHERE [PERIOD] (TIMESTAMP '2000-11-11 11:11:11', TIMESTAMP '2000-12-12 12:12:12') EQUALS [PERIOD] (TIMESTAMP '2000-11-12 11:11:11', TIMESTAMP '2000-12-11 12:12:12') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};

PRECEEDS

PERIOD PRECEEDS PERIOD (DATE,DATE): ADB, ADQM, ADP
SELECT [PERIOD] (DATE '2000-11-11', DATE '2000-12-12') [IMMEDIATELY] PRECEEDS [PERIOD] (DATE '2000-11-12', DATE '2000-12-11') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT [PERIOD] (date1_col, date2_col) [IMMEDIATELY] PRECEEDS [PERIOD] (date3_col, date4_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT date1_col, date2_col FROM table1 WHERE [PERIOD] (DATE '2000-11-11', DATE '2000-12-12') [IMMEDIATELY] PRECEEDS [PERIOD] (DATE '2000-11-12', DATE '2000-12-11') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};

PERIOD PRECEEDS PERIOD (TIMESTAMP,TIMESTAMP): ADB, ADQM, ADP
SELECT [PERIOD] (TIMESTAMP '2000-11-11 11:11:11', TIMESTAMP '2000-12-12 12:12:12') [IMMEDIATELY] PRECEEDS [PERIOD] (TIMESTAMP '2000-11-12 11:11:11', TIMESTAMP '2000-12-11 12:12:12') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT [PERIOD] (timestamp1_col, timestamp2_col) [IMMEDIATELY] PRECEEDS [PERIOD] (timestamp3_col, timestamp4_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT timestamp1_col, timestamp2_col FROM table1 WHERE [PERIOD] (TIMESTAMP '2000-11-11 11:11:11', TIMESTAMP '2000-12-12 12:12:12') [IMMEDIATELY] PRECEEDS [PERIOD] (TIMESTAMP '2000-11-12 11:11:11', TIMESTAMP '2000-12-11 12:12:12') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};

SUCCEEDS

PERIOD SUCCEEDS PERIOD (DATE,DATE): ADB, ADQM, ADP
SELECT [PERIOD] (DATE '2000-11-11', DATE '2000-12-12') [IMMEDIATELY] SUCCEEDS [PERIOD] (DATE '2000-11-12', DATE '2000-12-11') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT [PERIOD] (date1_col, date2_col) [IMMEDIATELY] SUCCEEDS [PERIOD] (date3_col, date4_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT date1_col, date2_col FROM table1 WHERE [PERIOD] (DATE '2000-11-11', DATE '2000-12-12') [IMMEDIATELY] SUCCEEDS [PERIOD] (DATE '2000-11-12', DATE '2000-12-11') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};

PERIOD SUCCEEDS PERIOD (TIMESTAMP,TIMESTAMP): ADB, ADQM, ADP
SELECT [PERIOD] (TIMESTAMP '2000-11-11 11:11:11', TIMESTAMP '2000-12-12 12:12:12') [IMMEDIATELY] SUCCEEDS [PERIOD] (TIMESTAMP '2000-11-12 11:11:11', TIMESTAMP '2000-12-11 12:12:12') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT [PERIOD] (timestamp1_col, timestamp2_col) [IMMEDIATELY] SUCCEEDS [PERIOD] (timestamp3_col, timestamp4_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};
SELECT timestamp1_col, timestamp2_col FROM table1 WHERE [PERIOD] (TIMESTAMP '2000-11-11 11:11:11', TIMESTAMP '2000-12-12 12:12:12') [IMMEDIATELY] SUCCEEDS [PERIOD] (TIMESTAMP '2000-11-12 11:11:11', TIMESTAMP '2000-12-11 12:12:12') DATASOURCE_TYPE = {'ADB' | 'ADQM'| 'ADP'};

Системные функции и операторы

CURRENT_CATALOG, CURRENT_SESSION, CURRENT_USER, SESSION_USER, CURRENT_ROLE, CURRENT_SCHEMA: ADB, ADP
SELECT CURRENT_CATALOG [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT SESSION_SESSION [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CURRENT_USER [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT SESSION_USER [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CURRENT_ROLE [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CURRENT_SCHEMA [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CURRENT_USER [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT SESSION_USER [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CURRENT_ROLE [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CURRENT_SCHEMA [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

Строковые функции и операторы

ASCII

ASCII: ADB, ADQM, ADP
SELECT ASCII('A') DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT * FROM table1 WHERE ASCII('A') = 65 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT * FROM table1 WHERE ASCII(varchar_col) = 65 FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

LIKE

LIKE: ADB, ADQM, ADP
SELECT 'abcd' LIKE '_bc%' DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT * FROM table1 WHERE varchar_col LIKE '_bc%' DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT * FROM table1 WHERE varchar_col LIKE '_bc%' FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

SIMILAR TO

SIMILAR TO ‘regexp’: ADB, ADQM, ADP
SELECT 'abc' SIMILAR TO '%(b|d)%' DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT * FROM table1 WHERE varchar_col SIMILAR TO '%\mabc\M%' DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT * FROM table1 WHERE varchar_col SIMILAR TO '%\mabc\M%' FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

CHAR_LENGTH

CHAR_LENGTH: ADB, ADQM, ADP
SELECT CHAR_LENGTH(varchar_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT CHAR_LENGTH(varchar_col) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

POSITION

POSITION: ADB, ADQM, ADP
SELECT POSITION('c' IN 'abcdef') [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT POSITION('c' IN 'abcdef') [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

UPPER

UPPER: ADB, ADQM, ADP
SELECT UPPER('abcdef') [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT UPPER('abcdef') [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

LOWER

LOWER: ADB, ADQM, ADP
SELECT LOWER('ABCDEG') [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT LOWER('ABCDEG') [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

SUBSTRING

SUBSTRING: ADB, ADQM, ADP
SELECT SUBSTRING('ABCDEG', 3, 2) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT SUBSTRING('ABCDEG', 3, 2) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

IS NULL

IS DISTINCT FROM: ADB, ADP
SELECT 1 IS [NOT] NULL DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT col1 IS [NOT] NULL FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

IS DISTINCT FROM

IS DISTINCT FROM: ADB, ADP
SELECT 1 IS [NOT] DISTINCT FROM 2 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT 1 IS [NOT] DISTINCT FROM NULL DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT col1 [NOT] IS DISTINCT FROM col2 FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

NULLIF

NULLIF: ADB, ADP, ADQM
SELECT NULLIF(col1, col2) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT NULLIF(int32_col,1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT NULLIF(varchar_col,'1.0') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT NULLIF(...) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

COALESCE

COALESCE: ADB, ADP, ADQM
SELECT COALESCE(int_col,1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT COALESCE(int32_col,1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT COALESCE(varchar_col,'1.0') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT COALESCE(...) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

COALESCE: ADB, ADP
SELECT COALESCE(boolean_col,true) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COALESCE(char_col,'1') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT COALESCE(...) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

COALESCE: ADB, ADP, ADQM
SELECT COALESCE(bigint_col,1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT COALESCE(CAST(varchar_col AS VARCHAR),'1.0') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT COALESCE(uuid_col,'1') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT COALESCE(link_col,'http://www.google.com') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT COALESCE(...) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

COALESCE: ADB, ADP
SELECT COALESCE(float_col,1.0) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COALESCE(double_col,1.0) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COALESCE(date_col,CAST('2001-01-01' AS DATE)) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COALESCE(time_col,CAST('11:12:13' AS TIME)) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COALESCE(timestamp_col,CAST('2001-01-01 11:12:13' AS TIMESTAMP)) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT COALESCE(...) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

COALESCE: не поддерживается
SELECT COALESCE(date_col,'2001-01-01') FROM table1;
SELECT COALESCE(time_col,'11:12:13') FROM table1;
SELECT COALESCE(timestamp_col,'2001-01-01 11:12:13') FROM table1;
Требуется явная конвертация строки даты-времени в соответствующий тип

TRIM

TRIM: ADB, ADQM, ADP
SELECT TRIM(' ABC XYZ ') [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT TRIM(' ABC XYZ ') [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

REPLACE

REPLACE: ADB, ADQM, ADP
SELECT REPLACE(' abc xyz ','ab', 'x') [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT REPLACE(' abc xyz ','ab', 'x') [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

CONCATENATION

CONCATENATION: ADB, ADQM, ADP
SELECT 'abc' || 'xyz' [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT 'abc' || 'xyz' [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

INITCAP

INITCAP: ADB, ADP
SELECT INITCAP('abc def ghi xyz') [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT INITCAP('abc def ghi xyz') [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

LISTAGG

LISTAGG: ADB, ADP
SELECT bigint_col, LISTAGG(varchar_col,'') WITHIN GROUP (ORDER BY varchar_col) FROM table1 GROUP BY bigint_col datasource_type = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT bigint_col, LISTAGG(varchar_col,'') WITHIN GROUP (ORDER BY varchar_col) FROM table1 GROUP BY bigint_col) SELECT * FROM cte1 datasource_type = {'ADB' | 'ADP'};

LISTAGG: ADB, ADP
SELECT bigint_col as varchar_col, LISTAGG(varchar_col,'') WITHIN GROUP (ORDER BY varchar_col) FROM table1 GROUP BY bigint_col datasource_type = {'ADB' | 'ADP'};
Результат запроса переменный в силу варьирующейся сортировки строк выражением ORDER BY с использованием alias

STRING_AGG

LISTAGG: ADB, ADP
SELECT STRINGAGG(varchar_col,'' [ORDER BY varchar_col]) FROM table1 GROUP BY bigint_col datasource_type = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT STRING_AGG(varchar_col,'' [ORDER BY varchar_col]) FROM table1) SELECT * FROM cte1 datasource_type = {'ADB' | 'ADP'};

Кавычки формата $$

$$quotes$$: ADB, ADP
SELECT $$quotes$$ = 'quotes' datasource_type = {'ADB' | 'ADP'};
SELECT $$quotes$$ datasource_type = {'ADB' | 'ADP'};
Кавычки, допускаемые в диалекте SQL для PostgreSQL

Текстовый поиск

TO_TSVECTOR и PLAINTO_TSQUERY

SELECT WHERE TO_TSVECTOR PLAINTO_TSQUERY: ADB, ADP
SELECT * FROM table1 WHERE to_tsvector(varchar_col) @@ plainto_tsquery('Фамилия');
SELECT * FROM table1 WHERE to_tsvector(coalesce(varchar_col, '-')) @@ plainto_tsquery('Фамилия');
SELECT * FROM table1 WHERE to_tsvector(coalesce(varchar1_col, '-')) @@ plainto_tsquery($$Фамилия$$) AND to_tsvector(coalesce(varchar2_col, '-')) @@ plainto_tsquery('Имя');
SELECT * FROM table1 WHERE to_tsvector('russian',coalesce(varchar_col, '-')) @@ plainto_tsquery('russian','Фамилия');
SELECT * FROM table1 WHERE to_tsvector('russian',coalesce(varchar_col, '-')) @@ plainto_tsquery('russian',$$Фамилия$$);

TO_TSVECTOR и TO_TSQUERY

SELECT WHERE TO_TSVECTOR TO_TSQUERY: ADB, ADP
SELECT * FROM table1 WHERE to_tsvector(varchar_col) @@ to_tsquery('Фамилия');
SELECT * FROM table1 WHERE to_tsvector(coalesce(varchar_col, '-')) @@ to_tsquery('Фамилия');
SELECT * FROM table1 WHERE to_tsvector(coalesce(varchar1_col, '-')) @@ to_tsquery($$Фамилия$$) AND to_tsvector(coalesce(varchar2_col, '-')) @@ to_tsquery('Имя');
SELECT * FROM table1 WHERE to_tsvector('russian',coalesce(varchar_col, '-')) @@ to_tsquery('russian','Фамилия');
SELECT * FROM table1 WHERE to_tsvector('russian',coalesce(varchar_col, '-')) @@ to_tsquery('russian',$$Фамилия$$);

TO_TSVECTOR и PHRASETO_TSQUERY

SELECT WHERE TO_TSVECTOR PHRASETO_TSQUERY: ADP
SELECT * FROM table1 WHERE to_tsvector(varchar_col) @@ phraseto_tsquery('Фамилия') DATASOURCE_TYPE = 'ADP';
SELECT * FROM table1 WHERE to_tsvector(coalesce(varchar_col, '-')) @@ phraseto_tsquery('Фамилия') DATASOURCE_TYPE = 'ADP';
SELECT * FROM table1 WHERE to_tsvector(coalesce(varchar1_col, '-')) @@ phraseto_tsquery($$Фамилия$$) AND to_tsvector(coalesce(varchar2_col, '-')) @@ phraseto_tsquery('Имя') DATASOURCE_TYPE = 'ADP';
SELECT * FROM table1 WHERE to_tsvector('russian',coalesce(varchar_col, '-')) @@ phraseto_tsquery('russian','Фамилия') DATASOURCE_TYPE = 'ADP';
SELECT * FROM table1 WHERE to_tsvector('russian',coalesce(varchar_col, '-')) @@ phraseto_tsquery('russian',$$Фамилия$$) DATASOURCE_TYPE = 'ADP';

TO_TSVECTOR и WEBSEARCH_TO_TSQUERY

SELECT WHERE TO_TSVECTOR WEBSEARCH_TO_TSQUERY: ADP
SELECT * FROM table1 WHERE to_tsvector(varchar_col) @@ websearch_to_tsquery('Фамилия') DATASOURCE_TYPE = 'ADP';
SELECT * FROM table1 WHERE to_tsvector(coalesce(varchar_col, '-')) @@ websearch_to_tsquery('Фамилия') DATASOURCE_TYPE = 'ADP';
SELECT * FROM table1 WHERE to_tsvector(coalesce(varchar1_col, '-')) @@ websearch_to_tsquery($$Фамилия$$) AND to_tsvector(coalesce(varchar2_col, '-')) @@ websearch_to_tsquery('Имя') DATASOURCE_TYPE = 'ADP';
SELECT * FROM table1 WHERE to_tsvector('russian',coalesce(varchar_col, '-')) @@ websearch_to_tsquery('russian','Фамилия') DATASOURCE_TYPE = 'ADP';
SELECT * FROM table1 WHERE to_tsvector('russian',coalesce(varchar_col, '-')) @@ websearch_to_tsquery('russian',$$Фамилия$$) DATASOURCE_TYPE = 'ADP';

Создание индексов для текстового поиска

CREATE INDEX USING GIN: ADP, ADB
CREATE INDEX fts_idx ON dm1.table1_actual USING gin (to_tsvector('russian',varchar_col));
CREATE INDEX fts_idx ON dm1.table1_actual USING gin (to_tsvector('russian',varchar_col)) DATASOURCE_TYPE ('ADP','ADP');

Математические функции и операторы

ABS

ABS: ADB, ADQM, ADP
SELECT ABS(-2.0) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT ABS(-2) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT ABS(-2.0) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT ABS(-2) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

ROUND

ROUND: ADB, ADQM, ADP
SELECT ROUND(-2.5) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT ROUND(-2.5) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

FLOOR

FLOOR: ADB, ADQM, ADP
SELECT FLOOR(-2.5) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT FLOOR(-2.5) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

CEIL

CEIL: ADB, ADQM, ADP
SELECT CEIL(-2.5) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT CEIL(-2.5) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

CEILING

CEILING: ADB, ADQM, ADP
SELECT CEILING(-2.5) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT CEILING(-2.5) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

BIT_AND

BIT_AND: ADB, ADP
SELECT BIT_AND(numeric_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT BIT_AND(numeric_col) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

BIT_OR

BIT_OR: ADB, ADP
SELECT BIT_OR(numeric_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT BIT_OR(numeric_col) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

BIT_XOR

BIT_XOR: ADB, ADP
SELECT BIT_XOR(numeric_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT BIT_OR(numeric_col) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

DEGREES

DEGREES: ADB, ADP
SELECT DEGREES(3.1415269) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT DEGREES(3.1415269) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

RADIANS

RADIANS: ADB, ADP
SELECT RADIANS(180.0) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT RADIANS(180.0) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

SIGN

SIGN: ADB, ADQM, ADP
SELECT SIGN(-2.0) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT SIGN(CAST(-2 AS FLOAT)) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT SIGN(-2.0) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT SIGN(CAST(-2 AS FLOAT)) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

RAND

RAND: ADQM
SELECT RAND(-2.0) [FROM table1] DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT RAND(-2.0) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';

SIN, COS, TAN, COT

SIN, COS, TAN, COT: ADB, ADQM, ADP
SELECT SIN(-2.0) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT COS(-2.0) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT TAN(-2.0) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT COT(-2.0) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT SIN(-2.0) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT COS(-2.0) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT TAN(-2.0) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT COT(-2.0) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

ASIN, ACOS, ATAN, ATAN2

ASIN, ACOS, ATAN, ATAN2: ADB, ADQM, ADP
SELECT ASIN(0.5) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT ACOS(0.5) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT ATAN(0.5) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT ATAN2(3.0,2.0) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT ASIN(0.5) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT ACOS(0.5) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT ATAN(0.5) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT ATAN2(3.0,2.0) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

POWER, EXP*, LN*

POWER, EXP, LN: ADB, ADQM*, ADP
SELECT POWER(0.5,2) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT EXP(1.0) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
SELECT LN(2.18281828) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT POWER(0.5,2) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT EXP(1.0) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT LN(2.18281828) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
* Возвращает недостаточно точные значения для EXP и LN

SQRT, CBRT

SQRT: ADB, ADQM, ADP
SELECT SQRT(4) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT SQRT(4) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

CBRT: ADB, ADP
SELECT CBRT(-8) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CBRT(-8) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

TRUNCATE

TRUNCATE: ADQM
SELECT TRUNCATE(12.345,1) [FROM table1] DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT TRUNCATE(12.345,1) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';

MOD

MOD: ADB, ADQM, ADP
SELECT MOD(8,3) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT MOD(8,3) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

MAX, MIN

MAX: ADB, ADQM, ADP
SELECT MAX(numeric_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT MAX(numeric_col) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

MIN: ADB, ADQM, ADP
SELECT MIN(numeric_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT MIN(numeric_col) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

SUM, COUNT

SUM: ADB, ADQM, ADP
SELECT SUM(numeric_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT SUM(numeric_col) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

COUNT: ADB, ADQM, ADP
SELECT COUNT(numeric_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
WITH cte1 AS (SELECT COUNT(numeric_col) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

AVG

AVG: ADB*, ADQM*, ADP*
SELECT AVG(CAST(numeric_col AS DOUBLE)) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT AVG(CAST(numeric_col AS FLOAT)) FROM table1 DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT AVG(CAST(numeric_col AS DOUBLE)) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT AVG(CAST(numeric_col AS FLOAT)) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';
* Нет неявного преобразования из целочисленного типа

COVAR

COVAR_POP: ADB, ADQM, ADP
SELECT COVAR_POP(CAST(numeric_col1 AS DOUBLE), numeric_col2) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COVAR_POP(CAST(numeric_col1 AS FLOAT), numeric_col2) FROM table1 DATASOURCE_TYPE = 'ADQM';
SELECT CAST(COVAR_POP(numeric_col1, numeric_col2) AS FLOAT) FROM table1 DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT COVAR_POP(CAST(numeric_col1 AS DOUBLE), numeric_col2) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT COVAR_POP(CAST(numeric_col1 AS FLOAT), numeric_col2) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT CAST(COVAR_POP(numeric_col1, numeric_col2) AS FLOAT) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';

COVAR_SAMP: ADB, ADQM, ADP
SELECT COVAR_SAMP(CAST(numeric_col1 AS DOUBLE), numeric_col2) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT COVAR_SAMP(CAST(numeric_col1 AS FLOAT), numeric_col2) FROM table1 DATASOURCE_TYPE = 'ADQM';
SELECT CAST(COVAR_SAMP(numeric_col1, numeric_col2) AS FLOAT) FROM table1 DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT COVAR_SAMP(CAST(numeric_col1 AS DOUBLE), numeric_col2) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT COVAR_SAMP(CAST(numeric_col1 AS FLOAT), numeric_col2) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT CAST(COVAR_SAMP(numeric_col1, numeric_col2) AS FLOAT) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';

VAR

VAR_POP: ADB, ADQM, ADP
SELECT CAST(VAR_POP(numeric_col) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(VAR_POP(numeric_col) AS FLOAT) FROM table1 DATASOURCE_TYPE = 'ADQM';
SELECT VAR_POP(CAST(numeric_col AS FLOAT)) FROM table1 DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT CAST(VAR_POP(numeric_col) AS DOUBLE) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(VAR_POP(numeric_col) AS FLOAT) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT VAR_POP(CAST(numeric_col AS FLOAT)) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';

VAR_SAMP: ADB, ADQM, ADP
SELECT CAST(VAR_SAMP(numeric_col) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(VAR_SAMP(numeric_col) AS FLOAT) FROM table1 DATASOURCE_TYPE = 'ADQM';
SELECT VAR_SAMP(CAST(numeric_col AS FLOAT)) FROM table1 DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT CAST(VAR_SAMP(numeric_col) AS DOUBLE) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(VAR_SAMP(numeric_col) AS FLOAT) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT VAR_SAMP(CAST(numeric_col AS FLOAT)) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';

STDDEV

STDDEV_POP: ADB, ADQM, ADP
SELECT CAST(STDDEV_POP(numeric_col) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(STDDEV_POP(numeric_col) AS FLOAT) FROM table1 DATASOURCE_TYPE = 'ADQM';
SELECT STDDEV_POP(CAST(numeric_col AS FLOAT)) FROM table1 DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT CAST(STDDEV_POP(numeric_col) AS DOUBLE) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(STDDEV_POP(numeric_col) AS FLOAT) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT STDDEV_POP(CAST(numeric_col AS FLOAT)) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';

STDDEV_SAMP: ADB, ADQM, ADP
SELECT CAST(STDDEV_SAMP(numeric_col) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(STDDEV_SAMP(numeric_col) AS FLOAT) FROM table1 DATASOURCE_TYPE = 'ADQM';
SELECT STDDEV_SAMP(CAST(numeric_col AS FLOAT)) FROM table1 DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT CAST(STDDEV_SAMP(numeric_col) AS DOUBLE) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(STDDEV_SAMP(numeric_col) AS FLOAT) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT STDDEV_SAMP(CAST(numeric_col AS FLOAT)) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';

Запросы с подзапросами

EXISTS

EXISTS: ADB, ADP
SELECT EXISTS (SELECT 1 LIMIT 0) DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT EXISTS (SELECT 1 FETCH FIRST 0 ROWS ONLY) DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT EXISTS (SELECT 1 FETCH NEXT 0 ROWS ONLY) DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT * FROM table1 WHERE EXISTS(SELECT * FROM table1) DATASOURCE_TYPE = {'ADB' | 'ADP'};

TRUNCATE HISTORY

TRUNCATE HISTORY: ADB, ADQM, ADG, ADP
TRUNCATE HISTORY table1 FOR SYSTEM_TIME AS OF '2021-01-01 23:59:59' WHERE int_col < 100;

TRUNCATE HISTORY: не поддерживается
TRUNCATE HISTORY table1 FOR SYSTEM_TIME AS OF '2021-01-01 23:59:59' WHERE int_col IN (SELECT int_col FROM table2);
Поддерживаются только постоянные условия в секции WHERE

DELETE

DELETE: ADB, ADQM, ADP
DELETE FROM table1 WHERE id IN (1, 2, 3, 4);
DELETE FROM table1 WHERE id IN (SELECT id FROM table2);

INSERT SELECT

Вставка из одной логической таблицы в другую: ADB, ADQM, ADP
INSERT INTO table1 (id, int32_col) SELECT id_col, COALESCE(int32_col,10) FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, int_col) SELECT id_col, COALESCE(int_col,-20) FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, bigint_col) SELECT id_col, COALESCE(bigint_col,-20) FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, float_col) SELECT id_col, COALESCE(float_col,10.5) FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, double_col) SELECT id_col, COALESCE(double_col,-0.5) FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, char10_col) SELECT id_col, COALESCE(char10_col,'0123456789') FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, varchar_col) SELECT id_col, COALESCE(varchar_col,'0123456789') FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, uuid_col) SELECT id_col, COALESCE(uuid_col,'0123456789') FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, link_col) SELECT id_col, COALESCE(link_col,'0123456789') FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
Источник данных логической таблицы table2 и приемник данных логической таблицы table1 должны удовлетворять поддерживаемым связям между ними

Вставка из одной логической таблицы в другую: ADB, ADP
INSERT INTO table1 (id, boolean_col) SELECT id_col, COALESCE(boolean_col,true) FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP'};
INSERT INTO table1 (id, date_col) SELECT id_col, COALESCE(date_col,CAST('2001-02-03' AS DATE)) FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP'};
INSERT INTO table1 (id, time_col) SELECT id_col, COALESCE(time_col,CAST('12:12:12' AS TIME)) FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP'};
INSERT INTO table1 (id, timestamp_col) SELECT id_col, COALESCE(timestamp_col,CAST('2001-02-03 12:12:12' AS TIMESTAMP)) FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP'};
Источник данных логической таблицы table2 и приемник данных логической таблицы table1 должны удовлетворять поддерживаемым связям между ними

Вставка из одной логической таблицы в другую c подзапросом: ADB, ADQM, ADG, ADP
INSERT INTO datamart.table01 SELECT * FROM (SELECT * FROM datamart.table01 AS t01 UNION ALL SELECT * FROM datamart.table01 AS t01) AS mv_query DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADG' |'ADP'};
INSERT INTO datamart.table01 SELECT * FROM (SELECT * FROM datamart.table01 AS OF CN 1747212142918085 AS t01 UNION ALL SELECT * FROM datamart.table01 AS OF CN 1747212142918085 AS t01) AS mv_query DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADG' |'ADP'};

Вставка из одной логической таблицы в другую c подзапросом*: ADB, ADQM, ADP
INSERT INTO datamart.table01 SELECT * FROM datamart.table01 t01 WHERE t01.id NOT IN (SELECT id FROM datamart.table01 DATASOURCE_TYPE = {'ADB' | 'ADP'});
INSERT INTO datamart.table01 SELECT * FROM (SELECT * FROM datamart.table01 FOR SYSTEM_TIME STARTED CN (0, 10) AS t01 UNION ALL SELECT * FROM datamart.table01 FOR_SYSTEM_TIME FINISHED CN (0, 10) AS t01) AS mv_query DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
INSERT INTO datamart.table01 SELECT * FROM (SELECT * FROM datamart.table01 FOR SYSTEM_TIME STARTED CN (0, 1747212142918085) AS t01 UNION ALL SELECT * FROM datamart.table01 FOR_SYSTEM_TIME FINISHED CN (0, 1747212142918085) AS t01) AS mv_query DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
* не поддерживается в ADG

Вставка из внешней readable-таблицы в логическую таблицу: ADB, ADQM, ADG, ADP
INSERT INTO table1 (id, int32_col1) SELECT id_col, COALESCE(int32_col,10) FROM readable_external_table2;
INSERT INTO table1 (id, int_col1) SELECT id_col, COALESCE(int_col,10) FROM readable_external_table2;
INSERT INTO table1 (id, bigint_col1) SELECT id_col, COALESCE(bigint_col,10) FROM readable_external_table2;
INSERT INTO table1 (id, float_col1) SELECT id_col, COALESCE(float_col,10.5) FROM readable_external_table2;
INSERT INTO table1 (id, double_col1) SELECT id_col, COALESCE(double_col,-0.5) FROM readable_external_table2;
INSERT INTO table1 (id, char10_col1) SELECT id_col, COALESCE(char10_col,'0123456789') FROM readable_external_table2;
INSERT INTO table1 (id, varchar_col1) SELECT id_col, COALESCE(varchar_col,10) FROM readable_external_table2;
INSERT INTO table1 (id, uuid_col1) SELECT id_col, COALESCE(uuid_col,10) FROM readable_external_table2;
INSERT INTO table1 (id, link_col1) SELECT id_col, COALESCE(link_col,10) FROM readable_external_table2;
INSERT INTO table1 (id, boolean_col1) SELECT id_col, COALESCE(boolean_col,true) FROM readable_external_table2;
INSERT INTO table1 (id, date_col1) SELECT id_col, COALESCE(date_col,CAST('2030-01-01' AS DATE)) FROM readable_external_table2;
INSERT INTO table1 (id, time_col1) SELECT id_col, COALESCE(time_col,CAST('12:12:12' AS TIME)) FROM readable_external_table2;
INSERT INTO table1 (id, datetime_col1) SELECT id_col, COALESCE(datetime_col,CAST('2030-01-01 12:12:12' AS TIMESTAMP)) FROM readable_external_table2;
Источник данных внешней таблицы чтения readable_external_table2 и приемник данных логической таблицы table1 должны удовлетворять поддерживаемым связям между ними

Вставка из логической таблицы во внешнюю writable-таблицу: ADB, ADQM, ADG, ADP
INSERT INTO writable_external_table1 (id, int32_col1) SELECT id_col, COALESCE(int32_col,10) FROM table2;
INSERT INTO writable_external_table1 (id, int_col1) SELECT id_col, COALESCE(int_col,10) FROM table2;
INSERT INTO writable_external_table1 (id, bigint_col1) SELECT id_col, COALESCE(bigint_col,10) FROM table2;
INSERT INTO writable_external_table1 (id,float_col1) SELECT id_col, COALESCE(float_col,10.5) FROM table2;
INSERT INTO writable_external_table1 (id, double_col1) SELECT id_col, COALESCE(double_col,-0.5) FROM table2;
INSERT INTO writable_external_table1 (id, char10_col1) SELECT id_col, COALESCE(char10_col,'0123456789') FROM table2;
INSERT INTO writable_external_table1 (id, varchar_col1) SELECT id_col, COALESCE(varchar_col,10) FROM table2;
INSERT INTO writable_external_table1 (id, uuid_col1) SELECT id_col, COALESCE(uuid_col,10) FROM table2;
INSERT INTO writable_external_table1 (id, link_col1) SELECT id_col, COALESCE(link_col,10) FROM table2;
INSERT INTO writable_external_table1 (id, boolean_col1) SELECT id_col, COALESCE(boolean_col,true) FROM table2;
INSERT INTO writable_external_table1 (id, date_col1) SELECT id_col, COALESCE(date_col,true) FROM table2;
INSERT INTO writable_external_table1 (id, time_col1) SELECT id_col, COALESCE(time_col,true) FROM table2;
INSERT INTO writable_external_table1 (id, datetime_col1) SELECT id_col, COALESCE(datetime_col,true) FROM table2;
Источник данных логической таблицы table2 и приемник данных внешней таблицы записи writable_external_table1 должны удовлетворять поддерживаемым связям между ними