Почему запрос не выводит остатки по всем товарам в 1С и как это исправить?

← На главную

Разработчики в среде «1С:Предприятие» регулярно сталкиваются с ситуацией, когда отчет или программный алгоритм не выводит остатки по части номенклатурных позиций. На первый взгляд запрос кажется написанным абсолютно правильно: передаются нужные параметры, создается временная таблица товаров, накладываются индексы и отборы. Однако на выходе часть позиций просто «исчезает» из результирующего набора данных.

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

---

Анализ исходной проблемы и архитектурный дефект запроса

Рассмотрим исходный запрос, с которым возникли сложности у автора темы. В нем выполняется выборка данных напрямую из виртуальной таблицы остатков регистра накопления РегистрНакопления.ТоварыОрганизацийБУ.Остатки:


ВЫБРАТЬ
    ТоварыОрганизацийОстатки.Товар,
    ТоварыОрганизацийОстатки.ИсточникПроисхождения,
    ТоварыОрганизацийОстатки.КоличествоОстаток
ИЗ
    РегистрНакопления.ТоварыОрганизацийБУ.Остатки(
            &ДатаОстатка,
            Организация = &Организация
                И Подразделение = &Подразделение
                И (Товар, ИсточникПроисхождения) В
                    (ВЫБРАТЬ
                        Вт_Товары.Товар,
                        Вт_Товары.ИсточникПроисхождения
                    ИЗ
                        Вт_Товары)) КАК ТоварыОрганизацийОстатки

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

Программа обращается к СУБД и запрашивает остатки по товарам, которые переданы во временную таблицу Вт_Товары.
Если по какому-то товару из списка на указанную дату в регистре нет остатка (он равен нулю или движения по этому товару вообще никогда не производились), СУБД просто не вернет строку для этой позиции.
Поскольку запрос выбирает данные непосредственно «ИЗ» таблицы остатков, отсутствующие позиции физически не могут попасть в итоговый результат. Разработчик ожидает увидеть строчку товара с нулевым количеством, а получает пустой набор данных по этой позиции.

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

---

Решение 1: Переход на ЛЕВОЕ СОЕДИНЕНИЕ для вывода нулевых остатков

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

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


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

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

---

Решение 2: Тонкости работы с границей периода (ВидГраницы)

Вторая скрытая проблема в коде автора кроется в строке установки параметров запроса:

Запрос.УстановитьПараметр("ДатаОстатка", Новый Граница(Реквизиты.Период));

Выясним причину, почему это может приводить к потере актуальных движений по регистру. При создании объекта Граница без явного указания второго параметра платформа «1С:Предприятие» по умолчанию применяет значение ВидГраницы.Исключая.

Что это означает на практике? Если в качестве периода передается момент времени конкретного документа или граница дня (например, конец дня), то все приходные или расходные движения, совершенные ровно в эту секунду, не будут учтены при расчете остатков. СУБД рассчитает остатки «накануне» этой секунды.

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


Запрос.УстановитьПараметр("ДатаОстатка", Новый Граница(Реквизиты.Период, ВидГраницы.Включая));

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

---

Решение 3: Оптимизация производительности СУБД (Уход от отборов по кортежам)

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

И (Товар, ИсточникПроисхождения) В (ВЫБРАТЬ Вт_Товары.Товар, Вт_Товары.ИсточникПроисхождения ИЗ Вт_Товары)

Проанализируем ситуацию с точки зрения СУБД. Конструкция проверки вхождения по нескольким полям (так называемый отбор по кортежу) внутри параметров виртуальных таблиц крайне негативно сказывается на производительности. Большинство современных СУБД (MS SQL Server, PostgreSQL) не способны эффективно построить план выполнения для подобных условий. Вместо быстрого поиска по существующим индексам (Index Seek) СУБД переходит к полному сканированию таблиц (Table Scan / Index Scan), что вызывает колоссальные задержки при росте базы данных.

Как поступить правильно? Стандарты разработки «1С» рекомендуют накладывать первичный жесткий отбор в параметрах виртуальной таблицы только по одному, наиболее селективному и обязательно проиндексированному полю (в нашем случае это Товар), а полное сопоставление по второму признаку (ИсточникПроисхождения) выносить во внешнее условие связи (ПО) при левом соединении.

Давайте посмотрим, как будет выглядеть оптимальная фильтрация в параметрах виртуальной таблицы:


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

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

---

Решение 4: Контроль типов при передаче Таблицы Значений в запрос

Еще одна классическая ошибка, о которой часто забывают начинающие разработчики, связана с передачей таблицы значений в качестве параметра:

Запрос.УстановитьПараметр("ТаблицаТовары", Параметры.ТаблицаТовары);

Для того чтобы платформа «1С:Предприятие» могла успешно преобразовать таблицу значений во временную таблицу SQL, все колонки передаваемой таблицы должны иметь строго определенные типы данных. Если при создании таблицы значений типы колонок не были явно заданы (остался тип по умолчанию Произвольный), запрос упадет с критической ошибкой: «Тип не может быть выбран в запросе».

Перед передачей таблицы значений в запрос всегда проверяйте программный код ее инициализации. Создание колонок должно выполняться с явным указанием типов:


МассивТиповНоменклатура = Новый Массив;
МассивТиповНоменклатура.Добавить(Тип("СправочникСсылка.Номенклатура"));
ОписаниеТиповНоменклатура = Новый ОписаниеТипов(МассивТиповНоменклатура);

ТаблицаТовары.Колонки.Добавить("Товар", ОписаниеТиповНоменклатура);

---

Итоговый вариант оптимизированного и корректного запроса

Объединим все озвученные рекомендации в один целостный, производительный и отказоустойчивый алгоритм. Рассмотрим финальный код получения остатков:


// Шаг 1. Корректная установка параметров с учетом границы времени
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("ТаблицаТовары", Параметры.ТаблицаТовары);
Запрос.УстановитьПараметр("ДатаОстатка", Новый Граница(Реквизиты.Период, ВидГраницы.Включая));    
Запрос.УстановитьПараметр("Организация", Реквизиты.Организация);
Запрос.УстановитьПараметр("Подразделение", Реквизиты.Подразделение);

// Шаг 2. Оптимизированный текст запроса
Запрос.Текст =     
"ВЫБРАТЬ
|    ТаблицаТовары.Товар КАК Товар,
|    ТаблицаТовары.ИсточникПроисхождения КАК ИсточникПроисхождения
|ПОМЕСТИТЬ Вт_Товары
|ИЗ
|    &ТаблицаТовары КАК ТаблицаТовары
|
|ИНДЕКСИРОВАТЬ ПО
|    Товар,
|    ИсточникПроисхождения
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
|    Вт_Товары.Товар КАК Товар,
|    Вт_Товары.ИсточникПроисхождения КАК ИсточникПроисхождения,
|    ЕСТЬNULL(ТоварыОрганизацийОстатки.КоличествоОстаток, 0) КАК КоличествоОстаток
|ИЗ
|    Вт_Товары КАК Вт_Товары
|    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизацийБУ.Остатки(
|            &ДатаОстатка,
|            Организация = &Организация
|                И Подразделение = &Подразделение
|                И Товар В (ВЫБРАТЬ Вт_Товары.Товар ИЗ Вт_Товары)
|            ) КАК ТоварыОрганизацийОстатки
|    ПО Вт_Товары.Товар = ТоварыОрганизацийОстатки.Товар
|        И Вт_Товары.ИсточникПроисхождения = ТоварыОрганизацийОстатки.ИсточникПроисхождения";

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

Применив данную структуру, мы полностью защитили себя от потери строк с нулевыми остатками, гарантировали корректный учет движений «секунда в секунду» благодаря правильной границе периода и обеспечили оптимальный план выполнения запроса на уровне СУБД.