Как сложить или получить разницу двух полей в запросе 1С и избежать вывода "Да/Нет" вместо чисел?

← На главную

При разработке отчетов и обработок в системе 1С:Предприятие программисты часто сталкиваются с необходимостью производить арифметические операции прямо в тексте запроса. Казалось бы, простая задача — вычесть из одного числового поля другое — иногда ставит в тупик, возвращая в результате логическое значение Да или Нет (Булево) вместо ожидаемой разности чисел. В рамках этой статьи мы подробно разберем, почему возникает такая ошибка, как ее исправить, а также рассмотрим важные правила оптимизации запросов при расчете остатков.

Выясняем причину: почему запрос возвращает логическое значение?

Рассмотрим ситуацию, с которой столкнулся автор темы. В запросе производилась выборка данных из временной таблицы и их левое соединение с регистром накопления. При расчете разницы между требуемым количеством и фактическим остатком в поле макета выводилось значение Да/Нет. Проанализируем фрагмент исходного кода запроса, содержащий ошибку:


ВЫБОР
    КОГДА СвободныеОстатки.ВНаличии > ТЗ.Количество
        ТОГДА 0
    ИНАЧЕ ТЗ.Разница = ТЗ.Количество - СвободныеОстатки.ВНаличии
КОНЕЦ КАК Разница

Обратим внимание на строку после ключевого слова ИНАЧЕ. Здесь написана следующая конструкция: ТЗ.Разница = ТЗ.Количество - СвободныеОстатки.ВНаличии. В традиционных языках программирования знак равенства используется для операции присваивания значения переменной. Однако в языке запросов 1С (который базируется на стандартах SQL) знак равенства = всегда выполняет функцию сравнения.

Таким образом, СУБД интерпретирует данное выражение не как запись результата вычитания в поле, а как логическое условие: "Равно ли значение поля ТЗ.Разница разности полей ТЗ.Количество и СвободныеОстатки.ВНаличии?". Результатом любого сравнения является логический тип данных — Булево (Истина или Ложь). Именно поэтому в итоговом табличном документе выводится "Да" (если равенство выполняется) или "Нет" (если равенство не выполняется).

Шаг 1. Исправляем арифметическое выражение

Для того чтобы вернуть числовую разницу, нам необходимо полностью убрать операцию сравнения и оставить только чистое математическое выражение. Перепишем конструкцию ВЫБОР правильным образом:


ВЫБОР
    КОГДА СвободныеОстатки.ВНаличии > ТЗ.Количество
        ТОГДА 0
    ИНАЧЕ ТЗ.Количество - СвободныеОстатки.ВНаличии
КОНЕЦ КАК Разница

Теперь система будет корректно вычислять разность двух чисел и возвращать числовой результат в поле Разница.

Шаг 2. Защита от неопределенности: обрабатываем значение NULL

При использовании левого соединения (ЛЕВОЕ СОЕДИНЕНИЕ) всегда существует вероятность, что для некоторой строки из левой таблицы (в нашем случае это ТЗ) не найдется соответствующей записи в правой таблице остатков. В этом случае СУБД вернет значение NULL во все поля правой таблицы.

Важно помнить критическое правило работы баз данных: любая арифметическая операция с участием NULL всегда возвращает NULL. Если в выражении ТЗ.Количество - СвободныеОстатки.ВНаличии значение остатка окажется равным NULL, результатом всего вычисления станет пустая ячейка (NULL). Чтобы избежать этой проблемы, необходимо использовать функцию ЕСТЬNULL, которая заменяет неопределенное значение на альтернативное (в данном случае на ноль):

ЕСТЬNULL(СвободныеОстатки.ВНаличии, 0)

Применим эту функцию в нашей конструкции ВЫБОР:


ВЫБОР
    КОГДА ЕСТЬNULL(СвободныеОстатки.ВНаличии, 0) > ТЗ.Количество
        ТОГДА 0
    ИНАЧЕ ТЗ.Количество - ЕСТЬNULL(СвободныеОстатки.ВНаличии, 0)
КОНЕЦ КАК Разница

Не путайте функцию ЕСТЬNULL(Выражение, ЗначениеЗамены) с логическим оператором проверки Выражение ЕСТЬ NULL. Функция работает значительно быстрее, лаконичнее и является стандартом разработки при выполнении расчетов в запросах 1С.

Шаг 3. Исправление архитектурной ошибки: переходим на виртуальные таблицы

В исходном запросе автора соединение происходило напрямую с физической таблицей регистра накопления:

ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СвободныеОстатки КАК СвободныеОстатки

Обращение к физической таблице остатков вместо виртуальной таблицы — это одна из самых серьезных ошибок при проектировании запросов в 1С. Разберем, к каким пагубным последствиям это приводит:

Дублирование строк и искажение данных: Физическая таблица содержит детальные записи всех движений (приходов и расходов), которые когда-либо совершались в системе. Если по номенклатуре было совершено много движений, левое соединение вернет множество строк вместо одной. Итоговые данные "размножатся", и отчет покажет неверные цифры.
Критическое падение производительности: База данных будет вынуждена сканировать весь объем записей движений и производить их группировку на лету, вместо того чтобы быстро прочитать уже готовые и рассчитанные итоги из встроенных таблиц остатков.

Для исправления этой архитектурной ошибки мы обязаны использовать виртуальную таблицу остатков Остатки. Кроме того, крайне рекомендуется накладывать фильтр по номенклатуре в параметрах этой виртуальной таблицы, чтобы база данных выбирала остатки только по тем позициям, которые присутствуют в нашей временной таблице:


ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СвободныеОстатки.Остатки(, Номенклатура В (ВЫБРАТЬ ТЗ.Номенклатура ИЗ ТЗ)) КАК СвободныеОстатки

Шаг 4. Оптимизация временных таблиц и группировки

Чтобы наш запрос работал максимально быстро даже на больших объемах данных, выполним еще несколько важных шагов по оптимизации:

Индексация временной таблицы: Поля, по которым происходит соединение таблиц (в нашем случае это Номенклатура), обязательно должны быть проиндексированы во временной таблице. Это позволит СУБД мгновенно сопоставлять строки без выполнения избыточного сканирования таблиц.
Группировка на этапе создания ВТ: Если во входящей таблице значений &Данные могут содержаться дублирующиеся строки по одной и той же номенклатуре, правильнее сгруппировать их и просуммировать количество еще на этапе создания временной таблицы ТЗ. Это существенно сократит объем данных, участвующих в последующем соединении.

Готовое оптимальное решение

С учетом всех разобранных ошибок и рекомендаций по оптимизации, итоговый пакет запросов примет следующий вид. Этот шаблон является оптимальным и полностью соответствует стандартам разработки фирмы "1С":


ВЫБРАТЬ
    Данные.Номенклатура КАК Номенклатура,
    Данные.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
    СУММА(Данные.Количество) КАК Количество
ПОМЕСТИТЬ ТЗ
ИЗ
    &Данные КАК Данные

СГРУППИРОВАТЬ ПО
    Данные.Номенклатура,
    Данные.ЕдиницаИзмерения

ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ТЗ.Номенклатура КАК Спецификация,
    ТЗ.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
    ТЗ.Количество КАК Количество,
    ЕСТЬNULL(СвободныеОстатки.ВНаличииОстаток, 0) КАК ВНаличии,
    ВЫБОР
        КОГДА ЕСТЬNULL(СвободныеОстатки.ВНаличииОстаток, 0) > ТЗ.Количество
            ТОГДА 0
        ИНАЧЕ ТЗ.Количество - ЕСТЬNULL(СвободныеОстатки.ВНаличииОстаток, 0)
    КОНЕЦ КАК Разница
ИЗ
    ТЗ КАК ТЗ
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СвободныеОстатки.Остатки(, Номенклатура В (ВЫБРАТЬ ТЗ.Номенклатура ИЗ ТЗ)) КАК СвободныеОстатки
        ПО ТЗ.Номенклатура = СвободныеОстатки.Номенклатура

Проанализировав данную конструкцию, можно заметить, что мы полностью избавились от избыточной группировки СГРУППИРОВАТЬ ПО во втором запросе пакета. Поскольку соединение теперь происходит с виртуальной таблицей остатков, которая гарантированно возвращает уникальные строки по ключевым полям, необходимость во вторичной группировке отпала, что существенно снижает общую нагрузку на сервер СУБД.