Ошибка при преобразовании типа данных float к decimal

I have a query I’m running in order to insert rows from one table to another. The issue I’m having is with a conversion from a FLOAT to a DECIMAL…

Here is the original query:

SELECT        FULL_ABBR, CAST(POST_PERIOD AS DATE) AS POST_PERIOD, GLETCODE, AMOUNT, GLEHSOURCE
FROM            (SELECT         
                 d.CL_CODE + '-' + d.DIV_CODE + '-' + d.PRD_CODE AS FULL_ABBR, 
                 h.GLEHPP + '01' AS POST_PERIOD, 
                 d.GLETCODE, 
                 d.GLETAMT * - 1 AS AMOUNT, 
                 h.GLEHSOURCE
                 FROM dbo.GLENTHDR AS h INNER JOIN
						(SELECT        
					 GLETXACT, 
					 RTRIM(CL_CODE) AS CL_CODE, 
					 RTRIM(DIV_CODE) AS DIV_CODE, 
					 RTRIM(PRD_CODE) AS PRD_CODE, 
					 GLETCODE, 
                    GLETAMT
                     FROM dbo.GLENTTRL AS sub
                          WHERE (PRD_CODE IS NOT NULL) AND (CL_CODE IS NOT NULL)) AS d 
							ON h.GLEHXACT = d.GLETXACT
							WHERE (h.GLEHPP IS NOT NULL) AND (SUBSTRING(d.GLETCODE, 4, 1) IN ('4', '5'))
                UNION ALL
                   SELECT        
                   CL_CODE + '-' + DIV_CODE + '-' + PRD_CODE AS FULL_ABBR, 
                   GLEHPP + '01' AS POST_PERIOD, 
                   GLETCODE, 
                  GLETAMT * - 1 AS AMOUNT, 
                   GLEHSOURCE
                   FROM            
						(SELECT LTRIM(RTRIM(SUBSTRING(d_1.GLETREM, 10, CHARINDEX('-', d_1.GLETREM, 11) - 10))) AS CL_CODE, 
                         LTRIM(RTRIM(SUBSTRING(d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 1, CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) - (CHARINDEX('-', d_1.GLETREM, 11) + 1)))) AS DIV_CODE, 
                         LTRIM(RTRIM(SUBSTRING(d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) + 1, CHARINDEX(' ', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) + 2) - (CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) + 1)))) AS PRD_CODE, 
                         h.GLEHPP, 
                         d_1.GLETCODE, 
                       d_1.GLETAMT, 
                         h.GLEHSOURCE
                            FROM dbo.GLENTHDR AS h INNER JOIN
								  (SELECT GLETXACT, GLETAMT, GLETREM, GLETCODE
									FROM dbo.GLENTTRL AS sub
									WHERE (PRD_CODE IS NULL) AND (CL_CODE IS NULL) AND (LEFT(GLETREM, 6) = 'Voided')) AS d_1 ON 
                                    h.GLEHXACT = d_1.GLETXACT
                                      WHERE (h.GLEHPP IS NOT NULL)) AS m) AS Ledger
WHERE (SUBSTRING(GLETCODE, 4, 1) IN ('4', '5'))

Of course, when I run the select, everything is fine. Once I incorporate the INSERT statement, I get an «Error converting data type vachar to numeric«, which I am assuming isa result of trying to insert a float into a
decimal field. So what I did was to add the following to each of the lines that contained the offending FLOAT field:

CONVERT(DECIMAL(18,2), GLETAMT)

So, my new query looks like this:

--INSERT INTO OLSON_BI.DBO.FACT_CLIENT_REVENUE
--(FULL_ABBR,
-- POST_PERIOD,
-- AMOUNT,
-- GLETCODE,
-- GLEHSOURCE
-- )
SELECT        FULL_ABBR, CAST(POST_PERIOD AS DATE) AS POST_PERIOD, GLETCODE, CONVERT(DECIMAL(18,2),AMOUNT), GLEHSOURCE
FROM            (SELECT         
                 d.CL_CODE + '-' + d.DIV_CODE + '-' + d.PRD_CODE AS FULL_ABBR, 
                 h.GLEHPP + '01' AS POST_PERIOD, 
                 d.GLETCODE, 
                CONVERT(DECIMAL(18,2), d.GLETAMT * -1) AS AMOUNT, 
                 h.GLEHSOURCE
                 FROM dbo.GLENTHDR AS h INNER JOIN
						(SELECT        
					 GLETXACT, 
					 RTRIM(CL_CODE) AS CL_CODE, 
					 RTRIM(DIV_CODE) AS DIV_CODE, 
					 RTRIM(PRD_CODE) AS PRD_CODE, 
					 GLETCODE, 
                     CONVERT(DECIMAL(18,2), GLETAMT)
                     FROM dbo.GLENTTRL AS sub
                          WHERE (PRD_CODE IS NOT NULL) AND (CL_CODE IS NOT NULL)) AS d 
							ON h.GLEHXACT = d.GLETXACT
							WHERE (h.GLEHPP IS NOT NULL) AND (SUBSTRING(d.GLETCODE, 4, 1) IN ('4', '5'))
                UNION ALL
                   SELECT        
                   CL_CODE + '-' + DIV_CODE + '-' + PRD_CODE AS FULL_ABBR, 
                   GLEHPP + '01' AS POST_PERIOD, 
                   GLETCODE, 
                   CONVERT(DECIMAL(18,2), GLETAMT * -1) AS AMOUNT, 
                   GLEHSOURCE
                   FROM            
						(SELECT LTRIM(RTRIM(SUBSTRING(d_1.GLETREM, 10, CHARINDEX('-', d_1.GLETREM, 11) - 10))) AS CL_CODE, 
                         LTRIM(RTRIM(SUBSTRING(d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 1, CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) - (CHARINDEX('-', d_1.GLETREM, 11) + 1)))) AS DIV_CODE, 
                         LTRIM(RTRIM(SUBSTRING(d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) + 1, CHARINDEX(' ', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) + 2) - (CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) + 1)))) AS PRD_CODE, 
                         h.GLEHPP, 
                         d_1.GLETCODE, 
                         CONVERT(DECIMAL(18,2), d_1.GLETAMT), 
                         h.GLEHSOURCE
                            FROM dbo.GLENTHDR AS h INNER JOIN
								  (SELECT GLETXACT, CONVERT(DECIMAL(18,2), GLETAMT), GLETREM, GLETCODE
									FROM dbo.GLENTTRL AS sub
									WHERE (PRD_CODE IS NULL) AND (CL_CODE IS NULL) AND (LEFT(GLETREM, 6) = 'Voided')) AS d_1 ON 
                                    h.GLEHXACT = d_1.GLETXACT
                                      WHERE (h.GLEHPP IS NOT NULL)) AS m) AS Ledger
WHERE (SUBSTRING(GLETCODE, 4, 1) IN ('4', '5'))

But now when I run just the select, I get a whole new crop of errors that make no sense. All I added were some CONVERT statements:

Msg 8155, Level 16, State 2, Line 25
No column name was specified for column 6 of 'd'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'GLETAMT'.
Msg 8155, Level 16, State 2, Line 45
No column name was specified for column 2 of 'd_1'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'GLETAMT'.
Msg 8155, Level 16, State 2, Line 47
No column name was specified for column 6 of 'm'.
Msg 207, Level 16, State 1, Line 32
Invalid column name 'GLETAMT'.

I have no idea why adding those CONVERT’s would suddenly cause these types of errors.

I really do not want to use a FLOAT in my destination. I could always create a calculated column on my target table, and then convert that way, but I’d rather figure out exactly the issue is with my query.

Any help is greatly appreciated!!

A. M. Robinson

I have a query I’m running in order to insert rows from one table to another. The issue I’m having is with a conversion from a FLOAT to a DECIMAL…

Here is the original query:

SELECT        FULL_ABBR, CAST(POST_PERIOD AS DATE) AS POST_PERIOD, GLETCODE, AMOUNT, GLEHSOURCE
FROM            (SELECT         
                 d.CL_CODE + '-' + d.DIV_CODE + '-' + d.PRD_CODE AS FULL_ABBR, 
                 h.GLEHPP + '01' AS POST_PERIOD, 
                 d.GLETCODE, 
                 d.GLETAMT * - 1 AS AMOUNT, 
                 h.GLEHSOURCE
                 FROM dbo.GLENTHDR AS h INNER JOIN
						(SELECT        
					 GLETXACT, 
					 RTRIM(CL_CODE) AS CL_CODE, 
					 RTRIM(DIV_CODE) AS DIV_CODE, 
					 RTRIM(PRD_CODE) AS PRD_CODE, 
					 GLETCODE, 
                    GLETAMT
                     FROM dbo.GLENTTRL AS sub
                          WHERE (PRD_CODE IS NOT NULL) AND (CL_CODE IS NOT NULL)) AS d 
							ON h.GLEHXACT = d.GLETXACT
							WHERE (h.GLEHPP IS NOT NULL) AND (SUBSTRING(d.GLETCODE, 4, 1) IN ('4', '5'))
                UNION ALL
                   SELECT        
                   CL_CODE + '-' + DIV_CODE + '-' + PRD_CODE AS FULL_ABBR, 
                   GLEHPP + '01' AS POST_PERIOD, 
                   GLETCODE, 
                  GLETAMT * - 1 AS AMOUNT, 
                   GLEHSOURCE
                   FROM            
						(SELECT LTRIM(RTRIM(SUBSTRING(d_1.GLETREM, 10, CHARINDEX('-', d_1.GLETREM, 11) - 10))) AS CL_CODE, 
                         LTRIM(RTRIM(SUBSTRING(d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 1, CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) - (CHARINDEX('-', d_1.GLETREM, 11) + 1)))) AS DIV_CODE, 
                         LTRIM(RTRIM(SUBSTRING(d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) + 1, CHARINDEX(' ', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) + 2) - (CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) + 1)))) AS PRD_CODE, 
                         h.GLEHPP, 
                         d_1.GLETCODE, 
                       d_1.GLETAMT, 
                         h.GLEHSOURCE
                            FROM dbo.GLENTHDR AS h INNER JOIN
								  (SELECT GLETXACT, GLETAMT, GLETREM, GLETCODE
									FROM dbo.GLENTTRL AS sub
									WHERE (PRD_CODE IS NULL) AND (CL_CODE IS NULL) AND (LEFT(GLETREM, 6) = 'Voided')) AS d_1 ON 
                                    h.GLEHXACT = d_1.GLETXACT
                                      WHERE (h.GLEHPP IS NOT NULL)) AS m) AS Ledger
WHERE (SUBSTRING(GLETCODE, 4, 1) IN ('4', '5'))

Of course, when I run the select, everything is fine. Once I incorporate the INSERT statement, I get an «Error converting data type vachar to numeric«, which I am assuming isa result of trying to insert a float into a
decimal field. So what I did was to add the following to each of the lines that contained the offending FLOAT field:

CONVERT(DECIMAL(18,2), GLETAMT)

So, my new query looks like this:

--INSERT INTO OLSON_BI.DBO.FACT_CLIENT_REVENUE
--(FULL_ABBR,
-- POST_PERIOD,
-- AMOUNT,
-- GLETCODE,
-- GLEHSOURCE
-- )
SELECT        FULL_ABBR, CAST(POST_PERIOD AS DATE) AS POST_PERIOD, GLETCODE, CONVERT(DECIMAL(18,2),AMOUNT), GLEHSOURCE
FROM            (SELECT         
                 d.CL_CODE + '-' + d.DIV_CODE + '-' + d.PRD_CODE AS FULL_ABBR, 
                 h.GLEHPP + '01' AS POST_PERIOD, 
                 d.GLETCODE, 
                CONVERT(DECIMAL(18,2), d.GLETAMT * -1) AS AMOUNT, 
                 h.GLEHSOURCE
                 FROM dbo.GLENTHDR AS h INNER JOIN
						(SELECT        
					 GLETXACT, 
					 RTRIM(CL_CODE) AS CL_CODE, 
					 RTRIM(DIV_CODE) AS DIV_CODE, 
					 RTRIM(PRD_CODE) AS PRD_CODE, 
					 GLETCODE, 
                     CONVERT(DECIMAL(18,2), GLETAMT)
                     FROM dbo.GLENTTRL AS sub
                          WHERE (PRD_CODE IS NOT NULL) AND (CL_CODE IS NOT NULL)) AS d 
							ON h.GLEHXACT = d.GLETXACT
							WHERE (h.GLEHPP IS NOT NULL) AND (SUBSTRING(d.GLETCODE, 4, 1) IN ('4', '5'))
                UNION ALL
                   SELECT        
                   CL_CODE + '-' + DIV_CODE + '-' + PRD_CODE AS FULL_ABBR, 
                   GLEHPP + '01' AS POST_PERIOD, 
                   GLETCODE, 
                   CONVERT(DECIMAL(18,2), GLETAMT * -1) AS AMOUNT, 
                   GLEHSOURCE
                   FROM            
						(SELECT LTRIM(RTRIM(SUBSTRING(d_1.GLETREM, 10, CHARINDEX('-', d_1.GLETREM, 11) - 10))) AS CL_CODE, 
                         LTRIM(RTRIM(SUBSTRING(d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 1, CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) - (CHARINDEX('-', d_1.GLETREM, 11) + 1)))) AS DIV_CODE, 
                         LTRIM(RTRIM(SUBSTRING(d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) + 1, CHARINDEX(' ', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) + 2) - (CHARINDEX('-', d_1.GLETREM, CHARINDEX('-', d_1.GLETREM, 11) + 2) + 1)))) AS PRD_CODE, 
                         h.GLEHPP, 
                         d_1.GLETCODE, 
                         CONVERT(DECIMAL(18,2), d_1.GLETAMT), 
                         h.GLEHSOURCE
                            FROM dbo.GLENTHDR AS h INNER JOIN
								  (SELECT GLETXACT, CONVERT(DECIMAL(18,2), GLETAMT), GLETREM, GLETCODE
									FROM dbo.GLENTTRL AS sub
									WHERE (PRD_CODE IS NULL) AND (CL_CODE IS NULL) AND (LEFT(GLETREM, 6) = 'Voided')) AS d_1 ON 
                                    h.GLEHXACT = d_1.GLETXACT
                                      WHERE (h.GLEHPP IS NOT NULL)) AS m) AS Ledger
WHERE (SUBSTRING(GLETCODE, 4, 1) IN ('4', '5'))

But now when I run just the select, I get a whole new crop of errors that make no sense. All I added were some CONVERT statements:

Msg 8155, Level 16, State 2, Line 25
No column name was specified for column 6 of 'd'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'GLETAMT'.
Msg 8155, Level 16, State 2, Line 45
No column name was specified for column 2 of 'd_1'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'GLETAMT'.
Msg 8155, Level 16, State 2, Line 47
No column name was specified for column 6 of 'm'.
Msg 207, Level 16, State 1, Line 32
Invalid column name 'GLETAMT'.

I have no idea why adding those CONVERT’s would suddenly cause these types of errors.

I really do not want to use a FLOAT in my destination. I could always create a calculated column on my target table, and then convert that way, but I’d rather figure out exactly the issue is with my query.

Any help is greatly appreciated!!

A. M. Robinson

Рассмотрим такой пример: 0.1 + 0.2 должно давать 0.3, правильно? Но проверьте вывод при использовании типа данных SQL FLOAT.

DECLARE @f1 FLOAT = 0.1

DECLARE @f2 FLOAT = 0.2

SELECT CASE WHEN @f1 + @f2 = .3 THEN 1 ELSE 0 END

Правильным результатом является 1. Но посмотрите на рисунок 1.

Рис.1 Использование SQL FLOAT, 0.1 + 0.2 не равно 0.3!

Я привлек ваше внимание? Надеюсь, что так. Это довольно страшно зависеть от системы, которая не дает правильных результатов математических операций. Но эта статья поможет вам избежать их.

Есть над чем поработать. Нужно начать с того, что представляет собой тип данных FLOAT.

Что это за тип данных SQL FLOAT?

Тип данных SQL FLOAT является приближенным числовым типом данных, используемым для чисел с плавающей точкой. Они могут хранить очень большие или очень маленькие числа. Они также используются для вычислений, которые требуют быстрой обработки.

Все это сказывается на потере точности. Кроме того, вы не можете сказать, где будет помещена десятичная точка после вычислений — она плавает. Между тем, точные числа типа DECIMAL будут иметь фиксированную позицию десятичной точки.

Как объявить тип данных SQL FLOAT

Синтаксис — FLOAT[(n)], где n — число бит, используемых для хранения мантиссы числа с плавающей точкой в научной нотации. Этим также определяется точность и размер хранилища. Возможными значениями для n являются числа от 1 до 53. Заметим, что параметр n является необязательным.

Пример:

DECLARE @floatValue1 FLOAT;   -- переменная Float без числа бит

DECLARE @floatValue2 FLOAT(3) -- переменная Float с 3 битами 

Если n не указывается, по умолчанию принимается 53. Это также является максимальным значением. Кроме того, FLOAT(53) — это число двойной точности с плавающей точкой или binary64. Вместо указания FLOAT(53), вы также можете объявить его как DOUBLE PRECISION.

Следующие 3 объявления функционально эквивалентны:

DECLARE @double1 FLOAT(53); 

DECLARE @double2 FLOAT;

DECLARE @double3 DOUBLE PRECISION;

В таблице показано число бит и соответствующий размер хранилища.

SQL FLOAT и REAL — это одно и то же?

REAL — это FLOAT(24). Это также называется одинарной точностью или binary32.

Почему это важно знать

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

FLOAT является приближенным числовым типом. DECIMAL — это точный числовой тип. Вот сводка различий:

Мы уже видели, как вычисление числа FLOAT может давать странные результаты. Если изменить тип данных на DECIMAL, то результат будет корректным:

DECLARE @d1 DECIMAL(2,1) = 0.1

DECLARE @d2 DECIMAL(2,1) = 0.2

SELECT CASE WHEN @d1 + @d2 = 0.3 THEN 1 ELSE 0 END 

Использование оператора «не равно» также вызывает проблемы. Проверьте нижеприведенный цикл.

DECLARE @floatValue FLOAT(1) = 0.0

WHILE @floatValue <> 5.0

BEGIN

	PRINT @floatValue;

	SET @floatValue += 0.1;

END 

Что вы думаете? Посмотрите рисунок 2 ниже.

Рис.2 Использование FLOAT в качестве счетчика приводит к бесконечному циклу.

Бум! Бесконечный цикл! Условие неравенства всегда будет true. Поэтому логично изменить тип данных на DECIMAL.

DECLARE @decimalValue DECIMAL(2,1) = 0.0

WHILE @decimalValue <> 5.0

BEGIN

	PRINT @decimalValue;

	SET @decimalValue += 0.1;

END

Вышеприведенный код будет четко останавливаться, когда @decimalValue равна 5.0. Посмотрите на рисунке 3 ниже.

Рис.3 Цикл останавливается при использовании DECIMAL, когда условие неравенства больше не true.

Отлично! Но если вы настаиваете на FLOAT, следующий код будет исправно работать, не входя в бесконечный цикл.

DECLARE @floatValue FLOAT(1) = 0.0

WHILE @floatValue < 5.0

BEGIN

	PRINT @floatValue;

	SET @floatValue += 0.1;

END

Между тем, округление тоже отказывает. Посмотрите следующий код:

DECLARE @value FLOAT(2) = 1.15

SELECT ROUND(@value, 1)  -- Результатом будет 1.1

Вместо 1.20 этот код дает 1.1. Но если использовать DECIMAL, результат будет правильным.

DECLARE @value DECIMAL(3,2) = 1.15

SELECT ROUND(@value, 1)  -- Будет получено 1.2 или 1.20

Когда FLOAT дает правильный результат, а DECIMAL — нет

Разве точные числа НЕ всегда точны? Для воспроизведения проблемы мы будем использовать вычисления, а затем обращать их. Сначала подготовим данные.

CREATE TABLE ExactNumerics1

(

	fixed1 DECIMAL(8,4),

	fixed2 DECIMAL(8,4),

	fixed3 DECIMAL(8,4),

	calcValue1 AS fixed3 / fixed1 * fixed2

)

GO

INSERT INTO ExactNumerics1

(fixed1,fixed2,fixed3)

VALUES

(54,0.03,1*54/0.03)

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

Теперь для сравнения с FLOAT создадим подобную таблицу и данные.

CREATE TABLE ApproxNumerics1

(

	float1 FLOAT(2),

	float2 FLOAT(2),

	float3 FLOAT(2),

	calcValue1 AS float3 / float1 * float2 

)

INSERT INTO ApproxNumerics1

(float1, float2, float3)

VALUES

(54,0.03,1*54/0.03)

Теперь запрос.

SELECT * FROM ApproxNumerics1

SELECT * FROM ExactNumerics1

Результаты? Смотрите рисунок 4.

Рис.4 Обратное вычисление показывает, что FLOAT дает правильный результат, а DECIMAL — нет.

Что произошло? FLOAT дал правильный ответ, а DECIMAL — нет. Что-то сделано неверно.

Опять неявное преобразование

Неявное преобразование происходит потому, что SQL прощает. Когда при вычислении используются данные разных типов, SQL Server пытается преобразовать это у нас за спиной, выполняя неявное преобразование.

Действительно ли было выполнено преобразование? Помимо прочего, каждый столбец в таблице ExactNumerics1 имеет тип DECIMAL.

Давайте проверим структуру таблицы ExactNumerics1 в SQL Server Management Studio:

Рис.5 Вычисляемый столбец имеет тип DECIMAL(30,17), а не DECIMAL(8,4).

Обратите внимание на красный прямоугольник на рисунке 5. Вычисляемый столбец имеет тип DECIMAL(30,17), а не DECIMAL(8,4). Согласно официальной документации, два столбца DECIMAL с различными точностью и масштабом являются двумя различными типами данных. Посмотрите здесь. Из-за разницы требуется преобразование. Поэтому происходит неявное преобразование.

Что если они различны, и произошло неявное преобразование?

Опять таки на основании официальной документации при неявном преобразовании может произойти потеря точности или масштаба. Таким образом, требуется явное преобразование CAST.

Здесь просто произошла некоторая потеря. Если вычисляемый столбец имеет также тип DECIMAL(8,4), неявное преобразование не происходит.

Чтобы избежать неявного преобразования, следуйте официальной документации. Структура таблицы должна быть подобна следующей:

CREATE TABLE ExactNumerics2

(

	fixed1 DECIMAL(8,4),

	fixed2 DECIMAL(8,4),

	fixed3 DECIMAL(8,4),

	calcValue1 AS CAST(fixed3 / fixed1 * fixed2 AS DECIMAL(8,4)) -- явный CAST

)

Явный CAST в вычисляемом столбце гарантирует согласованность типов данных. Если в таблицу такой структуры вставить те же данные, результат будет правильным. Посмотрите новый вывод на рисунке 6.

Рис.6 Теперь результаты для FLOAT и DECIMAL одинаковы.

Итак, точные числа могут оказаться неточными, если имеет место неявное преобразование между 2 и более значениями DECIMAL.

Почему эти знания важны

Они дают вам идеи относительно ваших таблиц и переменных. Более того, неявное преобразование может свести с ума даже точные числа. Поэтому точно определяйте точность и масштаб, и согласовывайте их с вашими вычислениями.

Следует ли использовать SQL FLOAT для финансовых данных?

При вычислении процентов секторов на круговой диаграмме сумма должна составлять 100%. Итоговые данные и детализированные отчеты также должны быть согласованы. Если точность результатов является ключевым моментом, приближенные типы данных, подобные FLOAT, не должны использоваться. Для этого логично выбирать DECIMAL.

Но вопросы остаются.

Когда следует использовать FLOAT?

Используйте FLOAT для данных, которые требуют астрономических значений типа расстояния между галактиками. При этом тип данных DECIMAL будет приводить к арифметическому переполнению для данных такого типа. Небольшие значения типа диаметра атомного ядра также подходят для использования FLOAT. Научные данные и другие значения, которые не требуют точности могут также с пользой использовать FLOAT.

Почему важно это знать

Мы не говорим, что FLOAT — плохой, а DECIMAL — хороший и наоборот. Знание правильного использования каждого типа даст вам и вашим пользователям надежные результаты. Ну, вы же хотите сделать ваших пользователей счастливыми, правильно?

Заключение

К концу дня все мы хотим сделать нашу работу, и сделать хорошо. Математика всегда будет частью нашей работы. И знание правильных числовых типов данных также поможет нам в этом. Это нетрудно, если вы знаете, что делаете.

• January 31, 2008 at 9:59 am

  #123408

  every time i try this conversion by simply changing the datatype value from float to

  decimal it errors out with an arithmetic overflow error.

  is there any easy way to do this? maybe some new feature/function in sql 2005 that

  i don’t know about?

• Adam Haines

  SSC-Insane

  Points: 23217

  January 31, 2008 at 3:04 pm

  #773966

  You need to set your decimal percision correctly otherwise it will overflow as a general rule of thumb, you can count the digits to determine what you percision should be and your scale is the number of decimal places.

  e.g.

  declare @var float

  set @var = 132456789.12

  select cast(@var as decimal(11,2))

• simsql

  Ten Centuries

  Points: 1389

  February 1, 2008 at 1:13 pm

  #774422

  thanks for the reply…

  unfortunately it still errors out with the arithmetic overflow error.

  this is really unusual.

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  February 1, 2008 at 1:55 pm

  #774448

  simsql (2/1/2008)

  ---

  thanks for the reply…

  unfortunately it still errors out with the arithmetic overflow error.

  this is really frustrating.

  Don’t be shy about posting the code that you want help with.

• simsql

  Ten Centuries

  Points: 1389

  February 1, 2008 at 2:18 pm

  #774459

  here’s the situation.

  i have a few million records that currently exist as a float which i need converted to

  decimal (25, 10) datatype, but whenever converted from Management Studio or

  Enterprise Manager it errors out.

  the reason i mention the 2 is because i’m left having to migrate the data from

  one sql 2000 table to a sql 2005 table. so naturally i’ve tried the conversions

  from both ends.

  i move the data over as a float, and try to convert to decimal. i have no idea how to

  do this while importing.

• simsql

  Ten Centuries

  Points: 1389

  February 1, 2008 at 2:35 pm

  #774468

  quick recap

  declare @var float

  set @var = 132456789.12

  select cast(@var as decimal(11,2))

  i’m setting it up like this though i doubt it will work.

  declare @var float

  set @var = (table.column)

  select cast(@var as decimal(11,2))

  ideas?

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  February 1, 2008 at 2:58 pm

  #774476

  This works Ok for me in SQL Server 2000 and 2005:

  declare @var float

  set @var = 132456789.12552346

  select [Decimal] =cast(@var as decimal(11,2)), [Float] =@var

  Results:

  Decimal Float

  ------------- -----------------------------------------------------

  132456789.13 132456789.12552346

  (1 row(s) affected)

• simsql

  Ten Centuries

  Points: 1389

  February 1, 2008 at 3:06 pm

  #774482

  according to this example though…

  it looks as though it specifies only one record.

  set @var = 132456789.12552346

  thats fine cause i can concat the entire script

  against every records (few million rows), then run

  the bulk script, but is there a way to set the @var

  to the entire column, and not per row?

  e.g.

  set @var = mytabe.column_name

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  February 1, 2008 at 3:11 pm

  #774483

  You said you had a float to decimal converion that errors out. Maybe you could start by posting an example of that?

• simsql

  Ten Centuries

  Points: 1389

  February 1, 2008 at 3:22 pm

  #774486

  first… thanks for all the good feedback. it’s really appreciated.

  the conversion that i did was using the gui. where you right click the table (FinMain), and

  select ‘design’ then you get all the columns, and their datatypes.

  i have a column: ValData float

  so i simply hit the drop down and selected Decimal (25, 10) for ValData

  then i clicked save.

  get the error: «…arithmetic overflow» it’s been suggested that cast and convert

  is all thats needed, but i’m not convinced.

  thoughts?

  declare @var float

  set @var = (select ValData from FinMain)

  select [Decimal] = cast(@var as decimal(25,10)), [Float] =@var

• Adam Haines

  SSC-Insane

  Points: 23217

  February 2, 2008 at 5:52 pm

  #774553

  You need to use the code like this:

  SELECT

  CAST(MyFloatColumn AS DECIMAL(11,2)) AS [Decimal],

  MyFloatColumn AS [Float]

  FROM MyTable

• neotokyo

  SSCertifiable

  Points: 7239

  February 2, 2008 at 7:55 pm

  #774554

  this seems to be a problem from time to time….

  i’ve done conversions which shouldn’t be too difficult; yet for some reason will

  still give me trouble. nice to know threads like this are still active.

  any information about this sort of this is helpful.

  _________________________

• simsql

  Ten Centuries

  Points: 1389

  February 2, 2008 at 8:42 pm

  #774555

  adam… thanks for the samples

  so far so good…. running past a couple million rows, and no issues…

  i’ll post an update when it’s finished.

  again… thanks 🙂

• simsql

  Ten Centuries

  Points: 1389

  February 2, 2008 at 8:47 pm

  #774556

  ahh well… it was a pretty good run.

  still get this error though:

  Msg 8115, Level 16, State 6, Line 1

  Arithmetic overflow error converting float to data type numeric.

  i think cause there are a few hundred float values of something like this:

  5.87348091654376E+15

  8.56784091067654E+15

  7.58674490270345E+15

  3.56734927376573E+15

  :unsure: must be a way to get this done… still scratching my head

  on this one.

• FrankMeng021

  SSC Veteran

  Points: 255

  February 2, 2008 at 8:59 pm

  #774557

  so i simply hit the drop down and selected Decimal (25, 10) for ValData

  ———-

  make sure 25 is large enough. you can try Decimal (38, 10)

Viewing 15 posts — 1 through 15 (of 31 total)

Проблема здесь двоякая:

Вы сохраняете числовые значения в виде текста Вы не проверяете ввод этого столбца текста

Ваш комментарий говорит, что ваше значение хранится следующим образом: - 5.862, а не так: -5.862.

Итак, вот некоторые SQL, чтобы опробовать:

select convert(decimal, '-5.862')
select convert(decimal, '- 5.862')

обратите внимание, что первый «работает» (но позволяет вернуться к этому), второй — это исключение.

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

НЕ допускайте попадания плохих данных в таблицу. Это не спасает какую-либо работу. Валидация и исправления, которые необходимо выполнить для того, чтобы ваши существующие данные могли работать, намного лучше размещены в точке, где данные поступают в вашу таблицу.

«Быстрое исправление», похожее на то, чтобы просто держать ваши штаны теплыми, таково:

select convert(decimal, replace('- 5.862', ' ', ''))

Но это просто откладывает проблему, пока кто-то не вступит в это:

-5,862
twenty
!27

то есть. что-то еще, что невозможно преобразовать. Поскольку вы храните их в виде текста, вам необходимо выполнить всю работу по исправлению плохих значений, где вы используете их, а не там, где они происходят.

Тем не менее, есть еще одна проблема, предложенная другими здесь, и это то, что DECIMAL по умолчанию не допускает никаких цифр после десятичной точки, поэтому тот, который работает выше, производит только -6.

Чтобы исправить эту проблему, укажите точность:

select convert(decimal(18, 5), '-5.862')

Однако, и я не могу сказать это достаточно сильно: не храните числовые значения в виде текста. Это катастрофа, ожидающая того, что вы уже выяснили.