SQL запросы к OData источникам¶

Начиная с версии 1.32 в моделях данных AW BI появилась возможность создаться SQL-объекты для OData источников.

Синтаксис SQL для таких источников поддерживается с существенными ограничениями. Например, не допускается указание агрегатных функций и группировки данных, DISTINCT и ряда других секций команды SELECT.

Синтаксис SELECT¶

В коннекторе поддерживается следующий вид инструкции SELECT:

SELECT 
    <список столбцов> 
FROM <таблица> 
[WHERE <критерий выборки>] 
[ORDER BY <список выражений> [ASC|DESC]] 
[LIMIT <максимальное количество записей>]

В списке столбцов допускается применение специального *-символа SQL (asterisk), заменяющего перечисление всех имеющихся в таблице столбцов. Использование псевдонимов не допускается. Предшествовать имени поля может только действительное имя таблицы, из которой происходит выборка. Например, для указания поля ИНН таблицы C_Контрагенты будут корректны обе формы записи: C_Контрагенты.ИНН или ИНН.

Ключевые слова в инструкции SELECT могут быть прописаны в произвольном регистре, но имена столбцов источника должны быть переданы с сохранением регистра.

Константы¶

Логические константы True и False могут быть написаны в произвольном регистре. Например:

SELECT * FROM C_Банки WHERE TRUE or False

Константы даты/времени указываются в формате DATETIME'YYYY-MM-DDThh:mm:sc', т.е. в начале записи указывается префикс DATETIME, после которого в одинарных кавычках указывается время в формате ISO 8601-1:2019 с гранулярностью до секунды, без часового пояса (стандарт OData3 не предусматривает поддержку временной зоны). Например:

SELECT * FROM C_Встречи WHERE Начало < DATETIME'2024-06-20T00:00:00'

Строковые константы должны быть заключены в одинарные кавычки. Например:

SELECT * FROM C_Встречи WHERE Описание LIKE 'Анализ%'

Стандарт OData включает тип данных Edm.Guid, однако в AW он приводится к строковому типу string. Однако источники OData не позволяют напрямую сравнивать значения Edm.Guid и Edm.String. Тем не менее в AW версий 1.34 и выше предусматриватеся возможность записи константы, соответствующей типу Edm.Guid в формате GUID'<XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX>'. Например:

SELECT 
    Ref_Key 
FROM 
    C_Встречи 
WHERE 
    Owner_Key != GUID'00000000-0000-0000-0000-000000000000'

Операторы и функции¶

Арифметические¶

Названии	Описание	Есть в OData3	Есть в 1С
+	Сумма	+	+
-	Разность	+	+
*	Произведение	+	+
/	Деление	+	+
%	Взятие остатка от целочисленного деления	+	-

Математические¶

Названии	Описание	Есть в OData	Есть в 1С
`ROUND(n)`	Округление до ближайшего целого	+	+
`FLOOR(n)`	Округление отбрасыванием дробной части	+	-
`CEIL(n)` `CEILING(n)`	Округление до ближайшего превосходящего или равного целого	+	-

Строковые¶

Названии	Описание	Есть в OData3	Есть в 1С
`LENGTH(s)`	Длина строки	+	-
`TRIM(s)`	Удаление пробелов в начале и конце строки	+	-
`LOWER(s)`	Приведение символов строки к нижнему регистру	+	-
`UPPER(s)`	Приведение символов строки к верхнему регистру	+	-
`CONCAT(s1,s2, ...)`	Конкатенация списка строк	+	+
`SUBSTRING(s,n)` `SUBSTRING(s,n,m)`	Выделение построки из заданный строки, начиная с `n`-го символа. Если указано три параметра, то последний ограничивает длину результирующей строки	+	+
`POSITION(s1,s)`	Позиция (индекс первого вхождения) подстроки `s1` в строке `s`	+	-
`LIKE`	Сравнение строк по шаблону	частично (*)	+

(*) Оператор LIKE не поддерживается стандартом OData3, однако средствами коннектора OData реализована возможность его использования с ограничениями. Ключевой символ % может присутствовать только в начале или конце шаблона. Например:

s LIKE '%спорт' - истинно, если строка заканчивается на "спорт"
s LIKE 'спорт%' - истинно, если строка начинается на "спорт"
s LIKE '%спорт%' - истинно, если строка содержит "спорт"

Логические¶

Названии	Описание	Есть в OData	Есть в 1С
`=`, `!=`, `<>`, `>`, `>=`, `<`, `<=`, `AND`, `OR`, `NOT`	Соответствуют стандартным логическим операторам, семантика и приоритет стандартные	+	+
`IS NULL`, `IS NOT NULL`	Сравнение значения с `NULL`	+	неизвестно (**)

(**) IS NULL / IS NOT NULL поддерживаются реализованным функционалом коннектором OData и успешно работает с источниками OData v3, однако доступный тестовый стенд с источником 1C при сравнивании с NULL возвращает ошибку.

Примеры допустимых SELECT инструкций¶

SELECT 
    * 
FROM 
    C_Контрагенты_ИсторияНаименований 
WHERE 
    Период <= DATETIME'2022-01-01T00:00:00' 
LIMIT 
    10

SELECT 
    ИНН, 
    НаименованиеПолное 
FROM 
    C_Контрагенты 
WHERE 
    SUBSTRING(НаименованиеПолное,5,3) = 'ТК' 
ORDER BY 
    SUBSTRING(НаименованиеПолное,5)