numeric (38,0) Division

Hi,
Anyone know why this won't work?
declare @.q numeric (38,0), @.w numeric (38,0), @.e numeric (38,0)
set @.q = 87654321098765432109876543210987654321
print @.q
set @.w = 2631308369336935301672180121600000
print @.w
set @.e = @.q / @.w
print @.e
I get this output with error message:
87654321098765432109876543210987654321
2631308369336935301672180121600000
Server: Msg 8115, Level 16, State 2, Line 6
Arithmetic overflow error converting expression to data type numeric.
BOL has no contraindications when is says:
/ (Divide)
Divides one number by another (an arithmetic division operator).
Syntax
dividend / divisor
Arguments
dividend
Is the numeric expression to divide. dividend can be any valid Microsoft
SQL ServerT expression of any of the data types of the numeric data type
category except the datetime and smalldatetime data types.
divisor
Is the numeric expression to divide the dividend by. divisor can be any
valid SQL Server expression of any of the data types of the numeric data
type category except the datetime and smalldatetime data types.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgYou probably have
SET NUMERIC_ROUNDABORT ON
If you change it to
SET NUMERIC_ROUNDABORT OFF
you calculation will run. The error is telling you that @.e didn't have the n
ecessary precision to represent the result of the division. The result of th
e division of two big number like you have here can result in a lot of decim
al places.
Dan|||Dan,
Thank you, but that's not it. I already had "arithabort" and "numeric
roundabort" set to false for my database to support indexed views.
I figured the rational was something like you stated, but BOL says the
divisor and the dividend can be "any valid Microsoft SQL ServerT expression
of any of the data types of the numeric data type category...," and those
two numeric literals are definitly valid expressions of numeric (38,0) data
type.
This new example doesn't work either, and in this case I have a 35 digit,
even number divided by 2. This should not generate any additional digits
during division:
declare @.q numeric (38,0)
set @.q = cast(87654321098765432109876543210987654
as numeric(38,0))/cast(2
as numeric(38,0))
Server: Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type numeric.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<Dan AT Pluralsight (Dan Sullivan)> wrote in message
news:eXr7dbmuGHA.5044@.TK2MSFTNGP05.phx.gbl...
> You probably have
> SET NUMERIC_ROUNDABORT ON
> If you change it to
> SET NUMERIC_ROUNDABORT OFF
> you calculation will run. The error is telling you that @.e didn't have the
> necessary precision to represent the result of the division. The result of
> the division of two big number like you have here can result in a lot of
> decimal places.
>
> Dan
>|||This works on my system and the extra step I added shows that @.e does not ha
ve enough precision to hold onto the result.
Dan
SET NUMERIC_ROUNDABORT OFF
declare @.q numeric (38,0), @.w numeric (38,0), @.e numeric (38,0)
set @.q = 87654321098765432109876543210987654321
print @.q
set @.w = 2631308369336935301672180121600000
print @.w
set @.e = @.q / @.w
print @.e
print @.e * @.w
87654321098765432109876543210987654321
2631308369336935301672180121600000
33312
87654144399351988769303664210739200000|||Hmmm,
That's wierd. I tried it on my test SQL 2005 box and it works, but it
doesn't work on any of my SQL 2000 boxes. Did you run on 2000 or 2005? On
my SQL 2005 box I added one more print statement with this result:
declare @.q numeric (38,0), @.w numeric (38,0), @.e numeric (38,0)
set @.q = 87654321098765432109876543210987654321
print @.q
set @.w = 2631308369336935301672180121600000
print @.w
set @.e = @.q / @.w
print @.e
print @.e * @.w
print (@.e + 1) * @.w
87654321098765432109876543210987654321
2631308369336935301672180121600000
33312
87654144399351988769303664210739200000
87656775707721325704605336390860800000
Since "@.e * @.w" and "(@.e + 1) * @.w" are on opposite sides of @.q, we see that
33312 is the correct, rounded off answer one would expect for a numeric
(s,0) data type (and it is the answer I am looking for). There is no
incorrect loss of precision.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<Dan AT Pluralsight (Dan Sullivan)> wrote in message
news:%23EOwy9muGHA.3936@.TK2MSFTNGP04.phx.gbl...
> This works on my system and the extra step I added shows that @.e does not
> have enough precision to hold onto the result.
> Dan
> SET NUMERIC_ROUNDABORT OFF
> declare @.q numeric (38,0), @.w numeric (38,0), @.e numeric (38,0)
> set @.q = 87654321098765432109876543210987654321
> print @.q
> set @.w = 2631308369336935301672180121600000
> print @.w
> set @.e = @.q / @.w
> print @.e
> print @.e * @.w
>
> 87654321098765432109876543210987654321
> 2631308369336935301672180121600000
> 33312
> 87654144399351988769303664210739200000
>
>|||I ran on SQL 2005. But if I
SET NUMERIC_ROUNDABORT ON
I get the same error you do.
Dan|||Also with the precision and scale you are using, you cannot make a decimal d
ata type that can handle the result unless SET NUMERIC_ROUNDABOUT OFF. The r
ule for the precision and scale from the BOL says that for division of numer
ics
precision scale
e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
so for your calculation you @.e would have to be numeric(76, 39) which isn't
possible.
Dansql