Math operation debugging

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Math operation debugging

Math operation debugging

Rate Topic

Display Mode

Topic Options

Author

Message

chisholmd

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 ^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)

Post #1392185

bitbucket-25253

Posted Monday, December 03, 2012 5:09 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 5,123, Visits: 20,370

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

Post #1392192

David Webb-CDS

Posted Monday, December 03, 2012 5:09 PM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:19 AM
Points: 834, Visits: 5,839

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

chisholmd

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!

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Post #1392198

Paul White

Posted Tuesday, December 04, 2012 2:59 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 10,990, Visits: 10,578

chisholmd (12/3/2012)

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #1392313

bleroy

Posted Tuesday, December 04, 2012 7:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:22 AM
Points: 141, Visits: 558

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

HowardW

Posted Tuesday, December 04, 2012 7:59 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:06 AM
Points: 1,092, Visits: 7,933

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! Tongue

)

Post #1392508

Paul White

Posted Tuesday, December 04, 2012 8:21 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 10,990, Visits: 10,578

We should all switch to Oracle immediately!

LOL

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #1392530

bleroy

Posted Tuesday, December 04, 2012 8:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:22 AM
Points: 141, Visits: 558

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

HowardW

Posted Tuesday, December 04, 2012 8:31 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:06 AM
Points: 1,092, Visits: 7,933

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 Smile

Post #1392540

« Prev Topic | Next Topic »

Permissions