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; (200109.38, « »)

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

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.questionfor.info.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.questionfor.info.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.questionfor.info.cnSR decimal(14,2), .sql.questionfor.info.cn210 decimal(14,2)

      set .sql.questionfor.info.cnSr = 9.08

      set .sql.questionfor.info.cn210 = 10

      select .sql.questionfor.info.cnSR / isnull(.sql.questionfor.info.cn210,0) + isnull( .sql.questionfor.info.cnSR, 0)

      select cast (.sql.questionfor.info.cnSR / isnull(.sql.questionfor.info.cn210,0) + isnull( .sql.questionfor.info.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.questionfor.info.discussions.microsoft.com> wrote in message

      news:172194FA-6C0C-4847-8123-0A037D756EED.sql.questionfor.info.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.questionfor.info.hotmail.com> wrote in message

      news:eVRY5oEqFHA.2364.sql.questionfor.info.tk2msftngp13.phx.gbl...

      > Get rid of the cast and do a select like this for values >

      > declare .sql.questionfor.info.cnSR decimal(14,2), .sql.questionfor.info.cn210 decimal(14,2)

      > set .sql.questionfor.info.cnSr = 9.08

      > set .sql.questionfor.info.cn210 = 10

      > select .sql.questionfor.info.cnSR / isnull(.sql.questionfor.info.cn210,0) + isnull( .sql.questionfor.info.cnSR, 0)

      > select cast (.sql.questionfor.info.cnSR / isnull(.sql.questionfor.info.cn210,0) + isnull( .sql.questionfor.info.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.questionfor.info.discussions.microsoft.com> wrote in message

      > news:172194FA-6C0C-4847-8123-0A037D756EED.sql.questionfor.info.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.questionfor.info.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.questionfor.info.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.questionfor.info.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.questionfor.info.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.questionfor.info.discussions.microsoft.com> wrote in message

      news:C5CC542D-3253-4772-B90B-2FE83173043D.sql.questionfor.info.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.questionfor.info.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.questionfor.info.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.questionfor.info.pe_NO_rFact.in_SPAM_fo> wrote in message

      news:jpbpg11e74g15mm3gjmmlsadi02mfni6ha.sql.questionfor.info.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