Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Math operation debugging Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, December 03, 2012 4:51 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, January 02, 2013 2:35 PM Points: 194, Visits: 86
 Hi all, I have a formula that works fine in Excel but not in SQL. Hopefully someone knows of some gotcha that I am not in this situation.When I run this in SQL I get 0.00 as a result but when I run it in Excel I get the expected value of 4020.73~select ((26.7*((504.682+14.566)/14.73)*(520/(460+64))*(1/POWER((1/(0.99877-(0.00000072531*504.682*64)+(0.00013027*504.682))),2)))+(19.5*((1751.84+14.566)/14.73)*(520/(460+67))*(1/POWER((1/(0.99877-(0.00000072531*1751.84*67)+(0.00013027*1751.84))),2))))QuickMath also had no problem understanding the formula (after switch the power() function to the ^notationhttp://bit.ly/Tyx3po (might get a warning about XSS but its safe)Is anyone aware of subtle differences in how SQL evaluates expressions compared to Excel?thanks DaveTrainmark.com IT Training B2B Marketplace(Jobs for IT Instructors)
Post #1392185
 Posted Monday, December 03, 2012 5:09 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 9:00 AM Points: 5,324, Visits: 21,913
Post #1392192
 Posted Monday, December 03, 2012 5:09 PM
 SSC Eights! Group: General Forum Members Last Login: Today @ 10:11 AM Points: 870, Visits: 6,382
 Just made everything at least 3 decimals. I think some of the integer math was dropping the decimals for you. `select ((26.700*((504.682+14.566)/14.730)*(520.000/(460.000+64.000))*(1.00/POWER((1.00/(0.99877-(0.00000072531*504.682*64.000)+(0.00013027*504.682))),2.000)))+(19.500*((1751.84+14.566)/14.73)*(520.000/(460.000+67.000))*(1.000/POWER((1.000/(0.99877-(0.00000072531*1751.84*67.000)+(0.00013027*1751.84))),2.000))))`gives me: 4020.733146 And then again, I might be wrong ...David Webb
Post #1392193
 Posted Monday, December 03, 2012 5:23 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, January 02, 2013 2:35 PM Points: 194, Visits: 86
 Thank you Mr. Webb! That did it all right. So glad I posted here I would have never tried that :)Now that the nature of the issue is resolved I can play around with the best way to fix this in the production system.Thanks again! DaveTrainmark.com IT Training B2B Marketplace(Jobs for IT Instructors)
Post #1392198
 Posted Tuesday, December 04, 2012 2:59 AM
 SSChampion Group: General Forum Members Last Login: Today @ 6:15 AM Points: 11,052, Visits: 10,814
 chisholmd (12/3/2012)Hi all, I have a formula that works fine in Excel but not in SQL. Hopefully someone knows of some gotcha that I am not in this situation.When I run this in SQL I get 0.00 as a result but when I run it in Excel I get the expected value of 4020.73~select ((26.7*((504.682+14.566)/14.73)*(520/(460+64))*(1/POWER((1/(0.99877-(0.00000072531*504.682*64)+(0.00013027*504.682))),2)))+(19.5*((1751.84+14.566)/14.73)*(520/(460+67))*(1/POWER((1/(0.99877-(0.00000072531*1751.84*67)+(0.00013027*1751.84))),2))))Is anyone aware of subtle differences in how SQL evaluates expressions compared to Excel?Within that complex expression are multiplications by the result of the following two computations:`SELECT (520 / (460 + 64))SELECT (520 / (460 + 67))`Where both arguments to the division operator are integers, SQL Server performs integer division, returning zero in both cases. See http://msdn.microsoft.com/en-us/library/ms175009.aspx Paul WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #1392313
 Posted Tuesday, December 04, 2012 7:41 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 27, 2013 10:01 AM Points: 161, Visits: 646
 For Information purposes only, copy/pasted your SQL and then added `FROM dual;` at the end to run it in Oracle and got the right answer without any further manipulations:`4020.73315`(running Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit)B
Post #1392490
 Posted Tuesday, December 04, 2012 7:59 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 8:49 AM Points: 1,172, Visits: 8,949
 bleroy (12/4/2012)For Information purposes only, copy/pasted your SQL and then added `FROM dual;` at the end to run it in Oracle and got the right answer without any further manipulations:`4020.73315`(running Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit)BYes, Oracle deals with integer division differently, so the result of integer division doesn't need to be an integer (e.g. the mathematically accurate way! )
Post #1392508
 Posted Tuesday, December 04, 2012 8:21 AM
 SSChampion Group: General Forum Members Last Login: Today @ 6:15 AM Points: 11,052, Visits: 10,814
 We should all switch to Oracle immediately!LOL Paul WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #1392530
 Posted Tuesday, December 04, 2012 8:31 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 27, 2013 10:01 AM Points: 161, Visits: 646
 We should all switch to Oracle immediately!LOL Paul White - SQL Server MVP ... I certainly was not suggesting such a radical step! - but I'm stuck at work and only have Oracle at my disposal, so always delighted when I can try stuff that works in both!
Post #1392539
 Posted Tuesday, December 04, 2012 8:31 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 8:49 AM Points: 1,172, Visits: 8,949
 SQL Kiwi (12/4/2012)We should all switch to Oracle immediately!LOLIt would make answering questions on the forum easier. We could just patronisingly point everyone to the homepage for Oracle documentation in order to give ourselves more job security
Post #1392540

 Permissions