Data precision problems between Oracle and SQL Server - number vs float

We want to move data from Oracle to SQL Server, and make it available to developers for several reasons. I've read on MSDN that the proper mapping from Oracle NUMBER is to SQL Server FLOAT.

We have a problem with dyadic rationals. In Oracle, it could have the value of 5.2, and it appears that in SQL Server it is not exactly that. (The number of significant digits can vary wildly within one field.)

A co-worker was connecting to both using Access and seeing the data issue. In SQL Server, I did a query on a huge table where one field was equal to 5.2, and got the same count as Oracle.

select count(*) from result where samp_aliquot_size = 5.6

In both Oracle and SQL Server, I get the count 1118. So then, I did a sum from both:

select sum(samp_aliquot_size) from result where samp_aliquot_size = 5.6

and in Oracle I get 6260.8, in SQL Server I get 6260.80000000009.

The requirements for thi开发者_Go百科s say that the data will be an "exact match". What is the best way to achieve that?

The answer to your question "The requirements for this say that the data will be an "exact match". What is the best way to achieve that?" is to move the dyadic rational from Oracle to SQL Server. SQL Server may then calculate the decimal representation from the EXACT dyadic rational. Do we know what the requested accuracy form Oracle was when the decimal was calculated?

If the dyadic rational is not available; the Oracle value should be moved into a varchar string in SQL Server. Converting to a string will retain the "exact" representation of the dyadic rational calculated by Oracle.

I used this Wikipedia link for my dyadic rational defination:

I am intrested to find where the numeric conversion is inexact. To identify the location could you import the data into two SQL Server columns, one column as varchar and the other as float. After the import output a list of rows where varchar not = Cast(real as varchar)

The mapping table also shows

NUMBER([1-38]) -> NUMERIC([1-38])

Have you considered fixed precision in SQL Server rather than floating if it is within Oracle's more limited DECIMAL range (which matches SQL Server)

Given Oracle's documentation states NUMBER is fixed anyway. And here