Re: [Q] Porting SQL to Oracle

Not religious, just the use of English.. '=' is not the same as 'IS' ...

One tests for equality ( that is, the same value) while the other tests for existence...

So while NULL cannot be equal to anything, even itself, it can, an often does, exist - so test for its existance..ergo, IS or IS NOT ....   "Scott Mattes" <Scott_at_TheMattesFamily.ws> wrote:

>See, didn't I tell you it was a religous type thing!
>
>Look, Hue-Mon, what care we for semantics? If'n I say 'where field = null'
>should produce a result of all records where that field is null; I shouldn't
>have to have special syntax of 'where field is null'.
>
>Save us from purists and bean counters!
>
>
>
>
>"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message
>news:bc7au0$7k7$1_at_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
>

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- Received on Wed Jun 11 2003 - 11:14:44 CDT