Arithmetic overflow error converting numeric to data type numeric on query execution

Question

I am running 2 SQL statements against a temp table to do some calculations. The first one runs well, but on the 2nd I am getting the following error and I don't know why.

Msg 8115, Level 16, State 8, Line 123
Arithmetic overflow error converting numeric to data type numeric.

Maybe you can see something that I am missing. Below are the statements and the table that I am using.

Statements:

update down
Set AMOUNT = case when (up.id is null) then null else  
case when(abs(down.SHAMT)<abs(up.SHAMT) 
and (down.SHQTY<up.SHQTY or down.SHPU#<up.SHPU#) 
and down.GROUPID is not null) then abs(up.SHAMT)-abs(down.SHAMT)
else case when(abs(down.SHAMT)>abs(up.SHAMT) 
and (down.SHQTY>up.SHQTY or down.SHPU#>up.SHPU#) 
and down.GROUPID is not null) then abs(down.SHAMT)-abs(up.SHAMT) end end end
From #ServiceChange down 
join #ServiceChange up 
on up.id = down.id-1 and up.SHCUST = down.SHCUST 
and up.SHDESC = down.SHDESC
where down.GROUPID in ('SD','SI')

update down
Set AMOUNT= case when (up.id is null) then null else  
case when(abs(down.SHAMT)<abs(up.SHAMT) 
and down.GROUPID is not null) then abs(up.SHAMT)-abs(down.SHAMT)
else case when(abs(down.SHAMT)>abs(up.SHAMT) 
and down.GROUPID is not null) then abs(down.SHAMT)-abs(up.SHAMT) end end end
 From #ServiceChange down 
join #ServiceChange up 
on up.id = down.id-1 and up.SHCUST = down.SHCUST 
and up.SHDESC = down.SHDESC
where down.GROUPID in ('PD','PI')

Table:

CREATE TABLE #ServiceChange
(
    [ID] [int] identity(1,1),
    [ORDER] [numeric](8, 0) NOT NULL,
    [SHCOMP] [char](2) NOT NULL,
    [SHCRTD] [numeric](8, 0) NOT NULL,
    [SHCUST] [numeric](7, 0) NOT NULL,
    [SHDESC] [char](35) NOT NULL,
    [SHTYPE] [char](1) NOT NULL,
    [SHAMT] [numeric](9, 2) NOT NULL,
    [SHCRTT] [numeric](6, 0) NOT NULL,
    [SHQTY] [numeric](5, 0) NOT NULL,
    [SHPU#] [numeric](2, 0) NOT NULL,
    [CBLNAM] [char](30) NOT NULL,
    [CSTRDT] [numeric](8, 0) NOT NULL,
    [TBODY] [char](6) NOT NULL,
    [GROUPID] [char](2) NULL,
    [AMOUNT] [numeric](8, 2) NULL
)

If more info is need it, please let me know.

when I double click on the error it highlight "update down" on the second statement — cojimarmiami
numeric(8,2) means total length is 8 and 2 decimal places. if you have any thing more than that then it will fail. there are multiple numberic fields in your statement, so any one might be exceeding — radar

Yevgeniy Kabanets Yevgeniy Kabanets · Accepted Answer · 2014-10-24T19:52:26

Maybe it's because you want to update [AMOUNT] [numeric](8, 2) with value of [SHAMT] [numeric](9, 2) ?

Arithmetic overflow error converting numeric to data type numeric on query execution

1 Answers