Поддержка SQL

Содержание раздела
  1. Партиционированные таблицы и партиции
    1. Выборки из партиционированных таблиц
    2. Выборки из партиций
    3. Удаление данных из партиционированных таблиц
  2. RETENTION-таблицы
    1. Запросы из RETENTION-таблиц
  3. Последовательности
    1. Создание и удаление последовательностей
    2. Функции для работы с последовательностями
  4. Преобразование типов
    1. Неявное преобразование
    2. CAST
  5. CASE WHEN … THEN … ELSE … в SELECT
  6. Псевдонимы столбцов в SELECT
  7. Запросы с подзапросами
    1. TRUNCATE HISTORY
    2. DELETE
    3. INSERT SELECT
  8. Фильтры запроса SELECT
    1. LIMIT, OFFSET, FETCH NEXT ROWS ONLY
  9. Фильтры агрегационных функций
    1. SELECT AGG FILTER
    2. SELECT AGG FROM HAVING FILTER
    3. SELECT CASE WHEN (AGG FILTER) THEN (AGG FILTER) ELSE END FROM
  10. Функции и операторы соединения
    1. UNION
    2. INTERSECT
    3. EXCEPT
    4. JOIN
  11. Функции и операторы даты и времени
    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
  12. Системные функции и операторы
  13. Строковые функции и операторы
    1. LIKE
    2. CHAR_LENGTH
    3. POSITION
    4. UPPER
    5. LOWER
    6. SUBSTRING
    7. COALESCE
    8. TRIM
    9. REPLACE
    10. CONCATENATION
    11. INITCAP
    12. LISTAGG
    13. Кавычки формата $$
  14. Текстовый поиск
    1. TO_TSVECTOR и TO_TSQUERY
    2. Создание индексов для текстового поиска
  15. Математические функции и операторы
    1. ABS
    2. ROUND
    3. FLOOR
    4. CEIL
    5. CEILING
    6. BIT_AND
    7. BIT_OR
    8. DEGREES
    9. RADIANS
    10. SIGN
    11. SIN, COS, TAN, COT
    12. ASIN, ACOS, ATAN, ATAN2
    13. POWER, EXP*, LN*
    14. SQRT, CBRT
    15. MOD
    16. MAX, MIN
    17. SUM, COUNT
    18. AVG
    19. COVAR
    20. VAR
    21. STDDEV

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

RETENTION-таблицы

Запросы из RETENTION-таблиц

SELECT col1 AS “alias” FROM: ADB, ADQM, ADG, ADP
SELECT col1 AS "col_one" FROM table1;
SELECT col1 AS “alias” FROM: не поддерживается
SELECT col1 AS "col-one" FROM table1;
Псевдонимы, содержащие символ -, не поддерживаются

Последовательности

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

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 … в SELECT

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;

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

AS “col1_alias”: ADB, ADQM, ADG, ADP
SELECT col1 AS "col1_alias" FROM table1;
AS “keyword”: не поддерживается
SELECT col1 AS "END-EXEC" FROM table1;

Полный список зарезервированных слов (keyword) доступен в разделе Зарезервированные слова.

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

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 должны удовлетворять поддерживаемым связям между ними
Вставка из внешней 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 должны удовлетворять поддерживаемым связям между ними

Фильтры запроса SELECT

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'};

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

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'};

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

UNION

UNION ALL: ADB, ADQM, ADG, 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'};

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

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'};
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, ADG, 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'};
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'};
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, ADQM, ADP
SELECT CAST(EXTRACT(EPOCH FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | '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 EXTRACT(QUARTER FROM DATE '2001-02-16') [FROM table1] DATASOURCE_TYPE = 'ADQM';
SELECT CAST(EXTRACT(YEAR FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT EXTRACT(YEAR FROM DATE '2001-02-16') [FROM table1] DATASOURCE_TYPE = 'ADQM';
SELECT CAST(EXTRACT(MONTH FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT EXTRACT(MONTH FROM DATE '2001-02-16') [FROM table1] DATASOURCE_TYPE = 'ADQM';
SELECT CAST(EXTRACT(DAY FROM DATE '2001-02-16') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT EXTRACT(DAY FROM DATE '2001-02-16') [FROM table] DATASOURCE_TYPE = 'ADQM';
WITH cte1 AS (SELECT CAST(EXTRACT(...) AS INT) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT (EXTRACT(...) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';
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, ADQM, 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 EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40') [FROM table1] DATASOURCE_TYPE = 'ADQM';
SELECT CAST(EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') [FROM table1] DATASOURCE_TYPE = 'ADQM';
SELECT CAST(EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40') [FROM table1] DATASOURCE_TYPE = 'ADQM';
SELECT CAST(EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40') [FROM table1] DATASOURCE_TYPE = 'ADQM';
SELECT CAST(EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') [FROM table1] DATASOURCE_TYPE = 'ADQM';
SELECT CAST(EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40') [FROM table1] DATASOURCE_TYPE = 'ADQM';
SELECT CAST(EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40') AS INT) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40') [FROM table1] DATASOURCE_TYPE = 'ADQM';
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'};
WITH cte1 AS (SELECT (EXTRACT(...) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = 'ADQM';

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'};

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

CURRENT_USER, SESSION_USER, CURRENT_ROLE, CURRENT_SCHEMA: 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'};

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

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'};

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, ADP
SELECT POSITION('c' IN 'abcdef') [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT POSITION('c' IN 'abcdef') [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

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'};

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

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

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

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

TO_TSVECTOR и TO_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',$$Фамилия$$);
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',$$Фамилия$$);
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';
SELECT WHERE TO_TSVECTOR WEBSEARCHTO_TSQUERY: ADP
SELECT * FROM table1
WHERE to_tsvector(varchar_col) @@ websearchto_tsquery('Фамилия')
DATASOURCE_TYPE = 'ADP';
SELECT * FROM table1
WHERE to_tsvector(coalesce(varchar_col, '-')) @@ websearchto_tsquery('Фамилия')
DATASOURCE_TYPE = 'ADP';
SELECT * FROM table1
WHERE to_tsvector(coalesce(varchar1_col, '-')) @@ websearchto_tsquery($$Фамилия$$) AND
to_tsvector(coalesce(varchar2_col, '-')) @@ websearchto_tsquery('Имя')
DATASOURCE_TYPE = 'ADP';
SELECT * FROM table1
WHERE to_tsvector('russian',coalesce(varchar_col, '-')) @@ websearchto_tsquery('russian','Фамилия')
DATASOURCE_TYPE = 'ADP';
SELECT * FROM table1
WHERE to_tsvector('russian',coalesce(varchar_col, '-')) @@ websearchto_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'};

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, ADP
SELECT SIGN(-2.0) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT SIGN(CAST(-2 AS FLOAT)) [FROM table1] DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT SIGN(-2.0) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT SIGN(CAST(-2 AS FLOAT)) [FROM table1]) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

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'};

MOD

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

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, numeric_col) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(VAR_POP(numeric_col, 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, numeric_col) AS DOUBLE) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(VAR_POP(numeric_col, 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, numeric_col) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(VAR_SAMP(numeric_col, 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, numeric_col) AS DOUBLE) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(VAR_SAMP(numeric_col, 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, numeric_col) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(STDDEV_POP(numeric_col, 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, numeric_col) AS DOUBLE) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(STDDEV_POP(numeric_col, 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, numeric_col) AS DOUBLE) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CAST(STDDEV_SAMP(numeric_col, 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, numeric_col) AS DOUBLE) FROM table1) SELECT * FROM cte1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
WITH cte1 AS (SELECT CAST(STDDEV_SAMP(numeric_col, 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';