Re: Weird Query

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Mon, 01 Aug 2005 14:04:50 +0200
Message-ID: <42ee10f6$0$11755$9b4e6d93@newsread4.arcor-online.net>

Marian Aldenhövel schrieb:
> Hi,
>
> > What exactly do you think is wrong in the results?
>
> The query
>
> SELECT PP_LOCK FROM cbr_385120060b39d42d8 WHERE PP_LOCK <> '';
>
> Does not return any rows although there definitely is exactly
> one row that satisfied the condition. This is proven by the result
> of the query without any constraints.
>

>> You know, the '' is equivalent to NULL.

>
>
> No, I did not know that. I am used to a ternary logic in SQL servers
> where NULL does not compare to any "real" values.

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. That means, in SQL Server's where you've worked before '' and NULL relationship is different to the Oracle Server and you are currently working on the Oracle and it is very irrational to expect that Oracle behave equally those SQL Server's. They are different.
>
> But this is not the point here as I am trying to query those records
> that _do_ have text in that field.
>

>> The first query delivers the 1 existing row with a *NOT NULL* value 
>> for pp_lock ('STEFANWIMMER\goit consulting\658.1_at_STEFANWIMMER'), all 
>> following have WHERE CLAUSE evaluated to FALSE and as result no rows 
>> were returned.

>
>
> Right. But should not exactly one of
>
> WHERE PP_LOCK<>''
> WHERE PP_LOCK=''
> WHERE PP_LOCK IS NULL
>
> be TRUE for _any_ value of PP_LOCK?

The 1 and 2 condition are false for *every* value of PP_LOCK ( those which are NULL including ), indeed, it doesn't matter , what value has PP_LOCK , you never get rows returning
The 3 is TRUE only for PP_LOCK values which are *NULL* and is FALSE for the rest

My transscript shows that the sample
> record is not returned for either constraint. The fourth "IS NOT NULL" does
> in fact return the record but that is not too surprising :-).

That i really don't understand, 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 ?
I can not follow your logic...
>
> Ciao, MM

Best regards

Maxim Received on Mon Aug 01 2005 - 07:04:50 CDT