Почему запрос к виртуальной таблице остатков регистра накопления возвращает пустой результат и как это обойти?

← На главную

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

Анализ проблемы: почему запрос возвращает пустой результат

Рассмотрим типовую ситуацию. Выполняется запрос к виртуальной таблице остатков:


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

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

Товар 1 — остаток 1,000
Товар 2 — остаток -1,000

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

Это штатное поведение платформы 1С:Предприятие, обусловленное спецификой работы виртуальных таблиц остатков:

Автоматическая агрегация по выбранным полям: Виртуальная таблица Остатки выполняет расчет итогов только по тем измерениям регистра, которые явно задействованы в запросе (в секциях ВЫБРАТЬ, ГДЕ, СГРУППИРОВАТЬ ПО или в условиях соединений). Если измерение Номенклатура не указано в запросе, расчет остатков выполняется по более крупному разрезу — только по измерению Документ.
Отсечение нулевых итогов платформой: При трансляции запроса в SQL платформа автоматически добавляет условие отсечения нулевых остатков (аналог конструкции HAVING SUM(КоличествоОстаток) <> 0 в СУБД). При суммировании +1 и -1 общий остаток по документу равен 0. Поскольку итоговый остаток в разрезе документа нулевой, платформа исключает эту строку из выборки виртуальной таблицы.

Разберем основные варианты решения задачи в зависимости от контекста использования (в обычном запросе или в динамическом списке).

Вариант 1. Использование вложенного запроса

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

Посмотрим на пример кода с вложенным запросом:


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

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

Вариант 2. Обращение к измерению в секции ГДЕ

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


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

Так как поле Номенклатура присутствует в условии ГДЕ, платформа рассчитывает итоги с учетом этого измерения, и схлопывания противоположных остатков до нуля на этапе виртуальной таблицы не происходит.

Вариант 3. Настройка динамического списка формы

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

В тексте запроса динамического списка выберем все необходимые измерения регистра (включая Номенклатура и Склад).
В дереве реквизитов формы раскроем реквизит Список, найдем измерение Номенклатура и в его свойствах установим флажок Использовать всегда.
На саму форму выведем только те колонки, которые требуются пользователю (например, только Документ).

Флаг Использовать всегда гарантирует, что оптимизатор динамического списка не исключит измерение из итогового SQL-запроса при скрытии колонки, и данные будут рассчитываться корректно.

Вариант 4. Запрос к реальной таблице движений

Если цель задачи — получить список документов, по которым вообще были движения, независимо от того, закрылся остаток в ноль или нет, рекомендуется обращаться не к виртуальной таблице остатков, а к физической таблице движений регистра:


ВЫБРАТЬ
    ТаблицаДвижений.Документ КАК Документ,
    СУММА(ВЫБОР
            КОГДА ТаблицаДвижений.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)
                ТОГДА ТаблицаДвижений.Количество
            ИНАЧЕ -ТаблицаДвижений.Количество
        КОНЕЦ) КАК КоличествоОстаток
ИЗ
    РегистрНакопления.ТоварыКВыдачеСоСклада КАК ТаблицаДвижений
ГДЕ
    ТаблицаДвижений.Период <= &Период
СГРУППИРОВАТЬ ПО
    ТаблицаДвижений.Документ

Обращение к реальной таблице движений исключает фильтрацию HAVING SUM <> 0, возвращая все документы с рассчитанным сальдо.