При разработке и доработке отчетов в системе 1С:Предприятие программисты часто сталкиваются с ситуацией, когда данные для вывода находятся в разных источниках. Например, правильные цены рассчитываются в одном алгоритме, а актуальные суммы движений берутся из регистров накопления. Пытаясь соединить эти данные, разработчики совершают типичные ошибки: используют объединение (ОБЪЕДИНИТЬ) вместо соединения (СОЕДИНЕНИЕ), передают в запрос нетипизированные таблицы значений или пытаются сопоставить строковые поля со ссылочными. В этой статье мы подробно разберем все эти проблемы и найдем оптимальные пути их решения.
Прежде чем перейти к практике, кратко разберем теорию, которая часто вызывает путаницу у начинающих разработчиков. Проанализируем два базовых механизма работы с множествами в SQL-запросах 1С:
ЛЕВОЕ СОЕДИНЕНИЕ) позволяет к строкам основной таблицы с суммами "пристыковать" соответствующие им цены из другой таблицы.---
Рассмотрим ситуацию автора темы. Он попытался выгрузить некоторую выборку цен в Таблицу Значений (ТЗ), передать ее в запрос в качестве параметра &ВыборкаЦен, поместить во временную таблицу ВТВыборкаЦены и соединить с данными о товарах. Однако система выдала ошибки несовместимости типов и пустой результат. Выясним причину этих ошибок.
Посмотрим на структуру временной таблицы ВТТовары в запросе автора:
ВЫБРАТЬ
ОтпускТоваровВОтделениеТовары.Ссылка КАК Ссылка,
ВЫРАЗИТЬ(ОтпускТоваровВОтделениеТовары.Номенклатура.Наименование КАК СТРОКА(150)) КАК Номенклатура
ПОМЕСТИТЬ ВТТовары
ИЗ
Документ.ОтпускТоваровВОтделение.Товары КАК ОтпускТоваровВОтделениеТовары
Обратите внимание на поле Номенклатура. Автор берет текстовое наименование номенклатуры (Номенклатура.Наименование), приводит его к строке длиной 150 символов и называет псевдонимом Номенклатура. Таким образом, поле ВТТовары.Номенклатура имеет тип Строка.
Теперь посмотрим на вторую таблицу ВТРегНакопления:
ВЫБРАТЬ
СебестоимостьТоваров.АналитикаУчетаНоменклатуры.Номенклатура КАК АналитикаУчетаНоменклатурыНоменклатура
ПОМЕСТИТЬ ВТРегНакопления
ИЗ
РегистрНакопления.СебестоимостьТоваров КАК СебестоимостьТоваров
Здесь поле АналитикаУчетаНоменклатурыНоменклатура извлекается напрямую из реквизитов и является ссылкой типа СправочникСсылка.Номенклатура.
В финальном запросе автор пытается соединить эти таблицы следующим образом:
ИЗ
ВТТОвары КАК ВТТОвары
ЛЕВОЕ СОЕДИНЕНИЕ ВТРегНакопления КАК ВТРегНакопления
ПО ВТТОвары.Номенклатура = ВТРегНакопления.АналитикаУчетаНоменклатурыНоменклатура
Критическая ошибка: в условии связи ПО происходит попытка сравнения строки (наименования номенклатуры) со ссылкой на элемент справочника. На уровне СУБД такое сравнение вызывает ошибку несовместимости типов данных (Incompatible types in Join), так как строка длиной 150 символов физически не может быть сопоставлена с бинарным UUID ссылки.
---
Чтобы исправить ошибку связи, нам необходимо переписать запрос так, чтобы ключевым полем связи выступала именно ссылка на справочник, а не строковое представление наименования. Строку наименования мы будем получать исключительно для вывода в отчет на самом последнем этапе.
Разберем по шагам правильную структуру пакета запросов:
Не преобразовывайте ссылку в строку на этапе формирования временной таблицы. Оставьте поле Номенклатура ссылочным типом:
ВЫБРАТЬ
ОтпускТоваровВОтделениеТовары.Ссылка КАК Ссылка,
ОтпускТоваровВОтделениеТовары.НомерСтроки КАК НомерСтроки,
ОтпускТоваровВОтделениеТовары.Номенклатура КАК Номенклатура, // Ссылка!
ОтпускТоваровВОтделениеТовары.Количество КАК Количество
ПОМЕСТИТЬ ВТТовары
ИЗ
Документ.ОтпускТоваровВОтделение.Товары КАК ОтпускТоваровВОтделениеТовары
ГДЕ
ОтпускТоваровВОтделениеТовары.Ссылка = &Ссылка
;
Здесь также выбираем ссылку на номенклатуру:
ВЫБРАТЬ
СебестоимостьТоваров.Регистратор КАК Регистратор,
ЕСТЬNULL(СебестоимостьТоваров.Стоимость, 0) КАК Стоимость,
СебестоимостьТоваров.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТРегНакопления
ИЗ
РегистрНакопления.СебестоимостьТоваров КАК СебестоимостьТоваров
ГДЕ
СебестоимостьТоваров.Регистратор = &Регистратор
;
Теперь мы можем безопасно связать таблицы по полям одинакового типа (СправочникСсылка.Номенклатура). Если нам требуется вывести именно строковое наименование, мы получим его через точку от ссылки на финальном этапе выборки. Платформа 1С автоматически оптимизирует такой запрос при обращении к СУБД:
ВЫБРАТЬ
ВТТовары.Ссылка КАК Ссылка,
ВТТовары.НомерСтроки КАК НомерСтроки,
ВТТовары.Номенклатура КАК НоменклатураСсылка,
ВТТовары.Номенклатура.Наименование КАК НоменклатураНаименование, // Получаем строку для вывода
ЕСТЬNULL(ВТРегНакопления.Стоимость, 0) КАК Сумма,
ВТТовары.Количество КАК Количество,
ЕСТЬNULL(ВТВыборкаЦены.Цена, 0) КАК Цена
ИЗ
ВТТовары КАК ВТТовары
ЛЕВОЕ СОЕДИНЕНИЕ ВТРегНакопления КАК ВТРегНакопления
ПО ВТТовары.Номенклатура = ВТРегНакопления.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ ВТВыборкаЦены КАК ВТВыборкаЦены
ПО ВТТовары.Номенклатура = ВТВыборкаЦен.Номенклатура
---
Проанализируем вторую проблему автора: внешняя временная таблица ВТВыборкаЦены, созданная на основе переданного параметра &ВыборкаЦен (Таблица Значений), возвращает пустой результат или вызывает ошибку несовместимых типов.
Выясним причину: когда мы передаем таблицу значений в запрос как параметр для помещения во временную таблицу, СУБД должна физически воссоздать структуру этой таблицы на сервере базы данных. SQL требует жесткого определения типов для каждого поля (размерность чисел, точность, максимальная длина строк). Если в коде 1С колонки таблицы значений были добавлены простым методом ТЗ.Колонки.Добавить("ИмяКолонки"), то тип этих колонок не определен (содержит тип Неопределено). СУБД не сможет обработать такую таблицу, что приведет к ошибкам выполнения запроса.
Рассмотрим, как правильно инициализировать и типизировать Таблицу Значений в коде BSL перед передачей в запрос:
// 1. Создаем описания типов для колонок таблицы
ОписаниеТипаНоменклатура = Новый ОписаниеТипов("СправочникСсылка.Номенклатура");
КвалификаторыЧисла = Новый КвалификаторыЧисла(15, 2);
ОписаниеТипаЧисло = Новый ОписаниеТипов("Число", , , КвалификаторыЧисла);
ОписаниеТипаСтрока = Новый ОписаниеТипов("Строка", , Новый КвалификаторыСтроки(150));
// 2. Инициализируем Таблицу Значений с жестко заданными типами
ТаблицаЦен = Новый ТаблицаЗначений;
ТаблицаЦен.Колонки.Добавить("Номенклатура", ОписаниеТипаНоменклатура);
ТаблицаЦен.Колонки.Добавить("Цена", ОписаниеТипаЧисло);
ТаблицаЦен.Колонки.Добавить("Сумма", ОписаниеТипаЧисло);
ТаблицаЦен.Колонки.Добавить("ТоварНаименование", ОписаниеТипаСтрока);
// 3. Заполняем Таблицу Значений данными из первой выборки
// (Пример заполнения в цикле или методом Скопировать/Выгрузить)
Для Каждого СтрокаВыборки Из ВыборкаИсточника Цикл
НоваяСтрока = ТаблицаЦен.Добавить();
НоваяСтрока.Номенклатура = СтрокаВыборки.Номенклатура;
НоваяСтрока.Цена = СтрокаВыборки.Цена;
НоваяСтрока.Сумма = СтрокаВыборки.Сумма;
НоваяСтрока.ТоварНаименование = СтрокаВыборки.Номенклатура.Наименование;
КонецЦикла;
// 4. Передаем готовую типизированную таблицу в запрос
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
// Обязательно указываем параметр как Таблицу Значений
Запрос.УстановитьПараметр("ВыборкаЦен", ТаблицаЦен);
После такой жесткой типизации СУБД без проблем создаст во временной базе данных (например, в tempdb на MS SQL Server) типизированную таблицу, построит необходимые индексы и выполнит быстрое левое соединение с основными данными отчета.
---
Иногда при соединении таблиц возникает ошибка: "Допускаются только поля совместимых типов. ЛЕВОЕ СОЕДИНЕНИЕ не может содержать поля неограниченной длины". В СУБД текстовые поля неограниченной длины (в 1С это строки длины 0, субтаблицы, описания) хранятся в специальных структурах данных (LOB - Large Objects), которые нельзя использовать в операциях сравнения =, группировки СГРУППИРОВАТЬ ПО или объединения ОБЪЕДИНИТЬ.
Если вам жизненно необходимо соединить таблицы по строковому представлению (хотя, как мы выяснили выше, лучше использовать ссылки), обязательно ограничьте длину строки с помощью функции ВЫРАЗИТЬ:
ВЫБРАТЬ
ВЫРАЗИТЬ(ВТТовары.НоменклатураНаименование КАК СТРОКА(150)) КАК НоменклатураНаименование
ПОМЕСТИТЬ ВТТоварыОграниченные
ИЗ
ВТТовары КАК ВТТовары
Функция ВЫРАЗИТЬ преобразует тип данных неограниченной строки SQL в фиксированный тип NVARCHAR(150), что позволяет использовать это поле в любых операциях соединения и сравнения.
---
Если отчет очень сложный, состоит из тысяч строк кода и переписывать исходные тяжелые SQL-запросы слишком рискованно, мы можем прибегнуть к быстрому программному соединению двух готовых Таблиц Значений непосредственно в коде BSL.
Чтобы такое соединение работало мгновенно даже на десятках тысяч строк, мы должны использовать индексацию таблиц. Поиск по индексированной колонке в 1С выполняется за константное время $O(1)$, в то время как обычный перебор занял бы квадратичное время $O(N^2)$.
Рассмотрим этот эффективный алгоритм:
// Предположим, у нас есть две выборки, выгруженные в Таблицы Значений
ТаблицаСуммы = РезультатЗапросаСуммы.Выгрузить(); // Есть правильные суммы, нет правильных цен
ТаблицаЦены = РезультатЗапросаЦены.Выгрузить(); // Есть правильные цены
// Шаг 1. Подготавливаем приемник: добавляем колонку для цены
ОписаниеЧисла = Новый ОписаниеТипов("Число", , , Новый КвалификаторыЧисла(15, 2));
ТаблицаСуммы.Колонки.Добавить("Цена", ОписаниеЧисла);
// Шаг 2. Создаем индекс по ключевому полю связи (Номенклатура) в таблице-источнике цен
ТаблицаЦены.Индексы.Добавить("Номенклатура");
// Шаг 3. Обходим основную таблицу и быстро сопоставляем данные
Для Каждого СтрокаДанных Из ТаблицаСуммы Цикл
// Поиск по индексированному полю выполняется моментально
НайденнаяСтрокаЦены = ТаблицаЦены.Найти(СтрокаДанных.Номенклатура, "Номенклатура");
Если НайденнаяСтрокаЦены <> Неопределено Тогда
// Заполняем цену из сопоставленной таблицы
СтрокаДанных.Цена = НайденнаяСтрокаЦены.Цена;
Иначе
// Если цена не найдена, можно установить дефолтное значение
СтрокаДанных.Цена = 0;
КонецЕсли;
КонецЦикла;
// Теперь ТаблицаСуммы содержит полностью готовый набор данных с правильными суммами и ценами.
// Ее можно передать в процессор вывода отчета.
---
Система компоновки данных (СКД) предоставляет нам встроенный декларативный механизм для связи различных наборов данных на уровне оперативной памяти. Если вы переведете свой отчет на СКД, вам больше не придется писать сложные запросы левого соединения или вручную сопоставлять таблицы в коде.
Проанализируем по шагам настройку связи наборов данных в СКД:
НаборДанныхСуммы и НаборДанныхЦены).НаборДанныхСуммыНаборДанныхЦеныНоменклатура = НоменклатураЭтот способ является наиболее чистым, архитектурно правильным и масштабируемым в современной разработке на платформе 1С:Предприятие 8.3.