Re: Oracle NULL vs '' revisited

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Fri, 17 Aug 2007 13:33:22 -0500
Message-ID: <46c5dde7$0$32552$88260bb3@free.teranews.com>

Thomas Kellerer wrote:
>
>
> Serge Rielau wrote on 17.08.2007 19:38:

>>> I would be interested: if this is important for character columns, 
>>> then I assume this is important for other columns as well.
>>> How do you implement that distinction for e.g. NUMBER or DATE columns?

>> If I get an assignment and I hand in an empty sheet that would be well 
>> empty. I typed zero characters.
>> If I don't hand it in it is missing. It is not decidable whether my 
>> work is good or bad, long short.. there is the NULL.

> If I don't hand it in, I don't "create a row in the database" that is
> something different compared to handing in an empty "row/assignment"

Depends on your data model doesn't it? What if the table looks like this:

STUDENT     EXAM1   EXAM2
Bob	      75       82
Tom           83
Kristi        94       92
Chick         56       47

So what does the entry for Tom in EXAM2 mean? There is a row in the table after all!

>> For numbers I think one can reasonably argue that 0 is the equivalent 
>> of empty.

> Hmm. I get an an assignment where I should calculate something. I hand
> in a sheet where I put 0 (zero) as the solution. So that is the same as
> handing in an assignment where I didn't enter anything.
> Doesn't sound logical to me.

I don't think that I'd ever equate 0 to empty for numbers. But maybe that's the mathematician in me. When computing averages or min values, a 0 can have a profound affect on the result where as no value can mean something else. Take for example the following set of numbers:

{0, 11, 22, 33, 44}

The average is 22 and the min is 0. Now take these numbers:

{NULL, 11, 22, 33, 44} Now the average is 27.5 and the min is 11.

So for those reasons, I would not reasonably argue that 0 is the equivalent of empty. Of course...it does depend on how you are going to use this data. There are cases where it does not make any difference.

>> I don't think that there is a similar "empty" concept for dates.

> That's my point. Character seems to be the only data were everybody
> requires the distinction between "nothing" and "empty" but nobody has
> ever requested this distinction for dates or numbers.
>

>> Question: If empty strings make no sense for VARCHAR, why have them 
>> for CLOBs? AFAIK Oracle has helper functions to produce empty lobs.

> I think that is a mere technical reason as for the CLOB some "management
> structures" (such as a locator) need to be created while this is not
> necessary with character data.

Agreed.

Cheers!
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

-- 
Posted via a free Usenet account from http://www.teranews.com

Received on Fri Aug 17 2007 - 13:33:22 CDT