Oracle 和 SQL Server 之间的数据精度问题 - 数字与浮点数
我们希望将数据从 Oracle 迁移到 SQL Server，并出于多种原因将其提供给开发人员。我在 MSDN 上读到正确的映射从 Oracle NUMBER 到 SQL Server FLOAT。
我们有二元理性的问题。在 Oracle 中，它的值可能是 5.2，但在 SQL Server 中似乎并非如此。 （一个字段内的有效位数可能会有很大差异。）
一位同事正在使用 Access 连接并看到数据问题。在 SQL Server 中，我对一张巨大的表进行了查询，其中一个字段等于 5.2，并得到了与 Oracle 相同的计数。
select count(*) from result where samp_aliquot_size = 5.6
在 Oracle 和 SQL Server 中，我得到的计数都是 1118。然后，我对两者进行求和：
select sum(samp_aliquot_size) from result where samp_aliquot_size = 5.6
在 Oracle 中我得到 6260.8，在 SQL Server 中我得到 6260.80000000009。
对此的要求是数据将是“完全匹配”。实现这一目标的最佳方法是什么？
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 this say that the data will be an "exact match". What is the best way to achieve that?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的问题的答案“对此的要求是数据将是“完全匹配”。实现这一目标的最佳方法是什么？”就是将二元理性从 Oracle 转移到 SQL Server。然后，SQL Server 可以根据 EXACT 二进有理计算十进制表示形式。我们是否知道计算小数时 Oracle 要求的精度是多少？
如果二元有理不可用； Oracle 值应移至 SQL Server 中的 varchar 字符串中。转换为字符串将保留 Oracle 计算的二元有理数的“精确”表示。
我使用这个维基百科链接作为我的二元理性定义：
我很想找到数字转换的位置不精确。为了确定位置，您可以将数据导入到两个 SQL Server 列中，一列为 varchar，另一列为 float。导入后输出一个行列表，其中 varchar not = Cast(real as varchar)
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)
映射表还显示，
如果它在 Oracle 更有限的 DECIMAL 范围内（与 SQL Server 匹配），您是否考虑过 SQL Server 中的固定精度而不是浮动精度
给定 Oracle 文档 指出 NUMBER 无论如何都是固定的。 这里
The mapping table also shows
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