Arithmetic overflow error converting numeric to data type numeric

Question

Arithmetic overflow error converting numeric to data type numeric

Greg Johnson-186398

SSCommitted

Points: 1703
More actions
June 1, 2005 at 8:06 am

#163847

can't convert numeric to numeric? ungh. didn't know it was such a stretch.
here's the query. The offending lines are commented. It will run as it is, but yield the title error when the comments are removed. Get this... it *used* to run. Could there be a divide by zero issue? WTF?
select
fp.fullname,
wins.numwins,
losses.numlosses,
wins.numwins + losses.numlosses as games_played--,
--convert(char(5), convert(decimal(3,1), 100*convert(decimal(3,1), wins.numwins)/(losses.numlosses + wins.numwins))) + '%' as winpct
from
FoosPlaya fp
inner join
(select fp.[id], count(*) as numwins
from FoosPlaya fp
inner join
(select 'frontwinner' = case
when home_score = 10 then gr.[home_forward_id]
when away_score = 10 then gr.[away_forward_id]
end,
'backwinner' = case
when home_score = 10 then gr.[home_back_id]
when away_score = 10 then gr.[away_back_id]
end,
'frontloser' = case
when home_score = 10 then gr.[away_forward_id]
when away_score = 10 then gr.[home_forward_id]
end,
'backloser' = case
when home_score = 10 then gr.[away_back_id]
when away_score = 10 then gr.[home_back_id]
end
from GameResult gr) q
on (fp.[id] = q.frontwinner or fp.[id] = q.backwinner)
group by fp.[id]) wins
on
fp.[id] = wins.[id]
inner join
(select fp.[id], count(*) as numlosses
from FoosPlaya fp
inner join
(select 'frontwinner' = case
when home_score = 10 then gr.[home_forward_id]
when away_score = 10 then gr.[away_forward_id]
end,
'backwinner' = case
when home_score = 10 then gr.[home_back_id]
when away_score = 10 then gr.[away_back_id]
end,
'frontloser' = case
when home_score = 10 then gr.[away_forward_id]
when away_score = 10 then gr.[home_forward_id]
end,
'backloser' = case
when home_score = 10 then gr.[away_back_id]
when away_score = 10 then gr.[home_back_id]
end
from GameResult gr) q
on (fp.[id] = q.frontloser or fp.[id] = q.backloser)
group by fp.[id]) losses
on
fp.[id] = losses.[id]
--order by
--convert(decimal(3,1), wins.numwins)/(losses.numlosses + wins.numwins) desc

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Try to look at the data you're retrieveing for column wins.numwins. Could it be possible that you have something in there that is > 99 ..?

/Kenneth

Greg Johnson-186398 SSCommitted Points: 1703 More actions · Answer 2

you hit the nail on the head. I can see why that would be an overflow. tx

jay-h SSCoach Points: 18816 More actions · Answer 3

let me ask a further question:

Sometimes there is a nonconvertable value in a query, but the query simply terminates and rolls back. Is there any way to see exactly which value caused the failure?

...

-- FORTRAN manual for Xerox Computers --

Greg Johnson-186398 SSCommitted Points: 1703 More actions · Answer 4

it was the wins being > 100 like you suggested. in the select list and in the group by.

it was trying to shoehorn 101.4 into decimal(3,1)

you can get the same error executing this:

declare @a decimal(3,1)

set @a = 101.4

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

I don't think you can see at once what caused the error. This is a bit of trial and error to find out. Anything specific in mind?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 6

Can we use this to find the error rows

Select DecimalCol FROM Table WHERE DecimalCol > 99.9

Regards,
gova