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