Поддержка SQL

Содержание раздела
  1. Преобразование типов
    1. Неявное преобразование
    2. CAST
  2. Запросы с подзапросами
    1. TRUNCATE HISTORY
    2. DELETE
    3. INSERT SELECT
  3. Функции и операторы соединения
    1. UNION
    2. INTERSECT
    3. EXCEPT
    4. JOIN
  4. Функции и операторы даты и времени
    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
  5. Системные функции и операторы
  6. Строковые функции и операторы
    1. CHAR_LENGTH
    2. POSITION
    3. UPPER
    4. LOWER
    5. SUBSTRING
    6. COALESCE
    7. TRIM
    8. REPLACE
    9. CONCATENATION
    10. INITCAP
    11. LISTAGG
  7. Математические функции и операторы
    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
  8. Функции в SQL+ запросах
    1. COALESCE: INSERT SELECT

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

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

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

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

INSERT SELECT: ADB, ADQM, ADP
INSERT INTO table1 (int32_col1) AS
SELECT COALESCE(int32_col,10) FROM readable_external_table2;
INSERT INTO table1 (int_col1) AS
SELECT COALESCE(int_col,10) FROM readable_external_table2;
INSERT INTO table1 (bigint_col1) AS
SELECT COALESCE(bigint_col,10) FROM readable_external_table2;
INSERT INTO table1 (float_col1) AS
SELECT COALESCE(float_col,10.5) FROM readable_external_table2;
INSERT INTO table1 (double_col1) AS
SELECT COALESCE(int32_col,-0.5) FROM readable_external_table2;
INSERT INTO table1 (char10_col1) AS
SELECT COALESCE(char10_col,'0123456789') FROM readable_external_table2;
INSERT INTO table1 (varchar_col1) AS
SELECT COALESCE(varchar_col,10) FROM readable_external_table2;
INSERT INTO table1 (uuid_col1) AS
SELECT COALESCE(uuid_col,10) FROM readable_external_table2;
INSERT INTO table1 (link_col1) AS
SELECT COALESCE(link_col,10) FROM readable_external_table2;
Значение параметра LOCATION внешней таблицы readable_external_table2 и значение параметра DATASOURCE_TYPE логической таблицы table1 должны относиться к одной СУБД.
INSERT SELECT : ADB, ADP
INSERT INTO table1 (boolean_col1) AS
SELECT COALESCE(boolean_col,true) FROM readable_external_table2;
INSERT INTO table1 (date_col1) AS
SELECT COALESCE(date_col,true) FROM readable_external_table2;
INSERT INTO table1 (time_col1) AS
SELECT COALESCE(time_col,true) FROM readable_external_table2;
INSERT INTO table1 (datetime_col1) AS
SELECT COALESCE(datetime_col,true) FROM readable_external_table2;
Значение параметра LOCATION внешней таблицы readable_external_table2 и значение параметра DATASOURCE_TYPE логической таблицы table1 должны относиться к одной СУБД.
INSERT SELECT: ADB, ADQM, ADP
INSERT INTO writable_external_table1 (int32_col1) AS
SELECT COALESCE(int32_col,10) FROM table2;
INSERT INTO writable_external_table1 (int_col1) AS
SELECT COALESCE(int_col,10) FROM table2;
INSERT INTO writable_external_table1 (bigint_col1) AS
SELECT COALESCE(bigint_col,10) FROM table2;
INSERT INTO writable_external_table1 (float_col1) AS
SELECT COALESCE(float_col,10.5) FROM table2;
INSERT INTO writable_external_table1 (double_col1) AS
SELECT COALESCE(int32_col,-0.5) FROM table2;
INSERT INTO writable_external_table1 (char10_col1) AS
SELECT COALESCE(char10_col,'0123456789') FROM table2;
INSERT INTO writable_external_table1 (varchar_col1) AS
SELECT COALESCE(varchar_col,10) FROM table2;
INSERT INTO writable_external_table1 (uuid_col1) AS
SELECT COALESCE(uuid_col,10) FROM table2;
INSERT INTO writable_external_table1 (link_col1) AS
SELECT COALESCE(link_col,10) FROM table2;
Значение параметра LOCATION внешней таблицы writable_external_table1 и значение параметра DATASOURCE_TYPE логической таблицы table2 должны относиться к одной СУБД.
INSERT SELECT: ADB, ADP
INSERT INTO writable_external_table1 (boolean_col1) AS
SELECT COALESCE(boolean_col,true) FROM table2;
INSERT INTO writable_external_table1 (date_col1) AS
SELECT COALESCE(date_col,true) FROM table2;
INSERT INTO writable_external_table1 (time_col1) AS
SELECT COALESCE(time_col,true) FROM table2;
INSERT INTO writable_external_table1 (datetime_col1) AS
SELECT COALESCE(datetime_col,true) FROM table2;
Значение параметра LOCATION внешней таблицы writable_external_table1 и значение параметра DATASOURCE_TYPE логической таблицы table2 должны относиться к одной СУБД.

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

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, ADG, ADP
SELECT a.*
FROM (
SELECT b.id
FROM (SELECT id from table2) b
INTERSECT
SELECT id
FROM table1
) as a DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADG'};

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, ADG, 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' | 'ADG'};
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, ADG, ADP
SELECT * FROM table1 t1
CROSS JOIN table2 t2
ORDER BY t1.id, t2.category_name limit 5
DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
LEFT JOIN для нескольких таблиц: ADB, ADG, 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' | 'ADG'};
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 CAST(CURRENT_DATE AS DATE) FROM table1 DATASOURCE_TYPE = {'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 CAST(CURRENT_TIME AS 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 CAST(CURRENT_TIMESTAMP AS TIMESTAMP) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
SELECT CURRENT_TIMESTAMP FROM table1 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 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'};

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

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, ADG, ADP
SELECT CHAR_LENGTH(varchar_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM' | 'ADG'};

POSITION

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

UPPER

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

LOWER

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

SUBSTRING

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

COALESCE

COALESCE: ADB, ADP, ADQM, ADG
SELECT COALESCE(int_col,1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM' | 'ADG'};
SELECT COALESCE(int32_col,1) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM' | 'ADG'};
SELECT COALESCE(varchar_col,'1.0') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'| 'ADG'};
COALESCE: ADB, ADP, ADG
SELECT COALESCE(boolean_col,true) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADG'};
SELECT COALESCE(char_col,'1') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADG'};
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, ADG, ADP
SELECT TRIM(' ABC XYZ ') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM' | 'ADG'};

REPLACE

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

CONCATENATION

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

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, ADG, ADP
SELECT ABS(-2.0) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADG'};
SELECT ABS(-2) FROM table1 DATASOURCE_TYPE = 'ADQM';

ROUND

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

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, ADG, ADP
SELECT MAX(numeric_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM' | 'ADG'};
MIN: ADB, ADQM, ADG, ADP
SELECT MIN(numeric_col) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM' | 'ADG'};

SUM, COUNT

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

AVG

AVG: ADB*, ADQM*, ADG**, ADP*
SELECT AVG(CAST(numeric_col AS DOUBLE)) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADG'};
SELECT AVG(CAST(numeric_col AS FLOAT)) FROM table1 DATASOURCE_TYPE = 'ADQM';
SELECT AVG(numeric_col) FROM table1 DATASOURCE_TYPE = 'ADG';
* Нет неявного преобразования из целочисленного типа
** Результат возвращается по каждому шарду

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

Функции в SQL+ запросах

COALESCE: INSERT SELECT

COALESCE: ADB, ADP
INSERT INTO table2 (id, boolean_col) SELECT COALESCE(boolean_col,true) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
COALESCE: ADB, ADQM, ADP
INSERT INTO table2 (id, int32_col) SELECT COALESCE(int32_col,10) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table2 (id, int_col) SELECT COALESCE(int_col,-20) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table2 (id, bigint_col) SELECT COALESCE(bigint_col,-20) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table2 (id, float_col) SELECT COALESCE(float_col,10.5) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table2 (id, double_col) SELECT COALESCE(double_col,-0.5) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table2 (id, char10_col) SELECT COALESCE(char10_col,'0123456789') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table2 (id, varchar_col) SELECT COALESCE(varchar_col,'0123456789') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table2 (id, uuid_col) SELECT COALESCE(uuid_col,'0123456789') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
INSERT INTO table2 (id, link_col) SELECT COALESCE(link_col,'0123456789') FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP' | 'ADQM'};
COALESCE: ADB, ADP
INSERT INTO table2 (id, date_col) SELECT COALESCE(date_col,CAST('2001-02-03' AS DATE)) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
INSERT INTO table2 (id, time_col) SELECT COALESCE(time_col,CAST('12:12:12' AS TIME)) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};
INSERT INTO table2 (id, timestamp_col) SELECT COALESCE(timestamp_col,CAST('2001-02-03 12:12:12' AS TIMESTAMP)) FROM table1 DATASOURCE_TYPE = {'ADB' | 'ADP'};