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