Tags: arithmetic, balking, believe, converting, error, level, msg, numeric, overflow, procedure, programming, server, sql, state, type, wrong
Arithmetic overflow error converting numeric to data type numeric
On Programmer » SQL
19,745 words with 12 Comments; publish: Thu, 29 May 2008 02:16:00 GMT; (20062.50, « »)
I do not believe this, simple arithmetic and the server is balking? Am I
doing something wrong?
Server: Msg 8115, Level 16, State 8, Procedure usp_bmr, Line 346
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
Can someone tell me what is the problem with this SQL:
Update #tblOutput
SET pctProcSR = cast (cnSR / isnull(cn210,0) + isnull( cnSR, 0) as
decimal(3,2) )
Where ( isnull(cn210,0) + isnull(cnSR,0) ) > 0 and cnsr is not null and
cnsr <> 0 and cn210 is not null ;
Where pctProcSR is Decimal(3,2)
In the past when I encounter these problems I just perform a cast and it
works.
I even tried casting every possible sub to no avail.
Update #tblOutput
SET pctProcSR = cast ( cast(cnSR as decimal(14,2) )/ isnull(cast(cn210
as decimal(14,2) ) ,0) + isnull( cast(cnSR as decimal(14,2)), 0) as
decimal(3,2) )
Where isnull(cast(cn210 as decimal(14,2) ) ,0) + isnull( cast(cnSR as
decimal(14,2)), 0) > 0 and cnsr is not null and cnsr <> 0 and cn210 is not
null ;
TIA
dpc
http://sql.itags.org/q_sql_3199.html
All Comments
Leave a comment...
- 12 Comments
- On Tue, 23 Aug 2005 15:17:01 -0700, dpc wrote:
>I do not believe this, simple arithmetic and the server is balking? Am I
>doing something wrong?
>Server: Msg 8115, Level 16, State 8, Procedure usp_bmr, Line 346
>Arithmetic overflow error converting numeric to data type numeric.
>The statement has been terminated.
>Can someone tell me what is the problem with this SQL:
>Update #tblOutput
>SET pctProcSR = cast (cnSR / isnull(cn210,0) + isnull( cnSR, 0) as
>decimal(3,2) )
>Where ( isnull(cn210,0) + isnull(cnSR,0) ) > 0 and cnsr is not null and
>cnsr <> 0 and cn210 is not null ;
>Where pctProcSR is Decimal(3,2)
Hi dpc,
Can you post the output of this query:
SELECT cnSR, cn210,
cnSR / isnull(cn210,0) + isnull(cnSR, 0)
FROM #tblOutput
WHERE isnull(cn210,0) + isnull(cnSR,0) > 0
AND cnsr is not null
AND cnsr <> 0
AND cn210 is not null
AND ABS(cnSR / isnull(cn210,0) + isnull(cnSR, 0)) > 9.99
BTW, I must admit that I know nothing about your business, but wouldn't
the calculation make more sense if you change it to
>SET pctProcSR = cast (cnSR / (isnull(cn210,0) + isnull( cnSR, 0) ) as
>decimal(3,2) )
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
#1; Thu, 29 May 2008 02:17:00 GMT
- dpc (dpc.sql.itags.org.discussions.microsoft.com) writes:
> I do not believe this, simple arithmetic and the server is balking? Am I
> doing something wrong?
> Server: Msg 8115, Level 16, State 8, Procedure usp_bmr, Line 346
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> Can someone tell me what is the problem with this SQL:
> Update #tblOutput
> SET pctProcSR = cast (cnSR / isnull(cn210,0) + isnull( cnSR, 0) as
> decimal(3,2) )
> Where ( isnull(cn210,0) + isnull(cnSR,0) ) > 0 and cnsr is not null and
> cnsr <> 0 and cn210 is not null ;
> Where pctProcSR is Decimal(3,2)
I guess the problem is that the count does not account for results that
are > 9.99.
But without access to the data it is very difficult to say exactly what
happens.
--
Erland Sommarskog, SQL Server MVP, esquel.sql.itags.org.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
#2; Thu, 29 May 2008 02:18:00 GMT
- Do you know what arithmetic overflow is? Maybe you should Google it.
"Just performing a cast" isn't a good solution to your problem.
"dpc" wrote:
> I do not believe this, simple arithmetic and the server is balking? Am I
> doing something wrong?
> Server: Msg 8115, Level 16, State 8, Procedure usp_bmr, Line 346
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> Can someone tell me what is the problem with this SQL:
> Update #tblOutput
> SET pctProcSR = cast (cnSR / isnull(cn210,0) + isnull( cnSR, 0) as
> decimal(3,2) )
> Where ( isnull(cn210,0) + isnull(cnSR,0) ) > 0 and cnsr is not null and
> cnsr <> 0 and cn210 is not null ;
> Where pctProcSR is Decimal(3,2)
> In the past when I encounter these problems I just perform a cast and it
> works.
> I even tried casting every possible sub to no avail.
> Update #tblOutput
> SET pctProcSR = cast ( cast(cnSR as decimal(14,2) )/ isnull(cast(cn210
> as decimal(14,2) ) ,0) + isnull( cast(cnSR as decimal(14,2)), 0) as
> decimal(3,2) )
> Where isnull(cast(cn210 as decimal(14,2) ) ,0) + isnull( cast(cnSR as
> decimal(14,2)), 0) > 0 and cnsr is not null and cnsr <> 0 and cn210 is not
> null ;
> TIA
> dpc
#3; Thu, 29 May 2008 02:19:00 GMT
- Get rid of the cast and do a select like this for values >
declare .sql.itags.org.cnSR decimal(14,2), .sql.itags.org.cn210 decimal(14,2)
set .sql.itags.org.cnSr = 9.08
set .sql.itags.org.cn210 = 10
select .sql.itags.org.cnSR / isnull(.sql.itags.org.cn210,0) + isnull( .sql.itags.org.cnSR, 0)
select cast (.sql.itags.org.cnSR / isnull(.sql.itags.org.cn210,0) + isnull( .sql.itags.org.cnSR, 0) as
decimal(3,2))
This returns
--
9.99900000000000000
and an error
Server: Msg 8115, Level 16, State 8, Line 7
Arithmetic overflow error converting numeric to data type numeric.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"dpc" <dpc.sql.itags.org.discussions.microsoft.com> wrote in message
news:172194FA-6C0C-4847-8123-0A037D756EED.sql.itags.org.microsoft.com...
>I do not believe this, simple arithmetic and the server is balking? Am I
> doing something wrong?
> Server: Msg 8115, Level 16, State 8, Procedure usp_bmr, Line 346
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> Can someone tell me what is the problem with this SQL:
> Update #tblOutput
> SET pctProcSR = cast (cnSR / isnull(cn210,0) + isnull( cnSR, 0) as
> decimal(3,2) )
> Where ( isnull(cn210,0) + isnull(cnSR,0) ) > 0 and cnsr is not null and
> cnsr <> 0 and cn210 is not null ;
> Where pctProcSR is Decimal(3,2)
> In the past when I encounter these problems I just perform a cast and it
> works.
> I even tried casting every possible sub to no avail.
> Update #tblOutput
> SET pctProcSR = cast ( cast(cnSR as decimal(14,2) )/ isnull(cast(cn210
> as decimal(14,2) ) ,0) + isnull( cast(cnSR as decimal(14,2)), 0) as
> decimal(3,2) )
> Where isnull(cast(cn210 as decimal(14,2) ) ,0) + isnull( cast(cnSR as
> decimal(14,2)), 0) > 0 and cnsr is not null and cnsr <> 0 and cn210 is
> not
> null ;
> TIA
> dpc
#4; Thu, 29 May 2008 02:20:00 GMT
- Erp, do the select for values where the expression > 9.99
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Louis Davidson" <dr_dontspamme_sql.sql.itags.org.hotmail.com> wrote in message
news:eVRY5oEqFHA.2364.sql.itags.org.tk2msftngp13.phx.gbl...
> Get rid of the cast and do a select like this for values >
> declare .sql.itags.org.cnSR decimal(14,2), .sql.itags.org.cn210 decimal(14,2)
> set .sql.itags.org.cnSr = 9.08
> set .sql.itags.org.cn210 = 10
> select .sql.itags.org.cnSR / isnull(.sql.itags.org.cn210,0) + isnull( .sql.itags.org.cnSR, 0)
> select cast (.sql.itags.org.cnSR / isnull(.sql.itags.org.cn210,0) + isnull( .sql.itags.org.cnSR, 0) as
> decimal(3,2))
> This returns
> --
> 9.99900000000000000
> and an error
> Server: Msg 8115, Level 16, State 8, Line 7
> Arithmetic overflow error converting numeric to data type numeric.
>
> --
> ----
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "dpc" <dpc.sql.itags.org.discussions.microsoft.com> wrote in message
> news:172194FA-6C0C-4847-8123-0A037D756EED.sql.itags.org.microsoft.com...
>>I do not believe this, simple arithmetic and the server is balking? Am I
>> doing something wrong?
>> Server: Msg 8115, Level 16, State 8, Procedure usp_bmr, Line 346
>> Arithmetic overflow error converting numeric to data type numeric.
>> The statement has been terminated.
>> Can someone tell me what is the problem with this SQL:
>> Update #tblOutput
>> SET pctProcSR = cast (cnSR / isnull(cn210,0) + isnull( cnSR, 0) as
>> decimal(3,2) )
>> Where ( isnull(cn210,0) + isnull(cnSR,0) ) > 0 and cnsr is not null
>> and
>> cnsr <> 0 and cn210 is not null ;
>> Where pctProcSR is Decimal(3,2)
>> In the past when I encounter these problems I just perform a cast and it
>> works.
>> I even tried casting every possible sub to no avail.
>> Update #tblOutput
>> SET pctProcSR = cast ( cast(cnSR as decimal(14,2) )/ isnull(cast(cn210
>> as decimal(14,2) ) ,0) + isnull( cast(cnSR as decimal(14,2)), 0) as
>> decimal(3,2) )
>> Where isnull(cast(cn210 as decimal(14,2) ) ,0) + isnull( cast(cnSR as
>> decimal(14,2)), 0) > 0 and cnsr is not null and cnsr <> 0 and cn210 is
>> not
>> null ;
>> TIA
>> dpc
>
#5; Thu, 29 May 2008 02:21:00 GMT
- Did that but it was giving me the overlfow problem so I tried casting more
stuff.
"dpc" wrote:
> I do not believe this, simple arithmetic and the server is balking? Am I
> doing something wrong?
> Server: Msg 8115, Level 16, State 8, Procedure usp_bmr, Line 346
> Arithmetic overflow error converting numeric to data type numeric.
> The statement has been terminated.
> Can someone tell me what is the problem with this SQL:
> Update #tblOutput
> SET pctProcSR = cast (cnSR / isnull(cn210,0) + isnull( cnSR, 0) as
> decimal(3,2) )
> Where ( isnull(cn210,0) + isnull(cnSR,0) ) > 0 and cnsr is not null and
> cnsr <> 0 and cn210 is not null ;
> Where pctProcSR is Decimal(3,2)
> In the past when I encounter these problems I just perform a cast and it
> works.
> I even tried casting every possible sub to no avail.
> Update #tblOutput
> SET pctProcSR = cast ( cast(cnSR as decimal(14,2) )/ isnull(cast(cn210
> as decimal(14,2) ) ,0) + isnull( cast(cnSR as decimal(14,2)), 0) as
> decimal(3,2) )
> Where isnull(cast(cn210 as decimal(14,2) ) ,0) + isnull( cast(cnSR as
> decimal(14,2)), 0) > 0 and cnsr is not null and cnsr <> 0 and cn210 is not
> null ;
> TIA
> dpc
#6; Thu, 29 May 2008 02:22:00 GMT
- My results can never be > 9.99 as it is being divided by itelsef plus
something else.
I.e. possible is 1.00 to 0......
"Erland Sommarskog" wrote:
> dpc (dpc.sql.itags.org.discussions.microsoft.com) writes:
> > I do not believe this, simple arithmetic and the server is balking? Am I
> > doing something wrong?
> >
> > Server: Msg 8115, Level 16, State 8, Procedure usp_bmr, Line 346
> > Arithmetic overflow error converting numeric to data type numeric.
> > The statement has been terminated.
> >
> > Can someone tell me what is the problem with this SQL:
> >
> > Update #tblOutput
> > SET pctProcSR = cast (cnSR / isnull(cn210,0) + isnull( cnSR, 0) as
> > decimal(3,2) )
> > Where ( isnull(cn210,0) + isnull(cnSR,0) ) > 0 and cnsr is not null and
> > cnsr <> 0 and cn210 is not null ;
> > Where pctProcSR is Decimal(3,2)
> I guess the problem is that the count does not account for results that
> are > 9.99.
> But without access to the data it is very difficult to say exactly what
> happens.
> --
> Erland Sommarskog, SQL Server MVP, esquel.sql.itags.org.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
#7; Thu, 29 May 2008 02:23:00 GMT
- Googled and had seen that others had this problem but maily withe the
conversion of datetime datatypes.
"KH" wrote:
> Do you know what arithmetic overflow is? Maybe you should Google it.
> "Just performing a cast" isn't a good solution to your problem.
>
> "dpc" wrote:
> > I do not believe this, simple arithmetic and the server is balking? Am I
> > doing something wrong?
> >
> > Server: Msg 8115, Level 16, State 8, Procedure usp_bmr, Line 346
> > Arithmetic overflow error converting numeric to data type numeric.
> > The statement has been terminated.
> >
> > Can someone tell me what is the problem with this SQL:
> >
> > Update #tblOutput
> > SET pctProcSR = cast (cnSR / isnull(cn210,0) + isnull( cnSR, 0) as
> > decimal(3,2) )
> > Where ( isnull(cn210,0) + isnull(cnSR,0) ) > 0 and cnsr is not null and
> > cnsr <> 0 and cn210 is not null ;
> > Where pctProcSR is Decimal(3,2)
> >
> > In the past when I encounter these problems I just perform a cast and it
> > works.
> >
> > I even tried casting every possible sub to no avail.
> > Update #tblOutput
> > SET pctProcSR = cast ( cast(cnSR as decimal(14,2) )/ isnull(cast(cn210
> > as decimal(14,2) ) ,0) + isnull( cast(cnSR as decimal(14,2)), 0) as
> > decimal(3,2) )
> > Where isnull(cast(cn210 as decimal(14,2) ) ,0) + isnull( cast(cnSR as
> > decimal(14,2)), 0) > 0 and cnsr is not null and cnsr <> 0 and cn210 is not
> > null ;
> >
> > TIA
> >
> > dpc
#8; Thu, 29 May 2008 02:24:00 GMT
- =?Utf-8?B?ZHBj?= (dpc.sql.itags.org.discussions.microsoft.com) writes:
> My results can never be > 9.99 as it is being divided by itelsef plus
> something else.
That "something else" can be negative. At least from my corner of
ignorance.
Anyway, the best you can do is this:
SELECT *
FROM #tblOutput
WHERE cast(cnSR / isnull(cn210,0) + isnull( nSR, 0) as decimal(13,2)) > 9.99
and (isnull(cn210,0) + isnull(cnSR,0) ) > 0
and cnsr is not null
and cnsr <> 0
and cn210 is not null ;
That will give you some clue of the data that is causing you problems.
--
Erland Sommarskog, SQL Server MVP, esquel.sql.itags.org.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
#9; Thu, 29 May 2008 02:25:00 GMT
- On Tue, 23 Aug 2005 23:10:04 -0700, dpc wrote:
>My results can never be > 9.99 as it is being divided by itelsef plus
>something else.
Hi dpc,
It is not. As I already mentioned in my previous reply, you missed some
parentheses. The formula
X = Y / Z + Y
will divide Y by Z, then add Y to that. It's the same as Y * (1 + 1/Z)
What you need is this:
X = Y / (Z + Y)
The extra parentheses ensure that Z + Y is calculated first, and that Y
is then divided by this value.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
#10; Thu, 29 May 2008 02:26:00 GMT
- Oh, I see what your problem is, you are not using parenthesis correctly.
Update #tblOutput
SET pctProcSR = cast (cnSR / isnull(cn210,0) + isnull( cnSR, 0) as
decimal(3,2) )
Where ( isnull(cn210,0) + isnull(cnSR,0) ) > 0 and cnsr is not null and
cnsr <> 0 and cn210 is not null ;
Where pctProcSR is Decimal(3,2)
cast (cnSR / isnull(cn210,0) + isnull( cnSR, 0) as decimal(3,2) )
You are doing:
Division comes before addition in precedence, so you are doing the divsion
first. Change to:
cnSR / (isNull(cn210,0) + isnull(cnSR,0))
And you should have your problem fixed.
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"dpc" <dpc.sql.itags.org.discussions.microsoft.com> wrote in message
news:C5CC542D-3253-4772-B90B-2FE83173043D.sql.itags.org.microsoft.com...
> My results can never be > 9.99 as it is being divided by itelsef plus
> something else.
> I.e. possible is 1.00 to 0......
> "Erland Sommarskog" wrote:
>> dpc (dpc.sql.itags.org.discussions.microsoft.com) writes:
>> > I do not believe this, simple arithmetic and the server is balking? Am
>> > I
>> > doing something wrong?
>> >
>> > Server: Msg 8115, Level 16, State 8, Procedure usp_bmr, Line 346
>> > Arithmetic overflow error converting numeric to data type numeric.
>> > The statement has been terminated.
>> >
>> > Can someone tell me what is the problem with this SQL:
>> >
>> > Update #tblOutput
>> > SET pctProcSR = cast (cnSR / isnull(cn210,0) + isnull( cnSR, 0)
>> > as
>> > decimal(3,2) )
>> > Where ( isnull(cn210,0) + isnull(cnSR,0) ) > 0 and cnsr is not null
>> > and
>> > cnsr <> 0 and cn210 is not null ;
>> > Where pctProcSR is Decimal(3,2)
>> I guess the problem is that the count does not account for results that
>> are > 9.99.
>> But without access to the data it is very difficult to say exactly what
>> happens.
>> --
>> Erland Sommarskog, SQL Server MVP, esquel.sql.itags.org.sommarskog.se
>> Books Online for SQL Server SP3 at
>> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>>
#11; Thu, 29 May 2008 02:27:00 GMT
- Sorry Hugo, didn't see you had already said that. Messy messy thread :)
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Hugo Kornelis" <hugo.sql.itags.org.pe_NO_rFact.in_SPAM_fo> wrote in message
news:jpbpg11e74g15mm3gjmmlsadi02mfni6ha.sql.itags.org.4ax.com...
> On Tue, 23 Aug 2005 23:10:04 -0700, dpc wrote:
>>My results can never be > 9.99 as it is being divided by itelsef plus
>>something else.
> Hi dpc,
> It is not. As I already mentioned in my previous reply, you missed some
> parentheses. The formula
> X = Y / Z + Y
> will divide Y by Z, then add Y to that. It's the same as Y * (1 + 1/Z)
> What you need is this:
> X = Y / (Z + Y)
> The extra parentheses ensure that Z + Y is calculated first, and that Y
> is then divided by this value.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
#12; Thu, 29 May 2008 02:28:00 GMT