Calculated field is calculated wrong

Hi there,
(SQL Server 2000, running on Windows 2000 server, all service packs
installed)
I have a table with columns A, B and C, all three are tinyint. I added a
fourth column D, that has this formula:
(A * 3) + (B * 2) + C
This doesn't get calculated well, for example:
A = 8, B = 10, C = 10 -> D = 57
I worked around it by changing the formula into A + A + A + B + B + C which
works correctly, still I don't understand what is wrong with the first
formula... or is it a bug?
Any ideas are appreciated.
RayI could not reproduce this:
USE tempdb
CREATE TABLE t(A tinyint, B tinyint, C tinyint, D AS (A * 3) + (B * 2) + C)
INSERT INTO t (A, B, C) values(8, 10, 10)
SELECT * FROM t
A B C D
-- -- -- --
8 10 10 54
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"R. van Laake" <nospam_r.vanlaake@.dfk.nl> wrote in message
news:41f4c08d$0$14112$d456229f@.news.routit.net...
> Hi there,
> (SQL Server 2000, running on Windows 2000 server, all service packs
> installed)
> I have a table with columns A, B and C, all three are tinyint. I added a
> fourth column D, that has this formula:
> (A * 3) + (B * 2) + C
> This doesn't get calculated well, for example:
> A = 8, B = 10, C = 10 -> D = 57
> I worked around it by changing the formula into A + A + A + B + B + C whi
ch
> works correctly, still I don't understand what is wrong with the first
> formula... or is it a bug?
> Any ideas are appreciated.
> Ray
>
>|||Hmmm... still I get it.
I entered the formula using Enterprise Manager. When I enter:
(A * 3) + (B * 2) + C
EM changes it into:
([A]*3 + [B]*2 + [C])
which should be correct of course... but it does get calculated wrong
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uPiS6jfAFHA.4008@.TK2MSFTNGP09.phx.gbl...
> I could not reproduce this:
> USE tempdb
> CREATE TABLE t(A tinyint, B tinyint, C tinyint, D AS (A * 3) + (B * 2) +
C)
> INSERT INTO t (A, B, C) values(8, 10, 10)
> SELECT * FROM t
> A B C D
> -- -- -- --
> 8 10 10 54
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "R. van Laake" <nospam_r.vanlaake@.dfk.nl> wrote in message
> news:41f4c08d$0$14112$d456229f@.news.routit.net...
which
>|||Did you run the code that Tibor posted? What result did you get? If in
doubt avoid Enterprise Manager for data and schema changes - TSQL code
gives you more control. However, I couldn't reproduce the problem even
using EM - the result I get is 54.
If you still think there's a problem after trying Tibor's code then
post a complete, tested set of steps to reproduce the problem: CREATE
TABLE, INSERT followed by the exact steps you performed in EM. Also
tell us your product edition, version and service pack.
--
David Portas
SQL Server MVP
--|||Tibor's code runs just fine. I just changed my workaround formula
(A+A+A+B+B+C) back to (A*3)+(B*2)+C and now it does seem to work
correctly... I really don't understand. I am 100% sure there was no typing
error, and that the calculation was wrong. I even changed some values in the
A, B and C columns and saw a wrong calculation over and over again.
I will keep the (A*3)+(B*2)+C formula and if I see that it goes wrong
again, post here again.
Thanks for all your help,
Ray
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1106563785.886482.117750@.c13g2000cwb.googlegroups.com...
> Did you run the code that Tibor posted? What result did you get? If in
> doubt avoid Enterprise Manager for data and schema changes - TSQL code
> gives you more control. However, I couldn't reproduce the problem even
> using EM - the result I get is 54.
> If you still think there's a problem after trying Tibor's code then
> post a complete, tested set of steps to reproduce the problem: CREATE
> TABLE, INSERT followed by the exact steps you performed in EM. Also
> tell us your product edition, version and service pack.
> --
> David Portas
> SQL Server MVP
> --
>