Прямые запросы 1С++: Как получить данные из поля неопределенного вида (ЮрЛицо/ФизЛицо) в SQL

← На главную

При написании прямых запросов к базе данных 1С 7.7 (обычно с использованием компоненты 1С++) мы часто сталкиваемся с ситуацией, когда реквизит справочника имеет тип «Справочник» без уточнения конкретного вида. В типовой конфигурации «Торговля и Склад» ярким примером является поле ЮрФизЛицо в справочнике Контрагенты. Оно может ссылаться либо на справочник ЮрЛица, либо на ФизЛица.

Рассмотрим подробно, как правильно написать SQL-запрос для получения реквизитов (например, ИНН или Адреса) из такого поля, разберем типичные ошибки синтаксиса MS SQL и проанализируем структуру идентификаторов 1С.

Проблема: Ошибки синтаксиса и структура ссылок

При попытке написать запрос «в лоб», используя привычные функции из других диалектов SQL (например, Oracle или SQLite), мы часто получаем ошибку от ODBC драйвера SQL Server:

State 42000, native 195, message [Microsoft][ODBC SQL Server Driver][SQL Server]'length' is not a recognized built-in function name.

Или аналогичную ошибку про функцию Substr. Давайте выясним причину. MS SQL (T-SQL) имеет свой набор строковых функций, который отличается от стандарта ANSI SQL, используемого в других СУБД.

Вместо length() в MS SQL используется функция LEN().
Вместо Substr() необходимо использовать SUBSTRING().

Вторая сложность заключается в структуре хранения данных в 1С 7.7. Поле неопределенного вида (составной тип) хранит идентификатор длиной 13 символов, в то время как поле ID в таблице конкретного справочника имеет длину 9 символов.

Структура 13-символьного ID:

Первые 4 символа — это идентификатор вида метаданных (Type ID) в 36-ричной системе.
Последние 9 символа — это непосредственно ID объекта (Surrogate Key).

Чтобы соединить таблицу контрагентов с таблицами ЮрЛиц и ФизЛиц, нам нужно правильно «разобрать» эту строку.

Решение 1: Использование LEFT JOIN с проверкой типа

Наиболее понятный способ решения — присоединить обе таблицы (и ЮрЛиц, и ФизЛиц) левым соединением, используя условия на проверку типа справочника. Для этого в 1С++ существуют удобные мета-имена, например $ВидСправочника36.ЮрЛица, который парсер превращает в те самые 4 символа типа.

Разберем логику запроса:

Берем таблицу контрагентов.
Проверяем первые 4 символа поля ЮрФизЛицо. Если они совпадают с видом справочника «ЮрЛица», соединяем с таблицей $Справочник.ЮрЛица по оставшимся 9 символам.
Аналогично поступаем для «ФизЛиц».
В выборке используем функцию COALESCE, которая вернет первое непустое значение (то есть адрес из той таблицы, которая успешно присоединилась).

Посмотрим на итоговый рабочий код запроса:


rs = СоздатьОбъект("ODBCRecordset");
ТекстЗапроса = "
|SELECT
|    Заявка.IDDOC [Заявка $Документ.ЗаявкаПокупателя]
|  , $Заявка.Контрагент [Контрагент $Справочник.Контрагенты]
|  , RTRIM(Контрагенты.Code) КодКонтрагента
|    -- Выбираем адрес из той таблицы, которая присоединилась
|  , COALESCE($СпрЮЛ.ЮрАдрес, $СпрФЛ.ЮрАдрес, 'Адрес не указан') AS ФактАдрес
|
|FROM _1SJOURN AS Журнал With (NOLOCK)
|    INNER JOIN $Документ.ЗаявкаПокупателя AS Заявка With (NOLOCK) 
|        ON Журнал.IDDOC = Заявка.IDDOC
|    INNER JOIN $Справочник.Контрагенты AS Контрагенты With (NOLOCK) 
|        ON Контрагенты.ID = $Заявка.Контрагент
|
|    -- Присоединяем ЮрЛиц
|    LEFT JOIN $Справочник.ЮрЛица AS СпрЮЛ With (NOLOCK)
|        ON SUBSTRING($Контрагенты.ЮрФизЛицо, 1, 4) = $ВидСправочника36.ЮрЛица 
|        AND СпрЮЛ.ID = SUBSTRING($Контрагенты.ЮрФизЛицо, 5, 9)
|
|    -- Присоединяем ФизЛиц
|    LEFT JOIN $Справочник.ФизЛица AS СпрФЛ With (NOLOCK)
|        ON SUBSTRING($Контрагенты.ЮрФизЛицо, 1, 4) = $ВидСправочника36.ФизЛица 
|        AND СпрФЛ.ID = SUBSTRING($Контрагенты.ЮрФизЛицо, 5, 9)
|
|WHERE 
|    $Заявка.ДатаОтгрузки BETWEEN :НачДата AND :КонДата
|    AND (Журнал.Closed & 1 = 1) -- Только проведенные документы
|";

Обратите внимание на использование функции SUBSTRING($Контрагенты.ЮрФизЛицо, 5, 9). Именно она вырезает 9-символьный ID для соединения с полем ID справочника.

Решение 2: Использование UNION (Оптимизация)

Хотя вариант с LEFT JOIN и условиями прост для понимания, иногда SQL Server может строить неоптимальный план выполнения из-за использования функций в условии соединения (ON). Альтернативный подход — заранее объединить справочники ЮрЛиц и ФизЛиц в одну виртуальную таблицу, приведя их ID к формату 13 символов.

Рассмотрим, как это сделать:


ТекстЗапроса = "
|SELECT
|    ... (поля выборки)
|    , ЮФЛ.ЮрАдрес AS ФактАдрес
|FROM ... (основные таблицы)
|
|    -- Соединяем с подзапросом
|    LEFT JOIN (
|        SELECT
|            ($ВидСправочника36.ЮрЛица + ID) AS ID13, -- Собираем полный 13-значный ID
|            $ЮрЛица.ЮрАдрес AS ЮрАдрес
|        FROM $Справочник.ЮрЛица With (NOLOCK)
|        
|        UNION ALL
|        
|        SELECT
|            ($ВидСправочника36.ФизЛица + ID) AS ID13,
|            $ФизЛица.ЮрАдрес AS ЮрАдрес
|        FROM $Справочник.ФизЛица With (NOLOCK)
|    ) AS ЮФЛ ON ЮФЛ.ID13 = $Контрагенты.ЮрФизЛицо
|";

Этот метод хорош тем, что условие соединения становится простым равенством ЮФЛ.ID13 = $Контрагенты.ЮрФизЛицо, что часто позволяет оптимизатору работать эффективнее.

Важные нюансы и рекомендации

Использование NOLOCK

В примерах выше мы использовали конструкцию With (NOLOCK). Для базы данных 1С 7.7 на SQL Server это критически важно при построении отчетов. 1С 7.7 часто держит блокировки на таблицах, и без NOLOCK ваш запрос будет ждать, пока пользователи закончат работу (или наоборот, отчет заблокирует работу пользователей). Это аналог «грязного чтения», но для отчетности в 7.7 это стандарт де-факто.

Важно: Начиная с SQL Server 2005 и выше, если вы указываете хинты таблиц, служебное слово With является обязательным, если хинтов несколько или в определенных контекстах, поэтому лучше писать его всегда: Tablename AS Alias With (NOLOCK).

INNER JOIN против LEFT JOIN

Давайте проанализируем, какой тип соединения использовать при связке документов и справочников.

В примере запроса используется INNER JOIN для соединения Документа и Справочника Контрагентов:

INNER JOIN $Справочник.Контрагенты AS Контрагенты ON ...

Если в вашей базе данных есть «битые ссылки» (ситуация, когда в документе ссылка на контрагента есть, а самого контрагента уже удалили без контроля ссылочной целостности), то при использовании INNER JOIN такие документы пропадут из выборки. Это может привести к расхождению итогов в отчете.

Для большей надежности, особенно в старых базах, рекомендуется использовать LEFT JOIN для присоединения справочников. В этом случае, если контрагент не найден, документ все равно попадет в отчет, а поля контрагента (Наименование, Код) будут иметь значение NULL.

Правильный подход для надежного отчета:


FROM _1SJOURN AS Журнал With (NOLOCK)
    INNER JOIN $Документ.ЗаявкаПокупателя AS Заявка With (NOLOCK) ON Журнал.IDDOC = Заявка.IDDOC
    -- Используем LEFT JOIN на случай битых ссылок
    LEFT JOIN $Справочник.Контрагенты AS Контрагенты With (NOLOCK) ON Контрагенты.ID = $Заявка.Контрагент

Резюме

Для успешного получения данных из полей неопределенного типа в 1С 7.7 через прямые запросы:

Используйте правильный синтаксис T-SQL: LEN() и SUBSTRING().
Помните о структуре ID: 4 символа типа + 9 символов ID.
Используйте мета-имена 1С++ ($ВидСправочника36...) для переносимости кода.
Применяйте COALESCE для выбора данных из нескольких таблиц при условном соединении.
Не забывайте про With (NOLOCK) для избежания блокировок.