При работе с платформой 1С 7.7 и использовании внешних компонент для выполнения прямых SQL-запросов (таких как 1С++ или Rainbow) разработчики часто сталкиваются с загадочной ситуацией: один и тот же запрос идеально отрабатывает в специализированных консолях (например, qryConsole или ToySql), но при попытке выполнить его непосредственно из модуля 1С система возвращает ошибку State 42000, native 102, message [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near....
В этой статье мы подробно разберем, почему это происходит, проанализируем ошибки в синтаксисе запросов и научимся правильно переводить «консольные» запросы в рабочий программный код.
Проанализируем ситуацию, описанную в теме. Автор столкнулся с ошибкой синтаксиса в районе ключевого слова TOTALS. Рассмотрим фрагмент запроса:
TOTALS Sum(_ubytok_S)
, Sum(_Pribyl_S)
, Count(KLV_KLI)
BY Vozrast
, [Пол $Перечисление.Пол]
Выясним причину ошибки. Ключевое слово TOTALS не является стандартным оператором языка T-SQL (Transact-SQL), на котором общается Microsoft SQL Server. Это расширение синтаксиса, которое реализовано внутри популярных консолей запросов для 1С 7.7. Когда консоль видит блок TOTALS, она выступает в роли интеллектуальной обертки:
Однако, когда мы используем объект ODBCRecordset в коде 1С++, метод Выполнить() (или Execute()) передает текст запроса напрямую драйверу ODBC. SQL Server, получив команду TOTALS, которую он не понимает, закономерно возвращает ошибку 102 (неверный синтаксис).
Решение: Если вам нужно получить итоги программно, блок TOTALS необходимо удалить. Рассчитывать итоги придется либо перебором выборки в цикле 1С, либо используя стандартные средства SQL Server, такие как ROLLUP или GROUPING SETS.
Рассмотрим еще один нюанс, который часто вызывает сбои при переходе от консоли к коду. В примере автора используется псевдоним Vozrast в блоках группировки и сортировки:
SELECT NullIf(Cast(Left(Прибыль.DATE_TIME_IDDOC, 8) AS datetime), '17530101') Vozrast
...
GROUP BY Cast(Left(Прибыль.DATE_TIME_IDDOC, 8) AS datetime)
...
ORDER BY Cast(Left(Прибыль.DATE_TIME_IDDOC, 8) AS datetime)
Обратите внимание на важный момент: в стандартном SQL Server (особенно старых версий) нельзя использовать псевдонимы (Aliasing), заданные в SELECT, внутри GROUP BY. В блоке ORDER BY использование псевдонимов иногда допустимо, но считается плохим тоном и может зависеть от настроек драйвера.
Правильный подход — дублировать полное выражение в GROUP BY, либо использовать вложенный подзапрос (Common Table Expression или производную таблицу), чтобы «вычислить» поле один раз и обращаться к нему по имени в верхнем уровне запроса.
Проанализируем конструкцию Cast(Left(Прибыль.DATE_TIME_IDDOC, 8) AS datetime). С точки зрения производительности — это «узкое место» запроса.
В 1С 7.7 данные о времени и дате хранятся в поле DATE_TIME_IDDOC в виде строки формата YYYYMMDDHHMMSSIDDOC. Когда мы применяем функции LEFT и CAST к полю таблицы в условии WHERE, SQL Server не может использовать индексы по этому полю. Ему приходится выполнять полное сканирование таблицы (Index Scan или Table Scan), вычисляя функцию для каждой строки.
Разберем, как сделать запрос эффективнее. Вместо того чтобы преобразовывать данные в базе к типу datetime, лучше передавать параметры из 1С в виде строк. Рассмотрим пример оптимизированного условия:
// В коде 1С подготовим параметры в строковом виде
ДатаНачСтр = Формат(ДатаНач, "ДГГГГММДД");
ДатаКонСтр = Формат(ДатаКон, "ДГГГГММДД") + "ZZZZZZZZZZ"; // Чтобы захватить весь день
// В тексте SQL-запроса
WHERE Прибыль.DATE_TIME_IDDOC >= :ДатаНачСтр
AND Прибыль.DATE_TIME_IDDOC <= :ДатаКонСтр
Такой подход позволяет серверу мгновенно найти нужный диапазон строк, используя индекс, что критически важно для больших регистров.
В исходном запросе мы видим конструкцию $Перечисление.Пол. Важно понимать, как 1С 7.7 хранит эти данные. В базе SQL Server перечисления — это не текст («Мужской», «Женский»), а короткие идентификаторы (ID) в 36-ричной системе счисления.
Если вы просто выберете это значение, вы получите системный ID. Чтобы получить представление (текст), необходимо либо соединяться с системной таблицей метаданных, либо обрабатывать результат в коде 1С. Однако большинство программистов предпочитает работать с ID для сравнения, так как это быстрее. Помните, что консоль запросов часто делает «красивый вывод» автоматически, подставляя названия значений перечислений, а ODBCRecordset вернет вам «сырые» данные из базы.
Разберем по шагам, что нужно сделать, чтобы запрос из примера заработал в коде:
Пока Запрос.ВзятьСтроку() = 1 Цикл ... КонецЦикла;.FROM или после последнего поля в SELECT.ORDER BY вызывает ошибку, попробуйте сначала запустить запрос без него, а затем добавьте его, используя точные выражения из GROUP BY.УстановитьТекстовыйПараметр("ДатаНач", ДатаНач). Компонента 1С++ сама преобразует дату 1С в нужный формат для SQL, если в запросе используется мета-имя поля через знак $.Посмотрим на пример того, как должен выглядеть очищенный запрос в коде 1С:
ТекстЗапроса = "
|SELECT
| Left(Прибыль.DATE_TIME_IDDOC, 8) as [ДатаДок $Дата],
| $Клиенты.Пол as [Пол $Перечисление.Пол],
| SUM(CASE WHEN $Прибыль.Выручка < 0 THEN $Прибыль.Выручка ELSE 0 END) as _ubytok_S,
| SUM(CASE WHEN $Прибыль.Выручка > 0 THEN $Прибыль.Выручка ELSE 0 END) as _Pribyl_S,
| Count(distinct $Прибыль.Клиент) as KLV_KLI
|FROM
| $Регистр.Прибыль AS Прибыль (NOLOCK)
|INNER JOIN
| $Справочник.Клиенты AS Клиенты (NOLOCK) ON $Прибыль.Клиент = Клиенты.ID
|WHERE
| Прибыль.DATE_TIME_IDDOC >= :ДатаНач AND Прибыль.DATE_TIME_IDDOC <= :ДатаКон
|GROUP BY
| Left(Прибыль.DATE_TIME_IDDOC, 8),
| $Клиенты.Пол
|ORDER BY
| 1, 2
|";
В данном примере мы использовали нумерацию колонок в ORDER BY 1, 2 (сортировка по первой и второй колонке SELECT), что является максимально универсальным способом для SQL Server и позволяет избежать проблем с именами псевдонимов.
Соблюдение этих правил позволит вам писать надежные и быстрые прямые запросы, которые будут работать одинаково стабильно как в инструментах разработчика, так и в боевой среде предприятия.