При разработке отчетов и печатных форм в программе 1С:Зарплата и управление персоналом 3.1 программисты часто сталкиваются с необходимостью объединения (сворачивания) строк по физическим лицам. Типичный пример — получение сводных данных по регистру накопления УдержанияПоИсполнительнымДокументам.
Поскольку удержания могут регистрироваться самыми разными документами-регистраторами (такими как НачислениеЗарплаты, БольничныйЛист, Отпуск и другие), в физической таблице базы данных образуется множество детальных записей. Попытка сгруппировать эти записи стандартными средствами языка запросов 1С часто приводит к возникновению критической ошибки СУБД:
«Недопустимое поле для группировки "ИсполнительныйДокументАдресПолучателяПредставление"»
В этой статье мы подробно разберем, почему возникает данная ошибка, проанализируем внутренние механизмы работы платформы 1С с базой данных и рассмотрим четыре практических способа решения этой проблемы.
Для начала давайте разберемся, почему простая группировка полей вызывает ошибку на уровне платформы. В конфигурации «Зарплата и управление персоналом 3.1» адрес получателя вносится непосредственно в документ ИсполнительныйЛист. Соответствующий реквизит (например, АдресПолучателяПредставление) имеет тип Строка (неограниченной длины).
С точки зрения систем управления базами данных (таких как MS SQL Server, PostgreSQL или Oracle), строки неограниченной длины хранятся в специальных структурах данных (LOB/CLOB). СУБД физически не могут выполнять операции сравнения, индексации и группировки (оператор GROUP BY) над такими тяжелыми типами данных. Платформа 1С:Предприятие транслирует это ограничение разработчику в виде ошибки синтаксиса запроса.
Давайте разберем по шагам, как мы можем обойти это ограничение архитектуры СУБД и получить красивый сгруппированный результат.
Самый быстрый и простой способ «успокоить» СУБД — это принудительно ограничить длину строкового поля «на лету» прямо в процессе выполнения запроса. Для этого в платформе 1С предусмотрен оператор ВЫРАЗИТЬ.
Мы можем преобразовать строку неограниченной длины в строку фиксированной длины (например, до 500 символов). Строки ограниченной длины СУБД группирует без каких-либо возражений.
Рассмотрим пример реализации такого запроса:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Удержания.ФизическоеЛицо КАК ФизическоеЛицо,
| Удержания.Получатель КАК Получатель,
| ВЫРАЗИТЬ(Удержания.ИсполнительныйДокумент.АдресПолучателяПредставление КАК СТРОКА(500)) КАК АдресПолучателя,
| СУММА(Удержания.СуммаУдержания) КАК СуммаУдержания,
| СУММА(Удержания.СуммаВознагражденияПлатежногоАгента) КАК СуммаВознагражденияПлатежногоАгента
|ИЗ
| РегистрНакопления.УдержанияПоИсполнительнымДокументам КАК Удержания
|ГДЕ
| Удержания.Регистратор.Дата МЕЖДУ &ДатаНачала И &ДатаКонца
|
|СГРУППИРОВАТЬ ПО
| Удержания.ФизическоеЛицо,
| Удержания.Получатель,
| ВЫРАЗИТЬ(Удержания.ИсполнительныйДокумент.АдресПолучателяПредставление КАК СТРОКА(500))";
Запрос.УстановитьПараметр("ДатаНачала", ДатаС);
Запрос.УстановитьПараметр("ДатаКонца", ДатаПо);
РезультатЗапроса = Запрос.Выполнить();
Важные нюансы метода:
ВЫРАЗИТЬ — 1024 символа. Для большинства адресов лимита в 500–1000 символов хватает с избытком.ВЫРАЗИТЬ должна быть написана как в секции ВЫБРАТЬ, так и в секции СГРУППИРОВАТЬ ПО.ВЫРАЗИТЬ может дополнять полученную строку пробелами до указанной максимальной длины. При выводе на печать рекомендуется обрабатывать такие строки стандартной функцией СокрЛП().Альтернативным вариантом ограничения длины строки в запросе является использование функции ПОДСТРОКА. Она решает ту же задачу, но работает по несколько иному алгоритму.
Функция ПОДСТРОКА вырезает из строки неограниченной длины кусок заданной длины, начиная с первого символа. При этом, в отличие от ВЫРАЗИТЬ, большинство СУБД не дополняют результат концевыми пробелами до максимального значения, что экономит память и упрощает дальнейшую работу.
Посмотрим на пример применения функции ПОДСТРОКА:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Удержания.ФизическоеЛицо КАК ФизическоеЛицо,
| Удержания.Получатель КАК Получатель,
| ПОДСТРОКА(Удержания.ИсполнительныйДокумент.АдресПолучателяПредставление, 1, 500) КАК АдресПолучателя,
| СУММА(Удержания.СуммаУдержания) КАК СуммаУдержания,
| СУММА(Удержания.СуммаВознагражденияПлатежногоАгента) КАК СуммаВознагражденияПлатежногоАгента
|ИЗ
| РегистрНакопления.УдержанияПоИсполнительнымДокументам КАК Удержания
|ГДЕ
| Удержания.Регистратор.Дата МЕЖДУ &ДатаНачала И &ДатаКонца
|
|СГРУППИРОВАТЬ ПО
| Удержания.ФизическоеЛицо,
| Удержания.Получатель,
| ПОДСТРОКА(Удержания.ИсполнительныйДокумент.АдресПолучателяПредставление, 1, 500)";
Запрос.УстановитьПараметр("ДатаНачала", ДатаС);
Запрос.УстановитьПараметр("ДатаКонца", ДатаПо);
РезультатЗапроса = Запрос.Выполнить();
Этот способ также отлично работает, но согласно внутренним стандартам разработки фирмы «1С», использование оператора ВЫРАЗИТЬ является более приоритетным для обеспечения кроссплатформенной совместимости и лучшей производительности на СУБД MS SQL Server.
Предыдущие два способа имеют один существенный недостаток: они искусственно обрезают строку адреса. Если в базе данных заполнен очень длинный адрес (например, включающий страну, субъект, район, сложную структуру улиц и комментарии для почтальона), то лимит в 500 символов может быть превышен, и адрес на печатной форме окажется обрезанным.
Чтобы избежать этой проблемы и одновременно оптимизировать скорость работы СУБД, мы можем применить двухэтапную схему запроса с использованием Временных Таблиц (ВТ):
ФизическоеЛицо, Получатель, ИсполнительныйДокумент). Ссылка на документ ИсполнительныйЛист группируется без проблем, так как имеет фиксированный тип идентификатора. Результат сохраняем во временную таблицу.АдресПолучателяПредставление в его первозданном виде. На этом этапе группировка уже не требуется, и адрес выводится полностью, без ограничений по длине.Проанализируем пример такого оптимального запроса:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Удержания.ФизическоеЛицо КАК ФизическоеЛицо,
| Удержания.Получатель КАК Получатель,
| Удержания.ИсполнительныйДокумент КАК ИсполнительныйДокумент,
| СУММА(Удержания.СуммаУдержания) КАК СуммаУдержания,
| СУММА(Удержания.СуммаВознагражденияПлатежногоАгента) КАК СуммаВознагражденияПлатежногоАгента
|ПОМЕСТИТЬ ВТ_СгруппированныеДанные
|ИЗ
| РегистрНакопления.УдержанияПоИсполнительнымДокументам КАК Удержания
|ГДЕ
| Удержания.Регистратор.Дата МЕЖДУ &ДатаНачала И &ДатаКонца
|
|СГРУППИРОВАТЬ ПО
| Удержания.ФизическоеЛицо,
| Удержания.Получатель,
| Удержания.ИсполнительныйДокумент
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ.ФизическоеЛицо КАК ФизическоеЛицо,
| ВТ.Получатель КАК Получатель,
| ВТ.ИсполнительныйДокумент.АдресПолучателяПредставление КАК АдресПолучателя,
| ВТ.СуммаУдержания КАК СуммаУдержания,
| ВТ.СуммаВознагражденияПлатежногоАгента КАК СуммаВознагражденияПлатежногоАгента
|ИЗ
| ВТ_СгруппированныеДанные КАК ВТ";
Запрос.УстановитьПараметр("ДатаНачала", ДатаС);
Запрос.УстановитьПараметр("ДатаКонца", ДатаПо);
РезультатЗапроса = Запрос.Выполнить();
Преимущества данного подхода:
Бывают ситуации, когда запрос строится динамически, либо вы не хотите усложнять структуру пакета запросов временными таблицами. В этом случае группировку можно переложить с плеч СУБД на плечи сервера приложений 1С, выполнив свертку результатов программно.
Для этого мы получаем из базы данных детальные, несгруппированные записи (включая адрес неограниченной длины), выгружаем результат запроса в объект типа ТаблицаЗначений и применяем к нему встроенный метод Свернуть(). Платформа 1С умеет сравнивать и группировать длинные строки в оперативной памяти сервера без генерации ошибок.
Реализуем данный метод на практике:
// 1. Формируем простой запрос без группировки на стороне СУБД
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Удержания.ФизическоеЛицо КАК ФизическоеЛицо,
| Удержания.Получатель КАК Получатель,
| Удержания.ИсполнительныйДокумент.АдресПолучателяПредставление КАК АдресПолучателя,
| Удержания.СуммаУдержания КАК СуммаУдержания,
| Удержания.СуммаВознагражденияПлатежногоАгента КАК СуммаВознагражденияПлатежногоАгента
|ИЗ
| РегистрНакопления.УдержанияПоИсполнительнымДокументам КАК Удержания
|ГДЕ
| Удержания.Регистратор.Дата МЕЖДУ &ДатаНачала И &ДатаКонца";
Запрос.УстановитьПараметр("ДатаНачала", ДатаС);
Запрос.УстановитьПараметр("ДатаКонца", ДатаПо);
// 2. Выгружаем результат в Таблицу Значений
ТаблицаРезультата = Запрос.Выполнить().Выгрузить();
// 3. Производим свертку строк в оперативной памяти
// Первый параметр метода - колонки группировки, второй - суммируемые колонки
ТаблицаРезультата.Свернуть(
"ФизическоеЛицо, Получатель, АдресПолучателя",
"СуммаУдержания, СуммаВознагражденияПлатежногоАгента"
);
Важное предостережение: Метод со сверткой в памяти отлично подходит для небольших и средних объемов данных (до нескольких тысяч строк). Однако, если в выборку попадает огромный массив информации, программная обработка на сервере приложений будет расходовать значительный объем оперативной памяти и уступит по скорости оптимизированному SQL-запросу из Способа 3.
Мы подробно разобрали проблему группировки строк неограниченной длины и изучили способы ее обхода. Выбор конкретного метода зависит от требований к разрабатываемому функционалу: