Re: Warning about null and open question t

In article d2b_at_nn.fast.net, mccusker_at_fast.net (Jim McCusker) writes:
> In article <Pine.NXT.3.90.950613090306.17269C-100000_at_iluvatar>,
> John Jones <john_at_iluvatar.tip.duke.edu> wrote:
> >I appreciated everyone's response to this, but the majority of responses
> >have been to just wrap a nvl around everything. That is ok, but when you
> >are comparing around 50 or more columns that is a lot of typing and as a
> >programmer I look for ways to cut down on typing as much as possible. I
> >just think that NOTHING should be equal to NOTHING. I have heard that
> >other databases do this and was really just sounding off hoping Oracle
> >would do the same. Oh well, can't have everything I guess. Thanks for
> >listening.

If you want to compare a number of character columns in two records to see if one or more is different but don't want to use hundreds of NVL's you can do the following:

IF a.ename||chr(9)||a.address||chr(9)||to_char(a.phone)   != b.ename||chr(9)||b.address||chr(9)||to_char(b.phone) THEN

This bypasses the "null != null" issue by concatenating a non-null character to the end of your field, which turns it into something other than null. Things you need to know when doing this:

1. Pick an obscure character which is NOT going to occur in the data to seperate your columns with.
2. Concatenating stuff onto a column makes it unusuable by indexes.
3. You need one seperator between each field, not one per line, for this to work properly.
4. You need to exercise caution about how you To_Char numbers and dates.
5. If you are going to do this often write a script which looks up USER_TAB_COLUMNS and formats everything for you. Otherwise you will go slowly mad trying to maintain it.

Personally I'm a big fan of NULL. "Null != Null" is irritating but has to be that way to be consistant with everything else. I can't help but think that some of the criticism of NULL comes from people who've never taken the time to understand the difference between a relational database and a file server.

David Rolfe,
SunSoft,
Mountain View.

My Opinions are my own.   Received on Tue Jun 13 1995 - 00:00:00 CEST