SQL Server Performance Forum – Threads Archive
Numeric Expressions/Formulas in Query
I’m in the process of building a site and converting views/tables/queries from an Access database to SQL. I’ve done this quite a few times, and never had any significant issues I couldn’t figure out on my own. In Enterprise Manager, I’ve created a view and in the query, I need to create an alias that is similar to below: SELECT ((monthmult) + ((b2avg*15)-(av2*10)) + (lp1+lp2) + ((b1avg*30)-(av1*20))) as PIndexValue which is how the formula reads in the Access view. However, when I got to run the query, SQL strips out all of the parentheses and calculates the value in left to right order: (monthmult + b2avg*15-av2*10 + lp1+lp2 + b1avg*30-av1*20) as PIndexValue Which gives me an incorrect value. Does anyone know why this is happening, or am I just unaware of the right way of doing it? Thank you, DerrickThe logic is that multiplication always comes first, and since you’re not grouping more than two factors in any set of brackets, there should be no difference between the calculations. You must be doing this in the query tool of Enterprise Manager, which is a bit of a nuisance: always adds OUTER to LEFT and RIGHT JOINs, always re-arranges the tabs and line feeds, and by the looks of it also second-guesses your bracketing. The solution is easy: use Query Analyzer, which doesn’t do any of those things.
I might be wrong but the parentheses are not needed here anyway. So I don’t understand your issue.
What value would you expect? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
Thanks for the help. The same result came up in Query analyzer also, but I think the issue was that the original formula designed by someone else in the Access database was incorrect, thus why I was getting incorrect values. However, I did learn that SQL processes equations like that in order of multiplication, division, subtraction and addition rather than using parentheses. I just wasn’t aware of that before. I tweaked the forumula a bit, and rebuilt it in a form that is a bit easier for someone else (whoever has to deal with it once the project’s done) to understand. Thank you for your time and your insight. It is greatly appreciated. Derrick
No, that’s not true!
SQL Server just follows common mathematical rules here and surely will take care of parentheses when they have an influence on the result. Consider this:
SELECT 1 + 2 * 3 AS Parentheses_Not_Needed
, 1 + (2 * 3) AS Same_as_before
, (1 + 2) * 3 AS Parentheses_influencing_result_thus_applied Parentheses_Not_Needed Same_as_before Parentheses_influencing_result_thus_applied
———————- ————– ——————————————-
7 7 9 (1 row(s) affected) —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
Of course the same result will come up in Query Analyzer, but at least QA won’t edit out any parentheses that you put in – not even if they don’t make a difference. If you enter the last of Frank’s three expressions in Enterprise Manager, it should leave those parentheses as they are, since it influences the result.
]]>