Поддержка SQL

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

1. Преобразование типов
   1. Неявное преобразование
   2. CAST
2. CASE WHEN … THEN … ELSE … в SELECT
3. Псевдонимы столбцов в SELECT
4. Запросы с подзапросами
   1. TRUNCATE HISTORY
   2. DELETE
   3. INSERT SELECT
5. Фильтры запроса SELECT
   1. LIMIT, OFFSET, FETCH NEXT ROWS ONLY
6. Фильтры агрегационных функций
   1. SELECT AGG FILTER
   2. SELECT AGG FROM HAVING FILTER
   3. SELECT CASE WHEN (AGG FILTER) THEN (AGG FILTER) ELSE END FROM
7. Функции и операторы соединения
   1. UNION
   2. INTERSECT
   3. EXCEPT
   4. JOIN
8. Функции и операторы даты и времени
   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
9. Системные функции и операторы
10. Строковые функции и операторы
    1. CHAR_LENGTH
    2. POSITION
    3. UPPER
    4. LOWER
    5. SUBSTRING
    6. COALESCE
    7. TRIM
    8. REPLACE
    9. CONCATENATION
    10. INITCAP
    11. LISTAGG
11. Математические функции и операторы
    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. Для каждого примера указаны СУБД, в которых он доступен; если пример использования недоступен ни в одной из СУБД, для него указана строка «Не поддерживается».

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

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

Из 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 int32): не поддерживается
SELECT CAST(boolean_col as int32) FROM table1;

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 COALESCE(int32_col,10) FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, int_col) SELECT COALESCE(int_col,-20) FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, bigint_col) SELECT COALESCE(bigint_col,-20) FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, float_col) SELECT COALESCE(float_col,10.5) FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, double_col) SELECT COALESCE(double_col,-0.5) FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, char10_col) SELECT COALESCE(char10_col,'0123456789') FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, varchar_col) SELECT COALESCE(varchar_col,'0123456789') FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, uuid_col) SELECT COALESCE(uuid_col,'0123456789') FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table1 (id, link_col) SELECT COALESCE(link_col,'0123456789') FROM table2 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

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

Вставка из внешней readable-таблицы в логическую таблицу: ADB, ADQM, ADP
INSERT INTO table1 (int32_col1) SELECT COALESCE(int32_col,10) FROM readable_external_table2;
INSERT INTO table1 (int_col1) SELECT COALESCE(int_col,10) FROM readable_external_table2;
INSERT INTO table1 (bigint_col1) SELECT COALESCE(bigint_col,10) FROM readable_external_table2;
INSERT INTO table1 (float_col1) SELECT COALESCE(float_col,10.5) FROM readable_external_table2;
INSERT INTO table1 (double_col1) SELECT COALESCE(int32_col,-0.5) FROM readable_external_table2;
INSERT INTO table1 (char10_col1) SELECT COALESCE(char10_col,'0123456789') FROM readable_external_table2;
INSERT INTO table1 (varchar_col1) SELECT COALESCE(varchar_col,10) FROM readable_external_table2;
INSERT INTO table1 (uuid_col1) SELECT COALESCE(uuid_col,10) FROM readable_external_table2;
INSERT INTO table1 (link_col1) SELECT COALESCE(link_col,10) FROM readable_external_table2;
Значение параметра LOCATION внешней таблицы readable_external_table2 и значение параметра DATASOURCE_TYPE логической таблицы table1 должны относиться к одному датасорсу.

Вставка из внешней readable-таблицы в логическую таблицу: ADB, ADP
INSERT INTO table1 (boolean_col1) SELECT COALESCE(boolean_col,true) FROM readable_external_table2;
INSERT INTO table1 (date_col1) SELECT COALESCE(date_col,true) FROM readable_external_table2;
INSERT INTO table1 (time_col1) SELECT COALESCE(time_col,true) FROM readable_external_table2;
INSERT INTO table1 (datetime_col1) SELECT COALESCE(datetime_col,true) FROM readable_external_table2;
Значение параметра LOCATION внешней таблицы readable_external_table2 и значение параметра DATASOURCE_TYPE логической таблицы table1 должны относиться к одному датасорсу.

Вставка из логической таблицы во внешнюю writable-таблицу: ADB, ADQM, ADP
INSERT INTO writable_external_table1 (int32_col1) SELECT COALESCE(int32_col,10) FROM table2;
INSERT INTO writable_external_table1 (int_col1) SELECT COALESCE(int_col,10) FROM table2;
INSERT INTO writable_external_table1 (bigint_col1) SELECT COALESCE(bigint_col,10) FROM table2;
INSERT INTO writable_external_table1 (float_col1) SELECT COALESCE(float_col,10.5) FROM table2;
INSERT INTO writable_external_table1 (double_col1) SELECT COALESCE(int32_col,-0.5) FROM table2;
INSERT INTO writable_external_table1 (char10_col1) SELECT COALESCE(char10_col,'0123456789') FROM table2;
INSERT INTO writable_external_table1 (varchar_col1) SELECT COALESCE(varchar_col,10) FROM table2;
INSERT INTO writable_external_table1 (uuid_col1) SELECT COALESCE(uuid_col,10) FROM table2;
INSERT INTO writable_external_table1 (link_col1) SELECT COALESCE(link_col,10) FROM table2;
Значение параметра LOCATION внешней таблицы writable_external_table1 и значение параметра DATASOURCE_TYPE логической таблицы table2 должны относиться к одному датасорсу.

Вставка из логической таблицы во внешнюю writable-таблицу: ADB, ADP
INSERT INTO writable_external_table1 (boolean_col1) SELECT COALESCE(boolean_col,true) FROM table2;
INSERT INTO writable_external_table1 (date_col1) SELECT COALESCE(date_col,true) FROM table2;
INSERT INTO writable_external_table1 (time_col1) SELECT COALESCE(time_col,true) FROM table2;
INSERT INTO writable_external_table1 (datetime_col1) SELECT COALESCE(datetime_col,true) FROM table2;
Значение параметра LOCATION внешней таблицы writable_external_table1 и значение параметра DATASOURCE_TYPE логической таблицы table2 должны относиться к одному датасорсу.

INSERT SELECT: ADB, ADQM, ADP
INSERT INTO writable_external_table1 (int32_col1) SELECT COALESCE(int32_col,10) FROM table2;
INSERT INTO writable_external_table1 (int_col1) SELECT COALESCE(int_col,10) FROM table2;
INSERT INTO writable_external_table1 (bigint_col1) SELECT COALESCE(bigint_col,10) FROM table2;
INSERT INTO writable_external_table1 (float_col1) SELECT COALESCE(float_col,10.5) FROM table2;
INSERT INTO writable_external_table1 (double_col1) SELECT COALESCE(int32_col,-0.5) FROM table2;
INSERT INTO writable_external_table1 (char10_col1) SELECT COALESCE(char10_col,'0123456789') FROM table2;
INSERT INTO writable_external_table1 (varchar_col1) SELECT COALESCE(varchar_col,10) FROM table2;
INSERT INTO writable_external_table1 (uuid_col1) SELECT COALESCE(uuid_col,10) FROM table2;
INSERT INTO writable_external_table1 (link_col1) SELECT COALESCE(link_col,10) FROM table2;
Значение параметра LOCATION внешней таблицы writable_external_table1 и значение параметра DATASOURCE_TYPE логической таблицы table2 должны относиться к одному датасорсу.

Фильтры запроса 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 NEXT ROWS ONLY: ADB, ADQM, ADP
SELECT * FROM table1 order by id_col FETCH NEXT 1 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'};

FETCH NEXT ROWS ONLY OFFSET: ADB, ADQM, ADP
SELECT * FROM table1 order by id_col FETCH NEXT 1 ROWS ONLY OFFSET 1 DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 order by id_col FETCH NEXT 1 ROWS ONLY OFFSET 2 ROW DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADP'};
SELECT * FROM table1 order by id_col FETCH NEXT 1 ROWS ONLY OFFSET 3 ROWS 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 testdb2797.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 testdb2797.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 testdb2797.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 testdb2797.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 testdb2797.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 testdb2797.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 testdb2797.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 db99990.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

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, ADG, ADP
SELECT * FROM table1 WHERE date_col = '2021-01-02' DATASOURCE_TYPE = {'ADB' | 'ADQM' | 'ADG' | 'ADP'};

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

CURRENT_DATE

CURRENT_DATE: ADB, ADP
SELECT CURRENT_DATE FROM table1 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' | 'ADG' | 'ADP'};

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

CURRENT_TIME

CURRENT_TIME: ADB, ADP
SELECT CURRENT_TIME FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

CURRENT_TIME: не поддерживается
SELECT CURRENT_TIME FROM table1;

CAST AS TIMESTAMP

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

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

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP: ADB, ADP
SELECT CURRENT_TIMESTAMP FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

CURRENT_TIMESTAMP: не поддерживается
SELECT CURRENT_TIMESTAMP FROM table1;

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

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

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

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

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

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

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

TIMESTAMPADD(YEAR | MONTH |DAY | HOUR | MINUTE | SECOND, <integer>, <datetime>): ADB, ADP
SELECT TIMESTAMPADD(YEAR, -1, CURRENT_TIMESTAMP) FROM table1 LIMIT 1 FROM table1 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'};

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

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

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

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

CHAR_LENGTH

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

POSITION

POSITION: ADB, ADP
SELECT POSITION('c' IN 'abcdef') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

UPPER

UPPER: ADB, ADQM, ADP
SELECT UPPER('abcdef') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

LOWER

LOWER: ADB, ADQM, ADP
SELECT LOWER('ABCDEG') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

SUBSTRING

SUBSTRING: ADB, ADQM, ADP
SELECT SUBSTRING('ABCDEG', 3, 2) FROM table1 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'};

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

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

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

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

REPLACE

REPLACE: ADB, ADQM, ADP
SELECT REPLACE(' abc xyz ','ab', 'x') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

CONCATENATION

CONCATENATION: ADB, ADQM, ADP
SELECT 'abc' || 'xyz' FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

INITCAP

INITCAP: ADB, ADP
SELECT INITCAP('abc def ghi xyz') FROM table1 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'};

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.

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

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

ROUND

ROUND: ADB, ADQM, ADP
SELECT ROUND(-2.5) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

FLOOR

FLOOR: ADB, ADQM, ADP
SELECT FLOOR(-2.5) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

CEIL

CEIL: ADB, ADQM, ADP
SELECT CEIL(-2.5) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

CEILING

CEILING: ADB, ADQM, ADP
SELECT CEILING(-2.5) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

BIT_AND

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

BIT_OR

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

DEGREES

DEGREES: ADB, ADP
SELECT DEGREES(3.1415269) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

RADIANS

RADIANS: ADB, ADP
SELECT RADIANS(180.0) FROM table1 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'};

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

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

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'};
* Возвращает недостаточно точные значения для EXP и LN

SQRT, CBRT

SQRT: ADB, ADQM, ADP
SELECT SQRT(4) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};

CBRT: ADB, ADP
SELECT CBRT(-8) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

MOD

MOD: ADB, ADP
SELECT MOD(8,3) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};

MAX, MIN

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

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

SUM, COUNT

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

COUNT: ADB, ADQM, ADP
SELECT COUNT(numeric_col) FROM table1 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';
* Нет неявного преобразования из целочисленного типа

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

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

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

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

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

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