SQLServerCentral » T-SQL (SS2K8) » Math operation debuggingInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralMon, 16 Jan 2017 21:59:09 GMT20Math operation debugginghttps://www.sqlservercentral.com/Forums/FindPost1392185.aspxHi 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 ^notation
http://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
Tue, 04 Dec 2012 08:31:29 GMTchisholmdRE: Math operation debugginghttps://www.sqlservercentral.com/Forums/FindPost1392540.aspx[quote][b]SQL Kiwi (12/4/2012)[/b][hr]We should all switch to Oracle immediately!
LOL[/quote]
It 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 :-)Tue, 04 Dec 2012 08:31:29 GMTHowardWRE: Math operation debugginghttps://www.sqlservercentral.com/Forums/FindPost1392539.aspx[quote]
We should all switch to Oracle immediately!
LOL
Paul White - SQL Server MVP
[/quote]
:-D ... 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!Tue, 04 Dec 2012 08:31:24 GMTbleroyRE: Math operation debugginghttps://www.sqlservercentral.com/Forums/FindPost1392530.aspxWe should all switch to Oracle immediately!
LOLTue, 04 Dec 2012 08:21:25 GMTPaul WhiteRE: Math operation debugginghttps://www.sqlservercentral.com/Forums/FindPost1392508.aspx[quote][b]bleroy (12/4/2012)[/b][hr]For Information purposes only, copy/pasted your SQL and then added [code]FROM dual;[/code] at the end to run it in Oracle and got the right answer without any further manipulations:
[code]4020.73315[/code]
[i](running Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit)[/i]
B[/quote]
Yes, 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! :-P)Tue, 04 Dec 2012 07:59:59 GMTHowardWRE: Math operation debugginghttps://www.sqlservercentral.com/Forums/FindPost1392490.aspxFor Information purposes only, copy/pasted your SQL and then added [code]FROM dual;[/code] at the end to run it in Oracle and got the right answer without any further manipulations:
[code]4020.73315[/code]
[i](running Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit)[/i]
BTue, 04 Dec 2012 07:41:30 GMTbleroyRE: Math operation debugginghttps://www.sqlservercentral.com/Forums/FindPost1392313.aspx[quote][b]chisholmd (12/3/2012)[/b][hr]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?[/quote]
Within that complex expression are multiplications by the result of the following two computations:
[code="sql"]
SELECT (520 / (460 + 64))
SELECT (520 / (460 + 67))[/code]
Where both arguments to the division operator are integers, SQL Server performs integer division, returning zero in both cases. See [url]http://msdn.microsoft.com/en-us/library/ms175009.aspx[/url]Tue, 04 Dec 2012 02:59:49 GMTPaul WhiteRE: Math operation debugginghttps://www.sqlservercentral.com/Forums/FindPost1392198.aspxThank 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!Mon, 03 Dec 2012 17:23:02 GMTchisholmdRE: Math operation debugginghttps://www.sqlservercentral.com/Forums/FindPost1392193.aspxJust made everything at least 3 decimals. I think some of the integer math was dropping the decimals for you.
[code="sql"]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))))
[/code]
gives me: 4020.733146Mon, 03 Dec 2012 17:09:19 GMTDavid Webb-CDSRE: Math operation debugginghttps://www.sqlservercentral.com/Forums/FindPost1392192.aspxThis might be what you are looking for:
Check out Brandie Tarvin blog posting at:
[url]https://www.google.com/reader/view/?hl=en&tab=my#stream/feed%2Fhttp%3A%2F%2Fbrandietarvin.livejournal.com%2Fdata%2Frss[/url]
[quote]
Let’s start with precedence. 2 + 3 = 5. And 2 + 3 – 4 will always equal 1. Precedence in math and SQL is identified by the use of parenthesis. Everything is operated on from the inside heading to the outside. So we have two possible formulas: (2 + 3) – 4 or 2 + (3 – 4).
Here’s the difference
We start with (2 + 3) – 4 and work the parens first. The formula becomes (5) – 4. Then 5 – 4 = 1.
We start with 2 + (3 – 4) and work the parens first. The formula becomes 2 + (-1) where the addition gets changed to a minus due to the presence of a negative number. So now we have 2 – 1 (which is the same as 2 + -1) = 1.
Precedence becomes really important in cases of the division and multiplication operands: * or /. So when we have a formula like 2 + 3 * 4, our two possibilities are either (2 + 3) * 4 or 2 + (3 * 4). Now we have 2 possible answers, that of 20 or that of 14.
(2 + 3) * 4 becomes (5) * 4 becomes 20.
2 + (3 * 4) becomes 2 + (12) becomes 14.
[/quote]Mon, 03 Dec 2012 17:09:17 GMTbitbucket-25253