SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Math operation debugging


Math operation debugging

Author
Message
chisholmd
chisholmd
SSC Veteran
SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)

Group: General Forum Members
Points: 266 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 ^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

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7881 Visits: 25280
This might be what you are looking for:

Check out Brandie Tarvin blog posting at:

https://www.google.com/reader/view/?hl=en&tab=my#stream/feed%2Fhttp%3A%2F%2Fbrandietarvin.livejournal.com%2Fdata%2Frss


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.



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1584 Visits: 8586
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
chisholmd
chisholmd
SSC Veteran
SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)

Group: General Forum Members
Points: 266 Visits: 86
Thank you Mr. Webb! That did it all right. So glad I posted here I would have never tried that Smile

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!

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15922 Visits: 11355
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 White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
bleroy
bleroy
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 739
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
HowardW
HowardW
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2257 Visits: 9892
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)

B


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)
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15922 Visits: 11355
We should all switch to Oracle immediately!

LOL



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
bleroy
bleroy
SSC Veteran
SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)SSC Veteran (258 reputation)

Group: General Forum Members
Points: 258 Visits: 739

We should all switch to Oracle immediately!

LOL

Paul White - SQL Server MVP


:-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!
HowardW
HowardW
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2257 Visits: 9892
SQL Kiwi (12/4/2012)
We should all switch to Oracle immediately!

LOL


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 :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search