Re: Null's vs Empty string

efp_at_etechinc.com (Eric Palmer) wrote:

>On Sat, 2 Nov 1996 05:05:44 GMT, herman_at_iquest.net wrote:
 

>>Dick van Oordt <dvoordt_at_pi.net> wrote:
>>
 

>>
>>You should be aware that any numeric columns which are left NULL are
>>not considered to be zero in Oracle. You should ALWAYS use the NVL
>>function on numeric columns when doing any type of math. Example:
>>
>>select
>> num_col_1 + num_col_2
>>from
>> sometable;
>>
>>will not give the expected answer if either column is NULL. You
>>should always write this as:
>>
>>select
>> NVL(num_col_1,0) + NVL(num_col_2,0)
>>from
>> sometable;
>>
>>to make sure that a zero is added to num_col_1 if num_col_2 is NULL.
 

>I disagree. There are many cases where you want the numeric value to
>be left as NULL. You should only NVL convert to a 0 value if it makes
>sense for the type of data you have.
 

>Consider patient blood chemistry - a column for white cell count.
>A missing value (NULL) is much different than a value of 0.
 

>Nulls are here for a purpose. Ypu sure don't want to calc. an average
>value and include missing values as 0. It would be statistically
>incorrect.

There's no question that zero and null are very different.

I think the original poster was talking about varchar2 fields though. I had the same problem where SQL*LOADER was trimming trailing blanks in varchar2 strings. I had a varchar2 field as the last column in a primary key - as an optional course section suffix. With all trailing blanks trimmed, this optional code was converted to a null value when inserted by the loader.

But a space is a valid non-null code value - and this field as a PK component should not violate the uniqueness constraint. But Oracle turned it into a null and it did violate the constraint on no NULL primary key components. It took me a bit of asking around to learn the NVL technique of stopping Oracle from converting this PK component from blank to null.

• Robert Gordon