none
SQL Vs Oracle for Round different value why?

    Question

  • 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

    Thursday, March 28, 2013 8:33 PM

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.


    Gert-Jan

    Saturday, March 30, 2013 4:36 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


    Thursday, March 28, 2013 8:45 PM
    Moderator
  • 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
    Thursday, March 28, 2013 11:11 PM
  • I agree, both are different Data type,

    but i am sure lots of project are doing migration from Oracle and SQL , how they handle this calcutaion,

    are you suggesting me to change code in my C# programme, if so this would be big change.


    Thanks

    A-


    Ashok

    Friday, March 29, 2013 1:45 PM
  • 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
    Friday, March 29, 2013 3:37 PM
  • 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 Morgan-Kaufmann 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

    Friday, March 29, 2013 11:12 PM
  • 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.


    Gert-Jan

    Saturday, March 30, 2013 4:36 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
    Thursday, April 04, 2013 2:01 PM