Как в 1С 7.7 совместить ссылки и строки в одной колонке при SQL-запросе с итогами?

← На главную

При работе с прямыми SQL-запросами в 1С 7.7 часто возникает задача получить отчет с итогами. Для этого в SQL отлично подходит конструкция GROUP BY ... WITH ROLLUP. Однако возникает проблема: в итоговых строках хочется видеть текстовые подписи, например, "(все)" или "Итого", а в обычных строках — ссылки на объекты 1С (справочники, документы). Стандартный парсер 1С, получая результат запроса, определяет тип колонки по первым строкам. Если он видит ссылку на справочник, то всей колонке присваивается тип "Справочник", и любая строка, не являющаяся ссылкой, превращается в пустое значение. Давайте разберем, как обойти это ограничение.

Проанализируем ситуацию: у нас есть SQL-запрос, который возвращает данные и итоговые строки. В колонке, где должны быть ссылки на контрагентов, в итоговой строке мы хотим видеть текст. Парсер 1С "съедает" этот текст. Кроме того, при попытке использовать конструкцию CASE в SQL для вывода разных типов данных (например, даты и строки), SQL Server выдает ошибку преобразования типов. Рассмотрим по шагам, как решить обе эти проблемы.

Решение 1: Используем "неопределенный тип" и "самую длинную строку"

Ключ к решению — заставить парсер 1С не определять тип колонки жестко, а разбирать значение в каждой ячейке индивидуально. Для этого существует специальный, хотя и недокументированный, механизм, который часто называют "самая длинная строка".

Суть механизма заключается в двух действиях:

Указать специальный псевдоним для колонки. К имени колонки нужно добавить суффикс $Неопределено. Например, [Комитент $Неопределено]. Этот суффикс — сигнал для парсера, что в данной колонке могут быть значения разных типов.
Сформировать значения в специальном строковом формате. Каждое значение в такой колонке должно быть строкой, начинающейся со специального префикса, который указывает на тип данных.

Основные префиксы для "самой длинной строки":

S — для типа "Строка". Пример: 'S' + 'все'.
D — для типа "Дата". Пример: 'D' + '20240101'.
B1 — для типа "Справочник". Формат: 'B1' + $ВидСправочника36.ИмяСправочника + ID_Элемента.
O1 — для типа "Документ".
E1 — для типа "Перечисление". Формат: 'E1' + $ВидПеречисления36.ИмяПеречисления + ID_Значения.

Теперь объединим это знание с функцией SQL GROUPING(). Эта функция используется вместе с WITH ROLLUP и возвращает 1, если текущая строка является итоговой для указанного поля, и 0 в противном случае. Это позволяет нам точно определить, где выводить итоговую строку, а где — значение.

Посмотрим на пример. Нам нужно в итоговых строках по комитенту выводить строку "(все)", а в остальных — ссылку на комитента.


CASE WHEN Grouping($РасходнаяНакладнаяСтроки.Комитент) = 1 
     THEN 'S' + '(все)' 
     ELSE 'B1' + $ВидСправочника36.Контрагенты + $РасходнаяНакладнаяСтроки.Комитент 
END AS [Комитент $Неопределено]

Разберем этот код:

Grouping($РасходнаяНакладнаяСтроки.Комитент) = 1 — проверяем, является ли текущая строка итогом по полю "Комитент".
THEN 'S' + '(все)' — если это итоговая строка, формируем значение типа "Строка" с текстом "(все)".
ELSE 'B1' + $ВидСправочника36.Контрагенты + $РасходнаяНакладнаяСтроки.Комитент — в противном случае, формируем специальную строку для ссылки на справочник. Парсер 1С, получив такую строку, сам преобразует ее в полноценную ссылку на элемент справочника "Контрагенты".
AS [Комитент $Неопределено] — задаем псевдоним, который активирует этот механизм.

Этот подход можно применить для любых типов данных, которые нужно смешать в одной колонке.

Решение 2: Проблема смешивания типов в SQL CASE и ее решение

Вторая проблема, с которой столкнулся автор, — это ошибка SQL сервера при попытке вернуть из одного выражения CASE значения разных типов. Например, такой код вызовет ошибку:


-- Этот код вызовет ошибку
CASE WHEN Grouping(дат) = 1 
     THEN 'все' -- тип varchar
     ELSE дат   -- тип datetime
END as грдата

SQL требует, чтобы все возможные результаты выражения CASE приводились к одному типу данных с наивысшим приоритетом. В данном случае, у datetime приоритет выше, чем у varchar, поэтому SQL Server пытается преобразовать строку 'все' в дату, что, естественно, не удается, и мы получаем ошибку "Conversion failed...".

Решение этой проблемы идеально сочетается с первым решением. Нужно все результаты внутри CASE приводить к строковому типу, используя тот же механизм "самой длинной строки".

Исправленный вариант для даты будет выглядеть так:


CASE WHEN Grouping(Left(Журнал.DATE_TIME_IDDOC, 8)) = 1 
     THEN 'S' + '(все)' 
     ELSE 'D' + Left(Журнал.DATE_TIME_IDDOC, 8) 
END AS [Документ_дата $Неопределено]

Здесь мы дату (представленную строкой YYYYMMDD) "оборачиваем" в префикс D. Теперь SQL Server работает только со строками, и никаких ошибок преобразования типов не возникает. А парсер 1С на своей стороне корректно преобразует строку 'D20240101' в дату `01.01.2024`.

Итоговый пример кода

Давайте посмотрим на полный запрос, который объединяет все эти техники для построения сложного отчета с группировками и итогами без дополнительной обработки в 1С.


ТекстЗапроса = "
|SELECT 
|    Case when Grouping($РасходнаяНакладнаяСтроки.Комитент)=1 then 'S' + '(все)' else 'B1' + $ВидСправочника36.Контрагенты + $РасходнаяНакладнаяСтроки.Комитент end [Комитент $Неопределено]
|  , Case when Grouping(Журнал.$ОбщийРеквизит.ЮридическоеЛицо)=1 then 'S' + '(все)' else 'B1'  +$ВидСправочника36.Контрагенты+ Журнал.$ОбщийРеквизит.ЮридическоеЛицо end [ЮридическоеЛицо $Неопределено]
|  , Case when Grouping(Left(Журнал.DATE_TIME_IDDOC, 8))=1 then 'S' + '(все)' else 'D'+Left(Журнал.DATE_TIME_IDDOC, 8) end as [Документ_дата $Неопределено]
|  , Case when Grouping($РасходнаяНакладная.ТипНакладной)=1 then 'S' + '(все)' else 'E1' + $ВидПеречисления36.ТипыРасходнойНакладной + $РасходнаяНакладная.ТипНакладной end [ТипНакладной $Неопределено]
|  , Sum($РасходнаяНакладнаяСтроки.Сумма) Сумма
|  , Sum($РасходнаяНакладнаяСтроки.НДС) НДС
|  , SUM($РасходнаяНакладнаяСтроки.Количество) Количество
|  , SUM($РасходнаяНакладнаяСтроки.Всего) Всего
|FROM _1SJOURN AS Журнал With (NOLOCK)
|    INNER JOIN $Документ.РасходнаяНакладная AS РасходнаяНакладная With (NOLOCK) ON Журнал.IDDOC = РасходнаяНакладная.IDDOC
|    INNER JOIN $ДокументСтроки.РасходнаяНакладная AS РасходнаяНакладнаяСтроки With (NOLOCK) ON РасходнаяНакладная.IDDOC = РасходнаяНакладнаяСтроки.IDDOC
|WHERE ...
|GROUP BY $РасходнаяНакладнаяСтроки.Комитент, Журнал.$ОбщийРеквизит.ЮридическоеЛицо, Left(Журнал.DATE_TIME_IDDOC, 8), $РасходнаяНакладная.ТипНакладной WITH ROLLUP
|ORDER BY 
|    GROUPING($РасходнаяНакладнаяСтроки.Комитент) DESC,
|    $РасходнаяНакладнаяСтроки.Комитент,
|    GROUPING(Журнал.$ОбщийРеквизит.ЮридическоеЛицо) DESC,
|    Журнал.$ОбщийРеквизит.ЮридическоеЛицо,
|    GROUPING(Left(Журнал.DATE_TIME_IDDOC, 8)) DESC, 
|    Left(Журнал.DATE_TIME_IDDOC, 8)
|";
// ...
тз = рс.ВыполнитьИнструкцию(ТекстЗапроса);

Обратите внимание на секцию ORDER BY. Сортировка сначала по GROUPING() DESC позволяет разместить итоговые строки (где GROUPING()=1) после детальных записей по каждой группе.

Бонусный совет: Выгрузка результата в Индексированную таблицу

Если запрос возвращает большой объем данных, для повышения производительности дальнейшей обработки результат можно выгружать не в стандартную ТаблицуЗначений, а сразу в ИндексированнуюТаблицу. Метод ВыполнитьИнструкцию() это позволяет.

Для этого нужно сначала создать объект, а затем передать его вторым параметром в метод.


// Создаем объект индексированной таблицы
итз = СоздатьОбъект("ИндексированнаяТаблица");

// Выполняем запрос, результат будет помещен в итз
// Третий параметр (1 по умолчанию) указывает, что таблицу нужно очистить и создать колонки
рс.ВыполнитьИнструкцию(ТекстЗапроса, итз, 1);

// Теперь можно работать с итз, используя преимущества индексации

Таким образом, комбинируя знания об особенностях парсера 1С 7.7 и функциях SQL, можно создавать сложные и производительные отчеты одним запросом, избегая медленной постобработки данных средствами 1С.

← На главную