I obtain on my SQL Server 2005 an error
Server: Msg 8115,
Arithmetic overflow error converting numeric to data type numeric.
When I try to insert a value = X
(DECIMAL(3,2)) * Y
(INT)
in a column MYCOLUMN
typed DECIMAL (10,3)
How to workaround this problem?
UPDATE
some code bellow to understand the situation
-- ============================================================
-- Table : GP_WGENTH
-- ============================================================
CREATE TABLE #GP_WGENTH
(
KEYINTERNAL CHAR(32) NOT NULL,
KEYHOUR INT NOT NULL,
FIRM CHAR(5) NOT NULL,
CENTRE CHAR(2) NOT NULL,
SERVICE CHAR(6) NOT NULL,
AGENT CHAR(5) NOT NULL,
VERSION VARCHAR(3) NOT NULL,
DATE_ROULEMENT DATETIME NOT NULL,
SERVICEINSTANCE SMALLINT NOT NULL,
TYPEHOUR CHAR(9) NOT NULL,
QUANTITEFIXE DECIMAL(12,2) NULL ,
TYPECMP INT NULL ,
PERCENTCALC DECIMAL(3,2) NULL
)
-- ============================================================
-- Table : GP_WGENSVCTH
-- ============================================================
CREATE TABLE #GP_WGENSVCTH
(
KEYINTERNAL CHAR(32) NOT NULL,
KEYHOUR INT NOT NULL,
FIRM CHAR(5) NOT NULL,
CENTRE CHAR(2) NOT NULL,
SERVICE CHAR(6) NOT NULL,
AGENT CHAR(5) NOT NULL,
VERSION VARCHAR(3) NOT NULL,
DATE_ROULEMENT DATETIME NOT NULL,
SERVICEINSTANCE SMALLINT NOT NULL,
TYPEHOUR CHAR(9) NOT NULL,
VALUE DECIMAL(10,3) NOT NULL
)
GO
INSERT INTO #GP_WGENSVCTH
(KEYINTERNAL,
KEYHOUR,
FIRM,
CENTRE,
SERVICE,
AGENT,
VERSION,
DATE_ROULEMENT,
SERVICEINSTANCE,
TYPEHOUR,
VALUE)
SELECT WTH.KEYINTERNAL,
WTH.KEYHOUR,
WTH.FIRM,
WTH.CENTRE,
WTH.SERVICE,
WTH.AGENT,
WTH.VERSION,
WTH.DATE_ROULEMENT,
WTH.SERVICEINSTANCE,
WTH.TYPEHOUR,
WTH.PERCENTCALC * SUM(SC.LENGTH) -- !!! BOOOM !!!!!!!!! HERE
FROM #GP_WGENTH WTH
INNER JOIN GP_SERVICE_HABILLAGE_COMPONENT SC
ON WTH.FIRM = SC.FIRM
AND WTH.CENTRE = SC.CENTRE
AND WTH.SERVICE = SC.SERVICE
AND WTH.VERSION = SC.VERSION
AND WTH.TYPECMP = SC.TYPECMP
WHERE WTH.KEYINTERNAL = 'august 23 2012 10:45:21:027PM '
AND SC.LENGTH IS NOT NULL
GROUP BY WTH.KEYINTERNAL,
WTH.KEYHOUR,
WTH.FIRM,
WTH.CENTRE,
WTH.SERVICE,
WTH.AGENT,
WTH.VERSION,
WTH.DATE_ROULEMENT,
WTH.SERVICEINSTANCE,
WTH.TYPEHOUR,
WTH.PERCENTCALC
The rules for the necessary scale, precision and length are well covered in MSDN documentation.
Decimal is just a scaled integer, so let's look at an example with no decimal point.
For example X DECIMAL(3, 0)
and Y DECIMAL(3, 0)
where X = 100
and Y = 100
, X * Y = 10000
which will not fit in DECIMAL(3, 0)
, and requires DECIMAL(5, 0)
. In fact, 999 * 999 = 998001
which would require DECIMAL(6, 0)
.
So trying to insert the result of multiplying two decimal columns will only fit if the destination column has a capacity at least as great as that indicated by the formulas in the MSDN table for the different operations.