Почему ЕСТЬNULL в запросе 1С не возвращает 0 при отсутствии остатков и как это исправить?

← На главную

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

Выясним причину такого поведения платформы 1С и разберем по шагам надежные способы решения этой задачи.

В чем причина: физический смысл отсутствия записи и работа ЕСТЬNULL

Проанализируем механику работы базы данных и виртуальных таблиц 1С. Здесь важно понимать два ключевых нюанса:

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

Также обратим внимание на критическую ошибку в плане производительности: наложение фильтра по измерению в секции ГДЕ вместо параметров виртуальной таблицы. Если написать ГДЕ ТоварыНаСкладахОстатки.Номенклатура = &Номенклатура, система сначала рассчитает остатки по всем позициям склада и лишь затем применит фильтр. Условия всегда необходимо передавать во второй параметр виртуальной таблицы.

Рассмотрим рабочие варианты, позволяющие получить строку с нулевым значением.

Вариант 1. Использование агрегатной функции СУММА без группировки

Если нам необходимо получить остаток по одной конкретной позиции и вернуть только число, разберем изящный прием языка запросов 1С: применение функции СУММА без секции СГРУППИРОВАТЬ ПО.

По стандарту языка SQL агрегатная функция (такая как СУММА), примененная ко всему набору строк без группировки, всегда возвращает ровно одну строку. Если исходная выборка пуста, агрегат возвращает значение NULL. И вот теперь функция ЕСТЬNULL успешно перехватывает этот NULL и превращает его в 0.

Посмотрим на пример правильного запроса:


ВЫБРАТЬ
    ЕСТЬNULL(СУММА(ТоварыНаСкладахОстатки.КоличествоОстаток), 0) КАК КоличествоОстаток
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(, Склад В (&Склад) И Номенклатура = &Номенклатура) КАК ТоварыНаСкладахОстатки

Важные особенности данного метода:

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


ВЫБРАТЬ
    &Номенклатура КАК Номенклатура,
    ЕСТЬNULL(СУММА(ТоварыНаСкладахОстатки.КоличествоОстаток), 0) КАК КоличествоОстаток
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(, Склад В (&Склад) И Номенклатура = &Номенклатура) КАК ТоварыНаСкладахОстатки

Вариант 2. Левое соединение со справочником или временной таблицей

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

Разберем по шагам, как это работает: основной таблицей выступает перечень нужных позиций (справочник или временная таблица), а к ней слева присоединяется виртуальная таблица остатков. Поскольку левое соединение сохраняет все строки левой таблицы, для позиций без остатка поле регистра примет значение NULL, которое функция ЕСТЬNULL превратит в 0.

Посмотрим на текст запроса:


ВЫБРАТЬ
    СпрНоменклатура.Ссылка КАК Номенклатура
ПОМЕСТИТЬ ВтНоменклатура
ИЗ
    Справочник.Номенклатура КАК СпрНоменклатура
ГДЕ
    СпрНоменклатура.Ссылка = &Номенклатура
;

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

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

Вариант 3. Обработка отсутствия данных во встроенном языке (BSL)

В реальной разработке для получения одиночного остатка зачастую эффективнее не усложнять запрос соединениями и агрегатами, а оставить его максимально простым и перенести проверку в программный код.

Проанализируем пример кода на языке 1С:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	ТоварыНаСкладахОстатки.КоличествоОстаток КАК КоличествоОстаток
    |ИЗ
    |	РегистрНакопления.ТоварыНаСкладах.Остатки(, Склад = &Склад И Номенклатура = &Номенклатура) КАК ТоварыНаСкладахОстатки";

Запрос.УстановитьПараметр("Склад", ВыбранныйСклад);
Запрос.УстановитьПараметр("Номенклатура", ВыбраннаяНоменклатура);

РезультатЗапроса = Запрос.Выполнить();

Если РезультатЗапроса.Пустой() Тогда
    ОстатокТовара = 0;
Иначе
    Выборка = РезультатЗапроса.Выбрать();
    Выборка.Следующий();
    ОстатокТовара = Выборка.КоличествоОстаток;
КонецЕсли;

Также можно использовать компактную конструкцию обхода выборки:


Выборка = Запрос.Выполнить().Выбрать();
ОстатокТовара = ?(Выборка.Следующий(), Выборка.КоличествоОстаток, 0);

Этот подход гарантирует высокую скорость работы с базой данных, исключает лишние операции соединения на уровне СУБД и делает код легко читаемым для других специалистов.