SQL statement on SQL Server 2000

Can anyone figure out why these statements return totally different results?

'This Query Returns 0
SELECT SUM(LIFE_GDC) AS Expr1
FROM sysdba.C_WBY_TMAGICPROD
WHERE (PROD_DAY > '12/31/2002') AND (UPPER(CONTACTID) = 'C6UJ9A00347P')

'This Query Returns -1.81898940354586E-12
SELECT SUM(LIFE_GDC) AS Expr1
FROM sysdba.C_WBY_TMAGICPROD
WHERE (PROD_DAY > '12/31/2002') AND (CONTACTID = 'C6UJ9A00347P')

Running either one of these queries with a SELECT * in place of line 1 shows you the same 31 Records in the table. The data in the LIFE_GDC column is all zeroes with the exception of 4 records with 6895.46, -22233.2, 6815.64, and 8522.1 as the only non zero values. Clearly they add up to zero, yet if I don't have that UPPER applied to my CONTACTID I get erroneous results. Is this a Microsoft bug?

You might have missed that this is a forum for MySQL

I am a little slow but why does that matter? Wouldn't queries in MySQL and SQL 2000 be similiar?
Thanks!

The addition of 'upper' will convert any contact IDs that are in lower case to upper case. Check if any are in lower as the bottom query will exclude those in lower case.

Not sure if that resolves it or not.

Well, for one, those are not totally different results.
-1.81898940354586E-12 is basically 0.
I'm not sure why the difference occurs. The way you have written your query shows that the id is all uppercase anyway, which is all that the Upper() function will do for you. Purely speculating now, you often find that computers don't really store '0' as '0', which is why you get those little tiny fractions, which are functionally equivalent to 0. When you add the Upper() to the query, the program may truncate the numbers in the table for some reason, before running the sum. This could explain the difference, since without the Upper(), the raw numbers are used without any prior work being done.
Anyway, thats just my conjecture.
HTH
Dave

Thanks Mooseman.
It doesn't appear that any of the ContactIDs are anything but a mixture of Upper Case Characters and Numerals. The wierd thing though is that when you run the queries the order of the returned records are different. It appears that SQL thinks the Contact IDs are different. But what doesn't make sense to me is why would summing be different on the exact same record set depending on which order you sum them?

Thanks Karsh,
The only flaw I can see with your thoughts are that the Upper is being performed on the ContactID, not in the actual sum. Basically, I see it mentally as "Find these records with UPPER(CONTACTID) =..." and now sum them. It seems like the order in which the records are summed makes a difference when it shouldn't.

The Upper is being done before the Sum is calculated, making a sort of temporary table where the Sum is calculated. I'm guessing thats where the truncating occurs. For the statement without the Upper, the Sum is calculated from the original table.

Thanks Karsh,
That makes sense now. I guess I need to find out what's getting into my base table. I am importing decimal data up to 2 places as far as I know. Therefore, the sum being close to zero but not zero doesn't make sense. I'll check further.

Checking is good, but you just need to remember that because you think/see 2 decimal places, the computer actually has more stored. If it doesn't cause problems, I would say just leave the Upper() statement in, and accept the 0 you get that way.
Good luck

i should like to see the 31 records for myself

surely that's not too many for you to paste here?

that would eliminate a lot of guessing

i'm wondering if thedude has noticed this isn't the mysql forum...

Posting the records definitely could help.
(And it was in the mysql forum, it was moved)

doh!

sorry, that certainly would explain thedude's remark

sorry

Here is the final outcome. We are using SalesLogix 5.2 on SQL Server 2000. We have a table, TMAGICPROD, that stores production data. The guy who helped us set up the database recommended Long Integer fields for the sales data initially. Then, when rounding and decimals were an issue, had us change to Float data. Turns out that with Float data, you can enter a value as 6895.46 and SQL Server puts it in the fields something like 6895.4600000000181898940354586
Go figure. (Please read next post for additional info.)
Anyway, the fix was to change the Float fields to what SalesLogix calls Currency fields, same as Numeric(15,2), which should handle our needs for Sales fields that are actually dollars and cents anyway, with plenty of accuracy.
The reason the upper worked was as Karsh said, that function truncates the value in a temp table, therefore chopping of the non zero data.
Thanks for your help everyone!