Answered by:
SQL Vs Oracle for Round different value why?

Hi ,
I have a query which returing different value in SQL and Oracle if using Round function,
SQL select ROUND( ((38889726.33) / 32415583872.419230) * (10000 / 12 * 12), 2)
Oracle
select ROUND( ((38889726.33) / 32415583872.419230) * (10000 / 12 * 12), 2)from dual
what is solution for this.
looks very small difference, but when data in millions then become large difference.
Thanks
A
Ashok
Question
Answers

I can't tell you what Oracle does or the result it produces.
This is how SQL Server handles your statement.
Your statement has the format: ROUND( ( A / B ) * ( (C / D) * E ), 2)
The literal A (38889726.33) is interpreted as decimal(10,2).
The literal B (32415583872.419230) is interpreted as decimal(17,6).
The literal C (10000) is interpreted as int. The same applies to the literal D and E.
The precision formula for division is "p1  s1 + s2 + max(6, s1 + p2 + 1)", and the scale formula "max(6, s1 + p2 + 1)". Therefore, the expression A/B with the values above results in a decimal(34,20). Let's call it F.
The expression C/D performs integer division, and results in an int. Let's call it G.
Expression G*E is integer multiplication, and results in an int. Let's call it H.
For the expression F*H, the value of H is implicitly converted to decimal(10,0). The precision formula for multiplication is "p1 + p2 + 1" and the scale formula "s1 + s2". So the result would be a decimal(45,20). However the maximum precision is 38, so scale is lost, and you get a decimal(38,13). Rounding may occur here.
The minus doesn't change anything, so you are finally rounding the decimal(38,13) value of 11.9924325881244 to two decimal places, which in this case means rounding down to 11.99
So this was a very long explanation. The difference your are seeing is probably because you didn't expect integer division. The statement below will return "12.00"
select ROUND( ((38889726.33) / 32415583872.419230) * (10000 / 12. * 12), 2)
Notice the dot after 12, which turns the expression C/D into regular division instead of integer division.
GertJan
 Proposed as answer by Kalman TothModerator Saturday, March 30, 2013 4:59 PM
 Marked as answer by Jumpingboy Thursday, April 04, 2013 1:57 PM
All replies

SQL Server & ORACLE math algorithms are programmed by different teams of software engineers, hence they are different.
select ROUND( ((38889726.33) / 32415583872.419230) * (10000 / 12 * 12), 2)  11.9900000000000 select ROUND( ((38889726.33) / 32415583872.419230) * (10000.0 / 12 * 12), 2)  12.000000000000000 select ROUND( ((38889726.33) / 32415583872.419230) * (10000 / 12 * 12.0), 2)  11.9900000000000 select ROUND( ((38889726.33) / 32415583872.419230) * (10000 / 12.0 * 12), 2)  12.00000000000000 select ROUND( ((38889726.33) / 32415583872.419230) * (10000 / 12 * 12), 2.0)  11.9900000000000 select ROUND( ((38889726.33) / 32415583872.419230) * (1.0 * 10000 / 12 * 12), 2)  12.0000000000000 select ROUND( ((38889726.33) / 32415583872.419230) * 1.0 * (10000 / 12 * 12), 2)  11.99000000000
Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: SQL Programming & Database Design Using Microsoft SQL Server 2012 Edited by Kalman TothModerator Saturday, March 30, 2013 4:59 PM

You would have to pick the calculation apart to see what happens.
In the example all numbers are constants, which means that in SQL Server at least the data type is decimal. The rules what data types you get back when you do arithmetic with different decimal data types in SQL Server are complex, and I would surprised if they are constants.
If your actual data is float, then it is even more trivial, since float is an exact data type.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se 

Porting code between RDBMS often means a lot of work, yes. Then again, who says anyone else has the same calculation that you have?
No, we are not suggesting to change you C# program  we did not even know that you had one until you mentioned it.
Anyway, it's very difficult to help you, because we don't know anything about your calculation, your business requierments, your tables, your C# code. Or more precisely, we know next to nothing.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se 
The ANSI/ISO Standard says that rounding, truncation and precision are implementation defined. You have to know your products.
CELKO Books in Celko Series for MorganKaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

I can't tell you what Oracle does or the result it produces.
This is how SQL Server handles your statement.
Your statement has the format: ROUND( ( A / B ) * ( (C / D) * E ), 2)
The literal A (38889726.33) is interpreted as decimal(10,2).
The literal B (32415583872.419230) is interpreted as decimal(17,6).
The literal C (10000) is interpreted as int. The same applies to the literal D and E.
The precision formula for division is "p1  s1 + s2 + max(6, s1 + p2 + 1)", and the scale formula "max(6, s1 + p2 + 1)". Therefore, the expression A/B with the values above results in a decimal(34,20). Let's call it F.
The expression C/D performs integer division, and results in an int. Let's call it G.
Expression G*E is integer multiplication, and results in an int. Let's call it H.
For the expression F*H, the value of H is implicitly converted to decimal(10,0). The precision formula for multiplication is "p1 + p2 + 1" and the scale formula "s1 + s2". So the result would be a decimal(45,20). However the maximum precision is 38, so scale is lost, and you get a decimal(38,13). Rounding may occur here.
The minus doesn't change anything, so you are finally rounding the decimal(38,13) value of 11.9924325881244 to two decimal places, which in this case means rounding down to 11.99
So this was a very long explanation. The difference your are seeing is probably because you didn't expect integer division. The statement below will return "12.00"
select ROUND( ((38889726.33) / 32415583872.419230) * (10000 / 12. * 12), 2)
Notice the dot after 12, which turns the expression C/D into regular division instead of integer division.
GertJan
 Proposed as answer by Kalman TothModerator Saturday, March 30, 2013 4:59 PM
 Marked as answer by Jumpingboy Thursday, April 04, 2013 1:57 PM

Thanks all ,
I think the main problem is different data type and problem happening here:
Select 10000/12 , if you do in SQL you ll see only 833.0000
because 12 is integer , you have to make 12 as decimal
Select 10000/12.0 than all set.
so one think i notice if you doing operation with integer than result would be int, if your value is decimal than would be decimal,
so may be you can declare variable and set value inside and do than would be better.
Declare @test decimal
Set @test =12.0
select ROUND( ((38889726.33) / 32415583872.419230) * (10000 / @test * 12), 2)Thanks
A
Ashok
 Edited by Jumpingboy Thursday, April 04, 2013 2:01 PM