Re: [Q] Porting SQL to Oracle

andrewst_at_onetel.net.uk (Tony)

> > Define what value is in between the quotes for ''.
>
> It is the empty string. It is the character analogue of the number 0.
> It is a string of 0 characters.

Char(0) still takes up a single byte or character in a string. A string with '' does not contain a Char(0). The string is undefined as it does not contain anything.. i.e. the string does not exist. It has not been constructed.

For it to be a string, it must contain characters. For a number to be a number it must have a numeric value.

Is infinity a number? By the same token, is a '' a string when it does not contain a _single_ damn character?

WHAT IS THE STRING VALUE OF ''? If it does not have a value, how the hell can you say that is a string?

> So NULL and 0 are equivalent values for a NUMBER column, are they?

No. Read the above again. '' means no characters in string. I.e. the string has no value. I.e. the value of the string is undefined. I.e. there is no string. (or should that be spoon? ;-)

'' to a string is not the same as zero to a number. Zero as a number is a numeric value. '' as a string is meaningless as it does not define a string. There is not a friggen single character value in ''. So don't claim that '' is in fact a string. '' is just another symbol or depiction for the state of NULL.

> (sigh) So for numbers, 123+NULL = 123 does it?

No it does not. I specifically _exluded_ mathematical operations. Read my posting again.

'123'||NULL is '123' as you concat/add (non-math wise) nothing to the string. I.e. suffix the string with nothing/null.

123*NULL means perform an math operation where the value is unknown. Oracle should return an ORA-9999 "Remove brains from ass". ;-)

But seeing that Oracle is less crude, it tells you that the result of such a math operation is unknown... and unkown means NULL. And that is _exactly_ what you get.

> According to your
> definition of NULL it should. But it doesn't does it? Because NULL
> means "value unknown".

No. NULL does not mean unkown value. It means that there are no value. NULL is a state like infinity. You are still thinking of NULL as having to have some kind of value, i.e. an "unknown value".

Infinity is not an "unknown number".

> SQL says:
> 123+NULL = 123+[some unknown value] = [some unknown value] = NULL
> To be consistent then it should also say:
> 'ABC'||NULL = 'ABC'||[some unknown value] = [some unknown value] =
> NULL
Nope. Again. A concat operation DOES NOT EQUAL a maths operation.

> I believe this is what some other DBMSs do (SQL Server maybe, where
> the original questioner started from?)

Then those DBMSs are _wrong_. Period.

> SQL> select length('') from dual;
>
> LENGTH('')
> ----------
>
> You like that? You find that useful and sensible? Why is it not 0?

Because the string is _undefined_. It has no value. IT DOES NOT EXIST. It is like asking how many digits infinity has. Infinity IS NOT A NUMBER. It is NOT a value.

Ditto for NULL. NULL is not a value. It does not have a size. NULL means IT DOES NOT EXIST.

NULL = before creation.

value = after creation.

Makes perfect sense.

> You want a formal proof? I'm no C J Date, but how about this for a
> starting point (perhaps someone with more formal training can
> improve):
>
> Axiom: LENGTH(s1||s2) = LENGTH(s1)+LENGTH(s2)
>
> Let s1='ABC', s2=''

And that is where your argument derails. You are treating NULL as a value. Again.

This should be
  Let s1='ABC'
  s2 is NULL (i.e. s2 does not exist - it is simply

              a name of a reference of a string that 
              may be created) 

> If NULL = '' then it follows that NULL is a VALUE with a property
> length=0 also.

Nope. Again. NULL != ''. NULL != NULL. '' implies a NULL state. It is just another way to depict a NULL. Only, with '' you are depicting NULL as being of the class or domain of string as oppose to class/domain of number.

You can not assign NULL to s2. You "place" s2 in a NULL state. You are in fact specificaly saying I AM NOT CREATING A STRING S2, BUT A REFERENCE TO A STRING I MAY CREATE.
> But NULL is not a value, it marks the ABSENCE of a value.
> Contradiction!

The only contradiction is claiming that
  '' = NULL It is not. Because:
  NULL != NULL
> But I can see you are a lost cause. Long ago you read the Book of
> Oracle, and it said that '' and NULL were the Same Thing, Now and
> Forever.

Nope. This is a *BASIC* *CONCEPT* in dealing with classes and instances and OO design and development.

Get into some OO programming. See for yourself. And stop abusing NULL as a value. It is not.

--
Billy