Re: Weird Query

Hi,

> Well, it's simply an agreement, i could say that en empty string doesn't
> compare with any "real" value too, but it's not the point, the point is
> , that you should write your sql *according* the rules which are adopted
> in the rdbms engine.

I am absolutely willing to do so. I can't which is why I posted my question.

How would a WHERE-Constraint look that returned only the rows in which a given field is not empty? "Not empty" here being defined as "something to see" or "text to read". I am not (at this time, anyway) interested in the very fine distinction between NULL and an empty string and how it is handled by Oracle. I just want those records that hold some text.

> it is very irrational to expect that Oracle behave equally those
 > SQL Server's.

I don't.

I fully expect differences and am prepared for them. In fact the program whose malfunction I traced to the problem being discussed here can as of now use Paradox, MS Access, MS-SQL and Interbase/Firebird as backend. In between them this makes for a very "interesting" database access layer :-).

> do you expect the very *same* row to be
> returned for both where clauses - WHERE PP_LOCK IS NULL and WHERE
> PP_LOCK IS NOT NULL?
No.

I expect "WHERE PP_LOCK=''" to return one set of rows A, "WHERE PP_LOCK<>''" to return another set of rows B and the intersection of A and B to be empty. The results of "equal" and "does not equal" should be distinct.

I further expect "WHERE PP_LOCK IS NULL" to return another set C and I do not care whether C is included in A or not. The _Union_ of A, B and C should however in my very limited understanding of relational logic consist of _every_ record in the table.

If it's NULL it's in C. If it's not NULL it either has some text or an empty string. So it's in A or B.

This is not the case here, though. My test record is in none of A, B or C. And very clearly it does _not_ have NULL in PP_LOCK, I count 47 characters of text.

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"