Исправление ошибки арифметического переполнения при преобразовании numeric к типу данных numeric при расчете себестоимости

Опубликовал Руслан Шапиев (TheRealStanly) в раздел Программирование - Практика программирования

В данной статье описывается один из способов исправления ошибки арифметического переполнения при преобразовании numeric к типу данных numeric, с которой не раз сталкивались многие люди, занимающиеся расчетом себестоимости в базах с большим объёмом данных.

Многие люди, занимающиеся расчетом себестоимости в базах с большим объёмом данных, не раз сталкивались со следующей ошибкой:

Microsoft SQL Server Native Client 11.0:Ошибка арифметического переполнения при преобразовании numeric к типу данных numeric.

HRESULT=80040E57, SQLSrvr: SQLSTATE=22003, state=8, Severity=10, native=8115, line=1

Особенно часто (судя по личному опыту и темам форумов) данная ошибка появляется при проведении документа "Расчет себестоимости товаров", когда не хватает знаков до запятой при использоании в запросах функции ВЫРАЗИТЬ и происходит потеря значащих цифр при вычислении значения. Один из самых простых и быстрых вариантов решения следующий:

  1. Запускаем проведение проблемного документа расчета с/с в режиме отладки и дожидаемся ошибки.
  2. Смотрим, на какой строке кода происходит ошибка, и идём в конфигуратор. В моём случае это была строка 2519: Запрос.ВыполнитьПакет()[1].Выбрать() модуля объекта документа "Расчет себестоимости"
  3. В тексте запроса ищем функцию ВЫРАЗИТЬ
  4. Если есть строчки ВЫРАЗИТЬ(<ВыбранноеПоле> КАК ЧИСЛО (23,10))  изменяем их на ВЫРАЗИТЬ(<ВыбранноеПоле> КАК ЧИСЛО (25,10)) и радуемся результату

В моём случае текст запроса был изменен следующим образом:

        "ВЫБРАТЬ
		|	УзлыКорректировки.НомерУзла КАК НомерУзла,
		|	ВЫРАЗИТЬ(МАКСИМУМ(УзлыКорректировки.Стоимость) КАК ЧИСЛО(25, 10)) КАК СвободныйКоэффициент,
		|	ВЫРАЗИТЬ(МАКСИМУМ(УзлыКорректировки.СтоимостьБезНДС) КАК ЧИСЛО(25, 10)) КАК СвободныйКоэффициентБезНДС,
		|	ВЫРАЗИТЬ(МАКСИМУМ(УзлыКорректировки.ПостояннаяРазница) КАК ЧИСЛО(25, 10)) КАК СвободныйКоэффициентПостояннаяРазница,
		|	ВЫРАЗИТЬ(МАКСИМУМ(УзлыКорректировки.ВременнаяРазница) КАК ЧИСЛО(25, 10)) КАК СвободныйКоэффициентВременнаяРазница,
		|	ВЫРАЗИТЬ(МАКСИМУМ(УзлыКорректировки.СтоимостьДопРасходы) КАК ЧИСЛО(25, 10)) КАК СвободныйКоэффициентДопрасходы,
		|	ВЫРАЗИТЬ(МАКСИМУМ(УзлыКорректировки.СтоимостьДопРасходыБезНДС) КАК ЧИСЛО(25, 10)) КАК СвободныйКоэффициентДопрасходыБезНДС,
		|	МИНИМУМ(УзлыКорректировки.ВременнаяРазницаЗнак * ЕСТЬNULL(ВтТаблицаРешений.ВременнаяРазницаЗнак, 1)) КАК ВременнаяРазницаЗнак,
		|	МИНИМУМ(УзлыКорректировки.ПостояннаяРазницаЗнак * ЕСТЬNULL(ВтТаблицаРешений.ПостояннаяРазницаЗнак, 1)) КАК ПостояннаяРазницаЗнак,
		|	(ВЫРАЗИТЬ(СУММА(ЕСТЬNULL(ВтТаблицаРешений.Стоимость, 0) * ЕСТЬNULL(ПеремещенияСписания.Количество, 0)) КАК ЧИСЛО(25, 10))) / УзлыКорректировки.Количество КАК Стоимость,
		|	(ВЫРАЗИТЬ(СУММА(ЕСТЬNULL(ВтТаблицаРешений.СтоимостьБезНДС, 0) * ЕСТЬNULL(ПеремещенияСписания.Количество, 0)) КАК ЧИСЛО(25, 10))) / УзлыКорректировки.Количество КАК СтоимостьБезНДС,
		|	(ВЫРАЗИТЬ(СУММА(ЕСТЬNULL(ВтТаблицаРешений.ПостояннаяРазница, 0) * ЕСТЬNULL(ПеремещенияСписания.Количество, 0)) КАК ЧИСЛО(25, 10))) / УзлыКорректировки.Количество КАК ПостояннаяРазница,
		|	(ВЫРАЗИТЬ(СУММА((ЕСТЬNULL(ВтТаблицаРешений.ВременнаяРазница, 0) + ВЫБОР
		|				КОГДА ПеремещенияСписания.КосвенныеЗатратыНУ
		|					ТОГДА ЕСТЬNULL(ВтТаблицаРешений.Стоимость, 0)
		|				ИНАЧЕ 0
		|			КОНЕЦ + ВЫБОР
		|				КОГДА ПеремещенияСписания.КосвенныеЗатратыНУ
		|					ТОГДА ЕСТЬNULL(ВтТаблицаРешений.СтоимостьДопРасходы, 0)
		|				ИНАЧЕ 0
		|			КОНЕЦ) * ЕСТЬNULL(ПеремещенияСписания.Количество, 0)) КАК ЧИСЛО(25, 10))) / УзлыКорректировки.Количество КАК ВременнаяРазница,
		|	(ВЫРАЗИТЬ(СУММА(ЕСТЬNULL(ВтТаблицаРешений.СтоимостьДопРасходы, 0) * ЕСТЬNULL(ПеремещенияСписания.Количество, 0)) КАК ЧИСЛО(25, 10))) / УзлыКорректировки.Количество КАК СтоимостьДопРасходы,
		|	(ВЫРАЗИТЬ(СУММА(ЕСТЬNULL(ВтТаблицаРешений.СтоимостьДопРасходыБезНДС, 0) * ЕСТЬNULL(ПеремещенияСписания.Количество, 0)) КАК ЧИСЛО(25, 10))) / УзлыКорректировки.Количество КАК СтоимостьДопРасходыБезНДС
		|ПОМЕСТИТЬ ВременнаяТаблицаРешений
		|ИЗ
		|	ВтУзлыКорректировки КАК УзлыКорректировки
		|		ЛЕВОЕ СОЕДИНЕНИЕ ВтПеремещенияСписания КАК ПеремещенияСписания
		|		ПО УзлыКорректировки.НомерУзла = ПеремещенияСписания.НомерУзлаПриемник
		|		ЛЕВОЕ СОЕДИНЕНИЕ ВтТаблицаРешений КАК ВтТаблицаРешений
		|		ПО (ПеремещенияСписания.НомерУзлаИсточник = ВтТаблицаРешений.НомерУзла)
		|ГДЕ
		|	УзлыКорректировки.Количество <> 0
		|	И ЕСТЬNULL(ВтТаблицаРешений.Стоимость, 0) * ЕСТЬNULL(ПеремещенияСписания.Количество, 0) > -999999999.999999999
		|	И ЕСТЬNULL(ВтТаблицаРешений.Стоимость, 0) * ЕСТЬNULL(ПеремещенияСписания.Количество, 0) < 999999999.999999999
		|
		|СГРУППИРОВАТЬ ПО
		|	УзлыКорректировки.НомерУзла,
		|	УзлыКорректировки.Количество
		|
		|ИНДЕКСИРОВАТЬ ПО
		|	НомерУзла
		|;
		|

См. также

Лучшие комментарии
1. rjhev korum (корум) 300 13.12.16 11:05 Сейчас в теме
Чудны творения твои, 1с...
Остальные комментарии
1. rjhev korum (корум) 300 13.12.16 11:05 Сейчас в теме
Чудны творения твои, 1с...
2. Роберт В е р т и н с к и й (v3rter) 13.12.16 15:33 Сейчас в теме
Плюсану, поскольку сталкивался и лечил такие проблемы в самопальных складских программах.
3. Allexey (alex_4x) 72 13.03.17 13:15 Сейчас в теме
В данном случае помогло, но это не факт, что всегда поможет.
Проблема может быть не только с длиной целой части, но и длиной дробной части.

Суть ошибки в том, что во время вычисления - SQL не может преобразовать тип внутри вычисления как правило умножения или деления. Решается не обязательно увеличением разрядности, достаточно ВЫРАЗИТЬ( Значение , ХХ,ХХ) использовать для всех вычисляемых аргументов в том числе внутри скобок. Тогда SQL не занимается самодеятельностью по выбору типа внутри самого вычисления и ошибка не происходит.

Ну примерно так:
Было:
 ВЫРАЗИТЬ(СУММА(ЕСТЬNULL(ВтТаблицаРешений.ПостояннаяРазница, 0) * ЕСТЬNULL(ПеремещенияСписания.Количество, 0)) КАК ЧИСЛО(23, 10)))

Будет
ВЫРАЗИТЬ 
СУММА(ВЫРАЗИТЬ ЕСТЬNULL(ПеремещенияСписания.Количество, 0) КАК ЧИСЛО(23, 10) *
 ВЫРАЗИТЬ ЕСТЬNULL(ПеремещенияСписания.Количество, 0) КАК ЧИСЛО(23, 10) )
КАК ЧИСЛО(23, 10)
4. Роберт В е р т и н с к и й (v3rter) 13.03.17 14:23 Сейчас в теме
При таких расчетах могут накапливаться и ошибки округления дробной части, здесь это нивелируется десятью знаками после запятой ЧИСЛО(..., 10). Попутно может всплыть проблема сравнения итогов с нулём или константой, так как из-за накопления ошибок округления дробной части выглядеть она будет как -0.0001 < x <0,0001