При разработке отчетов и сложных выборок в системе 1С:Предприятие программисты часто сталкиваются с ситуацией, когда арифметические операции в запросе (сложение, вычитание) возвращают пустой результат, хотя данные в исходных таблицах точно есть. Особенно часто это проявляется при объединении данных из разных источников, например, из регистров накопления и регистров сведений. В этой статье мы подробно разберем, почему возникает такая проблема, как работает логика NULL в языке запросов и как правильно составить запрос, чтобы данные не терялись.
Рассмотрим ситуацию, описанную пользователем. В запросе используются две временные таблицы: ВТ_Продажи (данные об оборотах товаров) и ВТ_Удаленные (данные из независимого регистра сведений). Автор пытается соединить их через ЛЕВОЕ СОЕДИНЕНИЕ по полю Номенклатура.
Проанализируем ключевой фрагмент кода:
ВЫБРАТЬ
ВТ_Продажи.Номенклатура,
ВТ_Продажи.КоличествоПродаж,
ВТ_Удаленные.КоличествоУдаленных,
ВТ_Продажи.КоличествоПродаж + ВТ_Удаленные.КоличествоУдаленных КАК Всего
ИЗ
ВТ_Продажи КАК ВТ_Продажи
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Удаленные КАК ВТ_Удаленные
ПО ВТ_Продажи.Номенклатура = ВТ_Удаленные.Номенклатура
Проблема заключается в том, что если для конкретной номенклатуры в таблице ВТ_Удаленные нет записи (товар не удалялся), то в результате левого соединения поле ВТ_Удаленные.КоличествоУдаленных принимает значение NULL. В языке SQL и в языке запросов 1С любое арифметическое действие с участием NULL (например, 10 + NULL) всегда возвращает NULL. Именно поэтому колонка "Всего" оказывается пустой для всех строк, где не было соответствия во второй таблице.
Самый простой и правильный способ решения — использование встроенной функции ЕСТЬNULL. Эта функция проверяет значение первого параметра, и если оно равно NULL, заменяет его на второй параметр (в нашем случае — на ноль).
Рассмотрим, как должен выглядеть исправленный фрагмент запроса:
ВЫБРАТЬ
ВТ_Продажи.Номенклатура,
ВТ_Продажи.КоличествоПродаж,
ЕСТЬNULL(ВТ_Удаленные.КоличествоУдаленных, 0) КАК КоличествоУдаленных,
ВТ_Продажи.КоличествоПродаж + ЕСТЬNULL(ВТ_Удаленные.КоличествоУдаленных, 0) КАК Всего
ИЗ
ВТ_Продажи КАК ВТ_Продажи
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Удаленные КАК ВТ_Удаленные
ПО ВТ_Продажи.Номенклатура = ВТ_Удаленные.Номенклатура
Теперь, если данных в регистре сведений нет, система подставит 0, и сложение выполнится корректно: 10 + 0 = 10. Обратите внимание, что оборачивать в ЕСТЬNULL нужно те поля, которые приходят из "правой" (присоединяемой) таблицы в ЛЕВОМ СОЕДИНЕНИИ.
Проанализируем архитектурный нюанс, который может привести к другой ошибке — дублированию данных. В исходном сообщении автор обращается к физической таблице регистра сведений РегистрСведений.УдаленныеТовары. Если один и тот же товар удалялся несколько раз за период, в этой таблице будет несколько записей для одной номенклатуры.
При левом соединении одна строка из таблицы продаж соединится с каждой строкой из регистра сведений. В итоге количество продаж "размножится". Чтобы этого избежать, данные во временной таблице нужно обязательно группировать.
Рассмотрим правильное формирование временной таблицы:
ВЫБРАТЬ
УдаленныеТовары.Номенклатура КАК Номенклатура,
СУММА(ЕСТЬNULL(УдаленныеТовары.Количество, 0)) КАК КоличествоУдаленных
ПОМЕСТИТЬ ВТ_Удаленные
ИЗ
РегистрСведений.УдаленныеТовары КАК УдаленныеТовары
ГДЕ
УдаленныеТовары.Период МЕЖДУ &Дата1 И &Дата2
СГРУППИРОВАТЬ ПО
УдаленныеТовары.Номенклатура
;
Выясним причину, почему это важно: без группировки ваш отчет может показать неверные цифры продаж, просто из-за того, что в техническом регистре оказалось несколько записей по одному товару.
Если мы используем ЛЕВОЕ СОЕДИНЕНИЕ от таблицы продаж, мы увидим только те товары, которые продавались. Если товар был удален, но продаж по нему не было, он не попадет в итоговую таблицу. Если задача стоит увидеть вообще все движения, нам потребуется ПОЛНОЕ СОЕДИНЕНИЕ.
Посмотрим на пример реализации такого запроса:
ВЫБРАТЬ
ЕСТЬNULL(ВТ_Продажи.Номенклатура, ВТ_Удаленные.Номенклатура) КАК Номенклатура,
ЕСТЬNULL(ВТ_Продажи.КоличествоПродаж, 0) КАК КоличествоПродаж,
ЕСТЬNULL(ВТ_Удаленные.КоличествоУдаленных, 0) КАК КоличествоУдаленных,
ЕСТЬNULL(ВТ_Продажи.КоличествоПродаж, 0) + ЕСТЬNULL(ВТ_Удаленные.КоличествоУдаленных, 0) КАК Всего
ИЗ
ВТ_Продажи КАК ВТ_Продажи
ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Удаленные КАК ВТ_Удаленные
ПО ВТ_Продажи.Номенклатура = ВТ_Удаленные.Номенклатура
В этом случае мы используем ЕСТЬNULL не только для ресурсов, но и для ключевого поля Номенклатура, чтобы получить значение из той таблицы, где оно заполнено.
Подводя итоги, сформулируем основные правила работы с неопределенными значениями в 1С:
ЛЕВОЕ СОЕДИНЕНИЕ, должно быть обернуто в ЕСТЬNULL, если оно участвует в расчетах или итогах.ЕСТЬNULL(Поле, ЗначениеЗамены) должен соответствовать типу поля. Для чисел это 0, для строк — пустая строка "", для ссылок — ЗНАЧЕНИЕ(Справочник.Имя.ПустаяСсылка).ЕСТЬNULL в условиях соединения (секция ПО) или фильтрации (секция ГДЕ), так как это блокирует использование индексов СУБД и замедляет запрос.NULL при расчете итогов, но в вычисляемых полях или выражениях ресурсов лучше все же явно прописывать ЕСТЬNULL для гарантии результата.Проанализировав ситуацию, мы видим, что понимание природы NULL — это фундаментальный навык для работы с базами данных. Использование ЕСТЬNULL превращает "неизвестность" в конкретное число 0, что позволяет корректно выполнять математические операции и строить достоверные отчеты.