September 4, 2012 at 7:30 am
Hi there, I hope your help.
This is the error in my query:
[Err] 22003 - [SQL Server] Arithmetic overflow error converting numeric to data type numeric.
If tried [%TAX] DECIMAL (10, 2) I have the correct output, but I need 4 decimal in my output.
Can you help me?
Thanks in advance
MATNUMB-VER-EXENUMB-VER-RIC%TAX
a476427228.1515,17%
b9241314323.1515,50%
c7781510878.1413,98%
d7480019601.2626,20%
e877618297.099,45%
f540509234.1717,08%
g7673920936.2727,28%
h302148695.2928,78%
i13903813388.109,63%
l7108713514.1919,01%
m5499210440.1918,98%
Tot8065511365342.0016,93%
CREATE TABLE TestTable (
[MAT] VARCHAR (100),
[NUMB-VER-EXE] INT,
[NUMB-VER-RIC] INT,
[%TAX] DECIMAL (10, 4)
);
INSERT INTO TestTable (
[MAT],
[NUMB-VER-EXE],
[NUMB-VER-RIC] INT,
[%TAX]
) SELECT
[MAT],
[NUMB-VER-EXE],
[NUMB-VER-RIC],
[NUMB-VER-RIC] / (
CAST (
[NUMB-VER-EXE] AS DECIMAL (10, 4)
)
) AS [%TAX]
FROM
(
SELECT MAT,
SUM (
CASE
WHEN (
[RISC-MOT] = '1'
OR [RISC-MOT] = '2'
OR [RISC-MOT] = '3'
OR [RISC-MOT] = '4'
)
AND [DATE-V] IS NOT NULL THEN
1
ELSE
0
END
) AS [NUMB-VER-EXE],
SUM (
CASE
WHEN (
[COD] IN (
'A01',
'A02',
'A06',
'A07',
'A08',
'A47'
)
)
)
AND [DATE-V] IS NOT NULL THEN
1
ELSE
0
END
) AS [NUMB-VER-RIC]
FROM
dbo_40
GROUP BY
MAT
) AS SubQs;
SELECT
[MAT],
[NUMB-VER-EXE],
[NUMB-VER-RIC],
[%TAX]
FROM
(
SELECT
[MAT],
[NUMB-VER-EXE],
[NUMB-VER-RIC],
[%TAX]
FROM
TestTable
UNION
SELECT
COALESCE ([MAT], 'Tot') AS [MAT],
SUM (
[NUMB-VER-EXE]
),
SUM (
[NUMB-VER-RIC]
),
SUM ([%TAX])
FROM
TestTable
GROUP BY
(mat) WITH ROLLUP
) q
ORDER BY
CASE (MAT)
WHEN 'Tot' THEN
1
END;
DROP TABLE TestTable;
September 4, 2012 at 7:44 am
Please include definition of table "dbo_40" and sample data for it that replicates the issue you are facing, see this article for how --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D
Here's the code you posted but formatted to be easier on the eyes (imo).
IF object_id('TestTable') IS NOT NULL
BEGIN
DROP TABLE TestTable;
END;
CREATE TABLE TestTable ([MAT] VARCHAR(100), [NUMB-VER-EXE] INT, [NUMB-VER-RIC] INT, [%TAX] DECIMAL(10, 4));
INSERT INTO TestTable ([MAT], [NUMB-VER-EXE], [NUMB-VER-RIC], [%TAX])
SELECT [MAT], [NUMB-VER-EXE], [NUMB-VER-RIC], [NUMB-VER-RIC] / (CAST([NUMB-VER-EXE] AS DECIMAL(10, 4))) AS [%TAX]
FROM (SELECT MAT, SUM(CASE WHEN ([RISC-MOT] = '1' OR [RISC-MOT] = '2' OR [RISC-MOT] = '3' OR [RISC-MOT] = '4') AND [DATE-V] IS NOT NULL
THEN 1
ELSE 0 END) AS [NUMB-VER-EXE],
SUM(CASE WHEN ([COD] IN ('A01', 'A02', 'A06', 'A07', 'A08', 'A47')) AND [DATE-V] IS NOT NULL
THEN 1
ELSE 0 END) AS [NUMB-VER-RIC]
FROM dbo_40
GROUP BY MAT) AS SubQs;
SELECT [MAT], [NUMB-VER-EXE], [NUMB-VER-RIC], [%TAX]
FROM (SELECT [MAT], [NUMB-VER-EXE], [NUMB-VER-RIC], [%TAX]
FROM TestTable
UNION
SELECT COALESCE([MAT], 'Tot') AS [MAT], SUM([NUMB-VER-EXE]), SUM([NUMB-VER-RIC]), SUM([%TAX])
FROM TestTable
GROUP BY (mat) WITH ROLLUP
) q
ORDER BY CASE (MAT) WHEN 'Tot' THEN 1 END;
September 4, 2012 at 9:37 am
September 4, 2012 at 9:40 am
Luis Cazares (9/4/2012)
Have you tried using DECIMAL(12,4)?
thank you for help now working !!!!
September 4, 2012 at 9:42 am
cms9651 (9/4/2012)
Luis Cazares (9/4/2012)
Have you tried using DECIMAL(12,4)?thank you for help now working !!!!
Do you know why did it worked? It's important for you to understand what happened there.
September 4, 2012 at 10:21 am
Luis Cazares (9/4/2012)
cms9651 (9/4/2012)
Luis Cazares (9/4/2012)
Have you tried using DECIMAL(12,4)?thank you for help now working !!!!
Do you know why did it worked? It's important for you to understand what happened there.
Explain me please-
September 4, 2012 at 11:13 am
It's explained in this article: http://msdn.microsoft.com/en-us/library/ms187746(v=SQL.105).aspx
However, let me try to explain it, but read the article as well.
You had a decimal(10,2) value which can contain any value with two decimals from -99,999,999.99 to 99,999,999.99.
When you changed the datatype to decimal(10,4) you allowed more decimals but limited your range of values which is now -999,999.9999 to 999,999.9999.
Some value was over one million and generated the arithmetic overflow. You should be aware of this when defining the data types because you don't want to use more space than needed, but you don't want to find yourself with overflow problems.
I hope I was clear enough.
September 4, 2012 at 11:15 am
Luis Cazares (9/4/2012)
It's explained in this article: http://msdn.microsoft.com/en-us/library/ms187746(v=SQL.105).aspxHowever, let me try to explain it, but read the article as well.
You had a decimal(10,2) value which can contain any value with two decimals from -99,999,999.99 to 99,999,999.99.
When you changed the datatype to decimal(10,4) you allowed more decimals but limited your range of values which is now -999,999.9999 to 999,999.9999.
Some value was over one million and generated the arithmetic overflow. You should be aware of this when defining the data types because you don't want to use more space than needed, but you don't want to find yourself with overflow problems.
I hope I was clear enough.
Very , very nice your explanation and article msdn!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply