Arithmetic overflow error converting numeric to data type nume

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@.discussions.microsoft.com) writes:
> 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@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>examnotes (dpc@.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@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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)|||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@.discussions.microsoft.com> wrote in message
news:C5CC542D-3253-4772-B90B-2FE83173043D@.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:
>|||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@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:jpbpg11e74g15mm3gjmmli02mfni6ha@.
4ax.com...
> On Tue, 23 Aug 2005 23:10:04 -0700, dpc wrote:
>
> 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)