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 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|||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
>