Re: Weird Query

Hi,

> NULL means Unknowable, so NO, you cannot tell Oracle
 > that some/all NULLS are anything but NULL.

I agree with that. NULL is NULL. What Andreas and I are getting at is the notion that an empty string is different from a NULL.

> ( A Zero value string has a length of 0, so it cannot have a
 > value ( what would be known about it other that its length?))

There is exactly one string of length 0. There are n (the value of n depends on character-set of course) strings of length 1. There are n*n strings of length 2. An empty string is a real entity I would argue.

> Why do you care.

Well. My program did barf at it, so my clients care.

And no, it is not completely broken, it runs perfectly on a wide range of other DBM-Systems :-).

> When using it in a Where clause test for both blanks and Nulls with
> ( If trim( stringField) = '' or
> stringField IS NULL)

Wait a minute. Didn't we just learn that:

 >> You can blindly substitute '' by NULL

And in what way does my direct comparison "field=''" differ from your "trim(field)=''"? What makes it different to Oracle?

> Or use Oracle's NVL function to have Nulls return a specified value

Call me stubborn but I do not want the NULL in the first place. I want an empty string. I said so when INSERTing. If the database cannot comply with that I would rather have it raise an error than losing data :-).

All that does not mean there is no way to make it work, I am just trying to justify my surprize at the behaviour.

Ciao, MM

-- 
Marian Aldenhövel, Rosenhain 23, 53123 Bonn. +49 228 624013.
http://www.marian-aldenhoevel.de
"It's easy to make a small fortune in aviation,
  provided you start with a big one"