При разработке отчетов по складским остаткам в системе компоновки данных (СКД) часто возникает задача сопоставить физический остаток товара и зарезервированное количество, чтобы вычислить свободный остаток. Нередко разработчики объединяют данные из двух регистров через конструкцию ОБЪЕДИНИТЬ ВСЕ во вложенном запросе и вычисляют разность «на лету». Однако при установке пользовательского отбора (например, СвободныйОстаток > 0) итоговые данные искажаются: резервы перестают вычитаться или отображаются неверно. Разберем причины такого поведения платформы и рассмотрим правильные варианты реализации отчета.
Рассмотрим типичный пример запроса, приводящий к ошибке. Разработчик объединяет таблицы остатков партий и резервов через вложенный запрос:
ВЫБРАТЬ
ВложенныйЗапрос.Номенклатура КАК Номенклатура,
ВложенныйЗапрос.Остаток КАК Остаток,
ВложенныйЗапрос.Стоимость КАК Стоимость,
ВложенныйЗапрос.Резерв КАК Резерв,
ВложенныйЗапрос.Остаток - ВложенныйЗапрос.Резерв КАК СвободныйОстаток
ИЗ
(ВЫБРАТЬ
ПартииТоваровОстатки.Номенклатура КАК Номенклатура,
ЕСТЬNULL(ПартииТоваровОстатки.КоличествоОстаток, 0) КАК Остаток,
ЕСТЬNULL(ПартииТоваровОстатки.СтоимостьОстаток, 0) КАК Стоимость,
0 КАК Резерв
ИЗ
РегистрНакопления.ПартииТоваров.Остатки КАК ПартииТоваровОстатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
РезервыОстатки.Номенклатура,
0,
0,
ЕСТЬNULL(РезервыОстатки.КоличествоОстаток, 0)
ИЗ
РегистрНакопления.Резервы.Остатки КАК РезервыОстатки) КАК ВложенныйЗапрос
Выясним причину некорректного поведения при наложении отбора. По умолчанию СКД стремится оптимизировать исполняемый SQL-запрос. Когда пользователь устанавливает в настройках отчета условие СвободныйОстаток > 0, компоновщик данных переносит это условие в секцию ГДЕ (WHERE) результирующего запроса к базе данных, применяя его к каждой отдельной строке выборки до расчета итогов:
(Остаток - 0) > 0 возвращает результат Истина, поэтому данные по физическому остатку попадают в выборку.(0 - Резерв) > 0 возвращает Ложь, поскольку резерв всегда положителен, а -Резерв меньше либо равен нулю. В результате СУБД полностью отсекает строки с резервами еще на этапе чтения данных.В итоге в отчет попадают только физические остатки, а резервы обнуляются. Для отладки подобных ситуаций рекомендуется использовать Консоль системы компоновки данных: она позволяет сформировать и изучить реальный текст запроса, который платформа генерирует с учетом установленных пользователем отборов и группировок.
Разберем по шагам самый простой и надежный способ исправления запроса. Чтобы условие отбора не отсекало отдельные строки объединения, необходимо сгруппировать записи по измерениям и просуммировать числовые поля на уровне основного запроса.
Обратите внимание: функция ЕСТЬNULL внутри веток ОБЪЕДИНИТЬ ВСЕ является избыточной, так как виртуальные таблицы остатков возвращают фактические числа. Гораздо важнее применить группировку во внешнем запросе:
ВЫБРАТЬ
ВложенныйЗапрос.Номенклатура КАК Номенклатура,
СУММА(ВложенныйЗапрос.Остаток) КАК Остаток,
СУММА(ВложенныйЗапрос.Стоимость) КАК Стоимость,
СУММА(ВложенныйЗапрос.Резерв) КАК Резерв,
СУММА(ВложенныйЗапрос.Остаток) - СУММА(ВложенныйЗапрос.Резерв) КАК СвободныйОстаток
ИЗ
(ВЫБРАТЬ
ПартииТоваровОстатки.Номенклатура КАК Номенклатура,
ПартииТоваровОстатки.КоличествоОстаток КАК Остаток,
ПартииТоваровОстатки.СтоимостьОстаток КАК Стоимость,
0 КАК Резерв
ИЗ
РегистрНакопления.ПартииТоваров.Остатки(, ) КАК ПартииТоваровОстатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
РезервыОстатки.Номенклатура,
0,
0,
РезервыОстатки.КоличествоОстаток
ИЗ
РегистрНакопления.Резервы.Остатки(, ) КАК РезервыОстатки) КАК ВложенныйЗапрос
СГРУППИРОВАТЬ ПО
ВложенныйЗапрос.Номенклатура
Теперь каждая номенклатурная позиция представлена в выборке ровно одной строкой, где одновременно присутствуют и свернутый остаток, и свернутый резерв. Условие отбора будет корректно применяться к уже вычисленной разности.
Проанализируем архитектурно правильный подход, заложенный в механизмы СКД. Вместо вычисления свободного остатка в тексте запроса оставим в наборе данных только исходные поля, а математические расчеты и отборы перенесем на уровень схемы компоновки:
Номенклатура, Остаток, Стоимость, Резерв без поля СвободныйОстаток.Для поля Остаток: Сумма(Остаток)
Для поля Резерв: Сумма(Резерв)
Для поля СвободныйОстаток: Сумма(Остаток) - Сумма(Резерв)
СвободныйОстаток на вкладке «Вычисляемые поля» или сразу как ресурс.Когда отбор накладывается на поле-ресурс, СКД формирует условие в секции ИМЕЮЩИЕ (HAVING) после выполнения группировки либо применяет постобработку. Это гарантирует сохранение корректных значений исходных показателей.
Посмотрим на альтернативный вариант построения запроса с использованием соединения вместо объединения. Если связать две таблицы по номенклатуре, обе части данных попадут в одну строку:
ВЫБРАТЬ
ЕСТЬNULL(ПартииТоваровОстатки.Номенклатура, РезервыОстатки.Номенклатура) КАК Номенклатура,
ЕСТЬNULL(ПартииТоваровОстатки.КоличествоОстаток, 0) КАК Остаток,
ЕСТЬNULL(ПартииТоваровОстатки.СтоимостьОстаток, 0) КАК Стоимость,
ЕСТЬNULL(РезервыОстатки.КоличествоОстаток, 0) КАК Резерв,
ЕСТЬNULL(ПартииТоваровОстатки.КоличествоОстаток, 0) - ЕСТЬNULL(РезервыОстатки.КоличествоОстаток, 0) КАК СвободныйОстаток
ИЗ
РегистрНакопления.ПартииТоваров.Остатки КАК ПартииТоваровОстатки
ПОЛНОЕ СОЕДИНЕНИЕ РегистрНакопления.Резервы.Остатки КАК РезервыОстатки
ПО ПартииТоваровОстатки.Номенклатура = РезервыОстатки.Номенклатура
В этом случае в каждой строке сразу присутствуют оба показателя, а пропущенные значения замещаются нулями благодаря функции ЕСТЬNULL. Отбор по свободному остатку сработает корректно даже без явной группировки.
Рассмотрим еще один мощный инструмент платформы — использование нескольких наборов данных внутри одной схемы компоновки:
ПартииТоваров.Остатки.Резервы.Остатки.Номенклатура.ЕстьNull(Сумма(Остаток), 0) - ЕстьNull(Сумма(Резерв), 0).СКД самостоятельно выполнит параллельные выборки, соединит полученные потоки данных во временных структурах и корректно рассчитает итоги с учетом установленных фильтров.