SQL server 6.5 mis(t)ery

[Cakkie]

I have this strange problem, I have a query that looks like this
I'm using SQL Server 6.5 SP 5a
select unitprice , amount, convert(decimal(15,2), unitprice * amount) as total
from tblOrders


where unitprice is of the type real, and amount of the type float
This gives me a result that looks something like this
unitprice amount total
------------------------ ------------------------ -------------------
1401.49 17.5 24526.00
73.2208 348.0 25480.84
224.0 1.0 224.00
1116.12 152.0 169650.00
5454.0 1.0 5454.00
900.868 152.0 136932.00
7636.0 1.0 7636.00
72.3334 475.5 34394.51
640.0 1.0 640.00
1250.0 40.0 50000.00



At first, this looks fine, but at second...
Take the first line, 1401.49 * 17.5 = 24526.075, or rounded 24526.08
The second line is ok, so is the third
The fourth line however, 1116.12 * 152.0 = 169650.24
Or take this line, 72.3334 * 475.5 = 34394.5317, now explain me how he gets 34394.51

Has anybody ever encountered this problem, as you can imaging that accounts payable isn't very happy with these results.


Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[phunkydude]

Hi Cakkie

Haven't used SQL6.5 for a few years, so I can't even remember if it had a money dt. Try convert to a money then to decimal and check if there is any difference?

[Cakkie]

Been there, done that, still wrong


Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Clearcode]

I use Sybase here and it doesn't exhibit that behaviour.

Try using:

convert(smallmoney,(amount * unitprice)))




HTH,
Duncan

-------------------------------------------------
Ex. Datis: Duncan Jones
Merrion Computing Ltd
http://www.merrioncomputing.com

[TH1]

I put your data into my Sql Server 7 database and it works fine maybe you need a service pack for SQL 6.5 (If there's one available) cause it may be a bug

[Cakkie]

I applied servicepack 5a two week ago, up until that, that was the latest.

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook