Простейший метод построения прямого запроса к регистру остатков 1С   

Один из наиболее часто встречающихся видов запросов - это запрос балансового типа, в котором в разрезе необходимых нам измерений за выбранный период получаются начальные остатки, приход, расход и конечные остатки. Часто возникает необходимость ускорить данный запрос с помощью перевода его в прямой запрос к SQL-серверу. Но при рассмотрении выясняется, что, фактически мы имеем дело не с одним запросом, а с четырьмя, результаты которых должны быть объединены в один. Можно по-разному подходить к этой задаче – сводить данные в таблице значений, использовать временные таблицы SQL-сервера и т.д.

Здесь я опишу наиболее легкий вариант построения такого запроса, который не требует ни временных таблиц, ни сведения в один на «клиенте», ни каких бы то ни было дополнительных действий с базой данных. Все очень просто.

Итак. Представим такой простой запрос :
Запрос=СоздатьОбъект("Запрос");
ТекстЗапроса="//{{ЗАПРОС(ОдинС)
|Период с НачДата по КонДата;
|Товар = Регистр.Партии.Товар;
|Количество = Регистр.Партии.Количество;
|Функция Нач = НачОст(Количество);
|Функция Прих = Приход(Количество);
|Функция Расх = Расход(Количество);
|Функция Кон = КонОст(Количество);
|Группировка Товар без групп;
|"//}}ЗАПРОС
;

Как мы видим, имеет место обращение к регистру «Партии» с учетом выбранного в диалоге периода, одна группировка по измерению «Товар», 4 численных колонки (начальный остаток, приход, расход, конечный остаток). Никаких условий для простоты демонстрации. Как видно, в запросе будут участвовать три таблицы: таблицы остатков и движений регистра «Партии» и таблица «журнал документов». В тестовом запросе будет анализироваться период с 05.02.2002 года, по 20.01.2004 для усложнения задачи.

Казалось бы ничего сложного – мы строим 4 запроса в каждом из которых имеется столбец товара и числовой столбец с соответствующим результатом суммирования (начальный остаток, приход, расход или конечный остаток) и соединяем их в одну таблицу с помощью «UNION ALL». Но тут нас поджидает ловушка – в результирующей таблице будет всего 2 столбца: столбец товара и столбец данных, и это все несмотря на то, что мы по-разному называли наши столбцы данных в каждом из запросов.

Все очень просто – для правильного объединения SQL-сервер требует одинаковое количество столбцов в каждой колонке в каждой из объединяемых таблиц. И мы дадим ему это. Пусть столбец с товаром называется «Товар», а столбцы с данными будут называться соответственно «Нач», «Прих», «Расх», «Кон». Тогда нам нужно, чтобы в каждом запросе были все четыре столбца с такими наименованиями. Лишь один из них в каждом запросе будет содержать данные, остальные будут содержать 0, причем мы сами будем декларировать эти столбцы, как содержащие нулевое значение. Тогда объединение таблиц у нас пройдет на ура. Кроме того, раз мы имеем три значения балансовой формулы (начальный остаток, приход и расход), то конечный остаток нам высчитывать уже не нужно – мы возьмем его из уже получившейся таблицы!

Ниже приведен текст запроса в варианте написания для Rainbow, 1C++ или Neta.dll. Изначально он содержал в себе четыре запроса - 2 для остатков и по одному для прихода и расхода, но путем объединения запросов по приходу и расходу в один мы получим снижение времени выполнения запроса на 40%. Смотрите новый текст запроса:

SELECT Товар, SUM(Нач) AS Нач, SUM(Прих) AS Прих, SUM(Расх) AS Расх, SUM(Нач) + SUM(Прих) - SUM(Расх) AS Кон
FROM (SELECT SP259 AS Товар, SP263 AS Нач, 0 AS Прих, 0 AS Расх
  FROM RG258
  WHERE PERIOD = CONVERT(DATETIME, '2002-01-01 00:00:00', 102)

  UNION ALL

  SELECT RA258.SP259 AS Товар, RA258.SP263 * (1 - RA258.DEBKRED * 2) AS Нач, 0 AS Прих, 0 AS Расх
  FROM RA258 INNER JOIN
  _1SJOURN ON RA258.IDDOC = _1SJOURN.IDDOC
  WHERE (_1SJOURN.DATE_TIME_IDDOC >= '20020201') AND (_1SJOURN.DATE_TIME_IDDOC < '20020205') AND (_1SJOURN.RF258 = 1) AND
  (_1SJOURN.CLOSED & 1 = 1)

  UNION ALL

  SELECT RA258.SP259 AS Товар, 0 AS Нач, CASE WHEN RA258.DEBKRED = 0 THEN RA258.SP263 ELSE 0 END AS Прих,
  CASE WHEN RA258.DEBKRED = 1 THEN RA258.SP263 ELSE 0 END AS Расх
  FROM RA258 INNER JOIN
  _1SJOURN ON RA258.IDDOC = _1SJOURN.IDDOC
  WHERE (_1SJOURN.DATE_TIME_IDDOC >= '20020205') AND (_1SJOURN.DATE_TIME_IDDOC < '20040121') AND (_1SJOURN.RF258 = 1) AND
  (_1SJOURN.CLOSED & 1 = 1)) TMP
GROUP BY Товар

А это первая строчка запроса для варианта написания ADO (она единственная отличается):

SELECT Товар, CONVERT(money, SUM(Нач)) AS Нач, CONVERT(money, SUM(Прих)) AS Прих, CONVERT(money, SUM(Расх)) AS Расх, CONVERT(money, SUM(Нач) + SUM(Прих) - SUM(Расх)) AS Кон

На основе приведенной конструкции можно создавать и более сложные запросы с условиями, группировками и т.д.

 


 

Перепечатка, воспроизведение в любой форме, распространение, в том числе в переводе, любых материалов с сайта www.softpoint.ru возможны только с письменного разрешения компании "СофтПоинт". Это правило действует для всех без исключения случаев, кроме тех, когда в материале прямо указано разрешение на копирование (основание: Закон Российской Федерации "Об авторском праве и смежных правах").