November 26, 2009 at 9:16 am
Hi people.
I have a big problem.
In my db i have 2 field one for the quantity and one for the sign of movement.
if a make a select like this:
select count(*) from
(
select field1,sum(field2*field3)as tot from db1
where ......
group by field1
) as test
order by field1
where
field1 = item (nvarchar)
field2 = quantity (floa)
field3 = sign (-1 or 1) (smallint)
sometimes the tot value is 0 and sometimes like 0,11111E-16
someone can help me ???
thanks in advance
November 26, 2009 at 9:34 am
I don't understand the question. Please see the post in my signature on how to provide sample data to get a better idea of how to ask a question here.
November 26, 2009 at 10:03 am
this is the situation:
select field1,sum(field2*field3)as tot from db1
where ......
group by field1
Result
Item tot
"051 A 42798000" 0,900000000000002
"051 A 42910650" 0,800000000000002
select field1,field2,field3 from db1
where ......
Result
051 A 42798000211
051 A 427980001-1
051 A 427980001-1
051 A 427980001,5-1
051 A 427980005,61
051 A 427980001-1
051 A 427980002,5-1
051 A 427980001,7-1
051 A 4279800017-1
051 A 427980000-1
sum is 0,9
051 A 429106504,91
051 A 429106503,21
051 A 429106501-1
051 A 429106501-1
051 A 429106500,8-1
051 A 429106501-1
051 A 429106501-1
051 A 429106502,5-1
051 A 429106500-1
sum is 0,8
why ????
November 26, 2009 at 10:31 am
Getting closer. Did you read the article I mentioned?
Put the data/table structure in a format I can do something with and include the where clauses for both queries (so we can rule that out as a problem).
November 26, 2009 at 10:40 am
looks like you're using float data type for at least one of your columns.
This will give you approximate results (see quote below).
You might want to change it to numeric(p,s) data type.
Note: Since I don't know the range of values you need to store I cannot recommend any value for precision nor scale...
Straight from BooksOnLine:
Using float and real Data
The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.
Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.
Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.
The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server 2005 uses round up. All are accurate to the guaranteed precision but can result in slightly different floating-point values. Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply