Re: NULL values or Zero in numeric fields?

>>>>> "Steve" == stevec_at_zimmer.CSUFresno.EDU (Steve Cosner) writes:

Steve> In article <49ncqo$9oh_at_news.dot.gov> rfrazier_at_mailstorm.dot.gov Steve> (Richard Frazier) writes:

Steve> Keep in mind however, that when summing values across fields,
Steve> you can get some unexpected results when numeric fields are
Steve> null.  My own experience is that when one field is null,
Steve> none of the fields get computed.  So use caution, maybe the
Steve> 0 is the best answer....depends on the circumstances of
Steve> course.

Steve replies:

Steve> But it is not difficult, if you are aware that a column may
Steve> be null, to use   SUMX := SUMX+NVL(COLy,0); That is why the
Steve> NVL function exists.

I would consider using such a function as a dangerous prospect. As the NULLs are to be treated as unknown values, the sum of a column consisting (partially) of NULLs, will be unknown as well.

So the function that Steve mentions is to be treated as an some brand of an estimate-function, not as a sum-function.

Sum(1,2,3,4,NULL) = 10 + x
Steve-function(1,2,3,4,NULL) := Sum(1,2,3,4,NULL) where x --> 0 = 10

Michael. Received on Sun Dec 03 1995 - 00:00:00 CET