Re: Nulls get converted to 0 problem

On Friday, Jun 6, 2003, at 09:45 America/Chicago, scott.marlowe wrote:

> On Thu, 5 Jun 2003, Jon Earle wrote:
>
>> On Wed, 4 Jun 2003 terry@ashtonwoodshomes.com wrote:
>>
>>> Oracle *incorrectly* interprets blank (empty) strings as NULL.  They
>>> are NOT
>>> the same.  A string of zero characters is a string nonetheless.  A
>>> NULL is
>>> "the absence of value", which equals nothing (theoretically not even
>>> another
>>> NULL).
>>
>> If you're testing a value, you're testing to see if there's something
>> in
>> there or not - what difference does it make if the variable contains
>> 0, ""
>> or NULL?


If you even used a statistical package like SPSS, you will find that
null is a very important value.  Most statistical calculations
eliminate the null value since it implies that the value was not known
and therefore should be be used.

> Every interface I know of in every language (except cold fusion) has a
> test for null.  There IS a difference, and it's not a difference of
> just
> semantics, it has real world meaning.
>
> Enter a record for me.  Enter my cell phone number.  It's a text type.
>  If
> you enter a NULL you are saying I may or may not have a cell phone, you
> don't know.  If you enter '' you are saying that I do NOT have a cell
> phone.
>
> Hey, who has a cell phone we don't have numbers for?
>
> select * from table where cell_phone IS NULL;
>
> I don't have to make up a boolean to say what I mean when I put in a
> '' or
> a NULL.

We have a field in our tables which contains the date on which the
record was deleted (i.e. soft delete).  If there was no null value, we
would either have to add a deleted flag (not too bad) or have to
reserve a specific date to designate a deleted record (terrible).

> For numbers, a NULL should never be coerced to 0, which is what was
> happening to Ari due to the older jdbc driver.  Since blank numeric and
> date types aren't allowed there's no confusion issue.  But for text
> there
> certainly is a difference in meaning.

Actually it was happening to Avi :-)

The lack of testing for null is indeed a real issue with ColdFusion.
There is really no way to know if you received the empty string because
it was empty or because it was null.

Avi
-
Avi Schwartz
avi@CFFtechnologies.com