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