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