0

I have experienced a very strange issue when using SSIS (I have also tried this just by writing a query in SQL Server Management Studio and I got the same result).

I have a query that should show rows in which a certain value has increased from last time. For that I have 2 tables - Old and New, which (if I make it simple) contain ID and Value (and some other columns that are not important for this case). I join those 2 tables on the ID and compare the Old and New Values. Both of the columns (Old and New) are float (SSIS sees them both as DT_R8).

Let me give you a view of what the query looks like. 

SELECT n.ID, n.Val, o.Val
FROM New as n INNER JOIN Old as o ON n.ID = o.ID
WHERE n.Val > o.Val

There are several thousands of records in both of the tables and almost all of them go through this query and the results are correct, BUT 3 of all those records appear in the result set incorrectly. Those 3 have equal Old.Val and New.Val (the values are: 4803.2, 1336.44, 2883.25) but they appear in the result set anyway despite that they don't meet the condition. Let me also add that those 3 are not the only records with numbers with decimal places in the set, there are many many more and all those work just fine.

I have tried a few conversions but that did not change anything.

I have also tried several ways to export the data from those tables just to see if the Values actually differ from one another and in any way I do that, those 6 (2 pairs for each of the 3 cases) numbers always appear to be equal. There doesn't appear to anything following the second decimal space no matter how I export the data.

Did anyone ever experience something similar? I'd be very thankful for any kind of advice.

J. Klima
  • 385
  • 2
  • 10

2 Answers2

0

Try to cast float values to decimal in your comparison expression.

Factory Girl
  • 910
  • 4
  • 18
  • 29
0

Please convert both column to a common datatype in the WHERE clause and check the same. sample WHERE CAST(n.Val AS MONEY) > CAST(o.Val AS MONEY)

Abdul Rasheed
  • 6,486
  • 4
  • 32
  • 48
  • Works like a charm! May I ask on how this works? The numbers still seem to be the same (visually) but the comparison works correctly now. – J. Klima Apr 21 '16 at 08:37
  • @J.Kay, This may be help you, http://stackoverflow.com/questions/8839460/why-doesnt-this-sql-query-return-any-results-comparing-floating-point-numbers – Abdul Rasheed Apr 21 '16 at 08:42