- Home
- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges

hello experts - I came across behaviour I don't understand today. I was calculating some values from a temporary table where my source value was stored as an integer. There was an error in the calculation which was spotted and corrected, but the correction had no effect. I then altered the datatype of the source value to numeric and found that the issue was resolved. This is illustrated in the example below; the calculation was missing parentheses around the '9*(Num)' part of the calculation, but adding it did not change the calculation result. It was only when the datatype of the source column was changed that the result was corrected, i.e. the result named Calc_with_parentheses in the second query below is correct. Can anyone explain please why adding the parentheses to the calculation did not correct the issue and why changing the datatype was required? or, would there have been a better solution? Thanks for taking the time to read, Mickey CREATE TABLE #test1 ( ID INT, Num INT ) INSERT INTO #test1 VALUES ( 1, 100 ) INSERT INTO #test1 VALUES ( 2, 150 ) INSERT INTO #test1 VALUES ( 3, 200 ) INSERT INTO #test1 VALUES ( 4, 250 ) INSERT INTO #test1 VALUES ( 5, 300 ) SELECT * , Num * POWER(( 1 - ( 1 / 9 * ( Num ) ) - ( 1.959964 / ( 3 * SQRT(Num) ) ) ), 3) AS Calc_missing_parentheses , Num * POWER(( 1 - ( 1 / ( 9 * ( Num ) ) ) - ( 1.959964 / ( 3 * SQRT(Num) ) ) ), 3) AS Calc_with_parentheses FROM #test1 ALTER TABLE #test1 ALTER COLUMN num NUMERIC(10,6) SELECT * , Num * POWER(( 1 - ( 1 / 9 * ( Num ) ) - ( 1.959964 / ( 3 * SQRT(Num) ) ) ), 3) AS Calc_missing_parentheses , Num * POWER(( 1 - ( 1 / ( 9 * ( Num ) ) ) - ( 1.959964 / ( 3 * SQRT(Num) ) ) ), 3) AS Calc_with_parentheses FROM #test1

datatypecalculation
Comment

**17** People are following this question.

Copyright 2022 Redgate Software.
Privacy Policy