have a problem with pericision

I have been working on an application which require as much percision as we can get. We have defined all numeric values as (38,16) however when ever we divide a number by the result of a built in function it truncate the percision to 5 decimal place this cause us to have a small but enoying loss in the numbers being caculated we need a minimuim of 8 decimal places or better is there a work around or solution for this problem in sql server 2005. The more percision the better.

example: select tna/(select sum(tna) from taum) from taum where fund_id = 2345

the result is always 5 decimal places even though fund_id tna = 2569698.23 and sum(tna) =98745612325879.36 which should equal .00000002602341683313994 instead the result = .00000

Hi Caveman1,

I had try your issue using t_sample table:

CREATE TABLE t_sample ( a DECIMAL(38,16) NOT NULL, b DECIMAL(38,16) NOT NULL );

And I have received your same results to: SELECT a / b AS result FROM t_sample

I have changed precision and scale to my datatypes in t_sample table.

ALTER TABLE t_sample ALTER COLUMN a DECIMAL(18,10) NOT NULL, b DECIMAL(18,4) NOT NULL;

Great news: result = .00000002602341683313994324

Good coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||the problem appears to only happen when a you use a function like sum(b) or avg(b) as the denominator....|||

Nope!

Books online:

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.

Operation Result precision Result scale *

e1 + e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 - e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 * e2

p1 + p2 + 1

s1 + s2

e1 / e2

p1 - s1 + s2 + max(6, s1 + p2 + 1)

max(6, s1 + p2 + 1)

e1 { UNION | EXCEPT | INTERSECT } e2

max(s1, s2) + max(p1-s1, p2-s2)

max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

I don't understand the table above.

here is and example I hope will clearify the issue

if I have a table that is defined as such

create table sec ( secid as int,nasset as numeric(38,16))

If I write a query that no tryies to calcalulate the percentage of a security bassed on total holdings the result is truncated to five decimal places.

select nasset/(select sum(nasset) from sec) from sec where secid = 1 -- truncate the result to five decimal places

I need aleast 8 places of percision in the result what do I need to do.....?

I found a work around that suggest that if you use a variable instead of the subquery that it would solve the problem but that did not work.

example:

declare @.totolA as numeric(38,16)

select @.totalA = sum(nasset) from sec

select nasset/@.totalA from sec where secid = 1 --still truncates to 5 decimal places

|||

I have the same question. How SQL decide the number of decimal in division operator. the formulation in the online book does not make sense. I got 6 decimal when I used (28,18) /(28,18) but over 20 decimal when I used (28,18)/(18,4).

looks like there is no problem for addtion, subtraction, and multiplication operators.

can anyone help ? thx.

sql