Re: [Q] Porting SQL to Oracle

then of course there is

create table tab1(col1 number,col2 number);

insert into tab1(col1) values(1);

commit;

create table tab2(col1 varchar2(255),col2 BLOB); insert into tab2(col1) values('Quark, Strangeness and Charm'); commit;

What should

select tab1.col1,tab2.col1
from tab1,tab2
where tab1.col2=tab2.col2;

return. Well as UNDEFINED=UNDEFINED, clearly the correct answer is 1, 'Quark, Strangeness and Charm'

Madness, Madness. They call it Madness.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"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