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

← На главную

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

Выясним причину ошибки

Рассмотрим исходный запрос, в котором пропадают товары без цен:


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

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

Если для товара цена не задана, все поля правой таблицы (включая ЦеныНоменклатуры.ВидЦены) принимают значение NULL. При проверке условия NULL = &ВыбВид СУБД возвращает результат ЛОЖЬ, из-за чего вся строка с товаром отбрасывается. В итоге левое соединение фактически превращается во внутреннее.

Способ 1. Перенос фильтра в условие соединения (ПО)

Первый простой вариант исправления — перенести отбор по виду цены из секции ГДЕ непосредственно в условие связи ПО. В этом случае фильтрация правой таблицы происходит в момент соединения, и отсутствие цены не приведет к удалению строки с остатком.


ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Дата1) КАК ЦеныНоменклатуры
ПО ТоварыНаСкладах.Номенклатура = ЦеныНоменклатуры.Номенклатура
	И ЦеныНоменклатуры.ВидЦены = &ВыбВид

Способ 2. Передача отбора в параметры виртуальной таблицы (рекомендуется)

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


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

Дополнительные рекомендации по оптимизации запроса

Помимо исправления типа соединения, обратим внимание на важные детали, влияющие на скорость выполнения и корректность данных:

Фильтрация склада в параметрах виртуальной таблицы: Условие ТоварыНаСкладах.Склад = &ВыбСклад из блока ГДЕ необходимо перенести в параметры таблицы остатков: Остатки(&Дата1, Склад = &ВыбСклад). Иначе платформа сначала рассчитает остатки по всей базе данных, а затем отфильтрует результат.
Обработка значений NULL при расчете суммы: Если цена отсутствует, результат выражения NULL * КоличествоОстаток равен NULL. Чтобы получить корректную нулевую сумму, обернем цену в функцию ЕСТЬNULL(ЦеныНоменклатуры.Цена, 0).
Учет характеристик номенклатуры: В конфигурации «1С:Розница 2.2» учет остатков и цен часто ведется в разрезе характеристик. Если на предприятии используется учет по характеристикам, связывание только по номенклатуре приведет к задвоению строк. В таком случае связь должна быть двойной:
```
ПО ТоварыНаСкладах.Номенклатура = ЦеныНоменклатуры.Номенклатура
	И ТоварыНаСкладах.Характеристика = ЦеныНоменклатуры.Характеристика
```
Удаление избыточных группировок: Одновременное использование ВЫБРАТЬ РАЗЛИЧНЫЕ и СГРУППИРОВАТЬ ПО без агрегатных функций (таких как СУММА) создает лишнюю нагрузку на сервер БД. При правильной настройке условий связи группировка не требуется.