Re: [Q] Porting SQL to Oracle

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Wed, 11 Jun 2003 15:28:39 +0000
Message-ID: <bc7au0$7k7$1@ctb-nnrp2.saix.net>

Scott Mattes wrote:

> Null is a religous experience, so don't even try going there! Null is
> 'undefined' and guess what, Null does not equal itself (this seems silly
> to me, oops, here come the flames, since undefined is certainly the same
> as undefined, but religion sometimes doesn't make sense from the outside.

Beg to differ. And I will go there. SQL trekking through Oracle with shields on full, phasers charged and photon torpedos loaded.

I do not think it is at all confusing. Unless you happen to live in Choas Space. But then that would mean SQL is not a language for database communication, but is instead a frothy pink substance that is created by sound of opening the door of a motorcycle.

You have a VARCHAR2 column called x.

You do the following:
  INSERT INTO foo ( x ) VALUES ( '' )

Q. What is stored in column x?
A. ASCII/UniCode characters. Internally that is represented by a number,

   aka the ordinal value of the character.

Q. What is the ordinal value of the character in between the quotes

   that you are attempting to insert?
A. Undefined. There is no character value in between the quotes. Thus

   there can not be an ordinal value for it.

Q. How does Oracle store a value that is not given, i.e. the "nothing

   character" that is specified by that VALUES ( '' ) clause? A. Exactly what you told it to store - nothing. Nada. No value. Zip.

   Zilch. Niks. I.E. a friggen NULL.

Q. Is NULL a value?
A. No it is not. The above Q&A has just proven that NULL is NOT a value.

   NULL is not CHAR(255) or CHAR(13) or CHAR(0). CHAR(0) is still a    character _with_ a value - an ordinal value of zero!    You did not say:
     INSERT INTO foo ( x ) VALUES ( CHAR(0) )    You said:
     INSERT INTO foo ( x ) VALUES ( '' )    Which is why:

     x = NULL will always be FALSE as that is a value comparison and NULL
     is not a value

   Solution - use boolean algebra to see if the column has a value, or    whether the column has not a value, i.e.      x IS NULL?

Friggen straight forward and simple. Don't know why some wants to make a warp core breach out of it.

The Ferengi mentality of some.. trying to pawn of nulls as values <snorting in disgust>

--
Billy

Received on Wed Jun 11 2003 - 10:28:39 CDT