When running the query:

SELECT  SUM(field1 + field2 – field3) as sumfields

where some fields in records have Null values, the calculation returns wrong answer.
It seems that null is not always treated as value 0. (Datatype is Currency - I have not tried other datatypes)

If I run:
UPDATE myTable SET field1=0 WHERE field1=null;

I then get the right answer when running the first query again.


Regards,
Geir Bratlie

Geir Bratlie

<<
When running the query:

SELECT  SUM(field1 + field2 – field3) as sumfields

where some fields in records have Null values, the calculation returns wrong answer.
It seems that null is not always treated as value 0. (Datatype is Currency - I have not tried other datatypes)
>>
In SQL every operation with NULL always return NULL and this is not an error.

To avoid this situation and solve "your" situation for every field that can be null you must use COALESCE

SELECT  SUM(coalesce(field1,0 + coalesce(field2,0) – coalesce(field3,0)) as sumfields

The function COALESCE works like if there is NULL there assume "0" (zero) or anything else you want.

Eduardo (HPro)

Geir


I'll go along with Jose about using COALESCE to prevent the problem but out of interest what was the error?

Roy Lambert

The query resultet in a wrong sum

In this case the right summery was abt 800.000, and it resultet in abt 780.000 bacause of some null values.

After setting all NULL values to "0", the summary was correct.

In my opinion, Null should be calculated as "0" if it is a numberic field.

-Geir-

Geir


>In my opinion, Null should be calculated as "0" if it is a numberic field.

DO NOT repeat NOT get me started on the behaviour of nulls and sql - if you do you'll regret it!

Roy