Testing for is Not Null in an Or statement

[lawlerpat]

I have a query (works just great in SQL Server 200) in which I need to test on one of two conditions which due to my schema are usually mutually exclusive of each other.
Snipit:
...where (Field1 = 'aName' or Field2 is Not Null)
The problem is for the most part when Field1='aName', Field2 is almost always Null. Similarl and conversely, when Field2 is NOT null Field1 is not equal to 'aName'.
Therefore, I can not use the nvl(Field2,..) option because I end up getting records where neither condition is really met.
In a nut shell, I can't get the Or stmt to return true if the Field2 is NULL.
Please Advise
patrick

 

[SantaMufasa]

Patrick,

I'm sure we can help you, but I'm still not sure what condition with which you are looking for us to help. If "Field2 is NULL" and at the same time if "Field1 = 'aName'", then it should return TRUE for you.

So, restate your need, and we'll help.

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:56 (15Oct04) UTC (aka "GMT" and "Zulu"), 16:56 (15Oct04) Mountain Time)

 

[lawlerpat]

Let me try to be more clear.
I want to return true if either: Field1='aName' or Field2 is not Null.

My problem is when I find a record where Field1='aName' usually Field2 is actually NULL, which is fine by me but my understanding of Oracle and NULL is that a null field can not be evaluated, even for "IS NOT NULL" (at least not w/in an Or stmt). So even though my record meets the first requirement of the Or, I still don't get the record returned.
A'm I making myself clear?
I am sure that I could arrive at the same result with a union so before that solution is presented just know that that is not the one I am looking for.
Thanks again
Patrick

 

[SantaMufasa]

Patrick,

Absolutely, positively you can "evaluate" NULLs using "IS NULL" and "IS NOT NULL" even as a comparison operand on either side of an "OR". (Whoever told you otherwise probably just had a bad experience with "= NULL" or perhaps "!= NULL" and it coincidentally happened when also using an "OR" operator...but I digress.)

In your situation, if either "Field1='aName'" OR"Field2 is NULL", and there are no other conditions, then we can guarantee the row qualifies for the result set. If the row is not showing up, then neither of the above conditions is true. So, your WHERE statement is syntactically and logically valid; if you are not seeing rows you expect to see, then your data are bogus (such as Field1 = 'aNAME' or 'ANAME' or 'aname' et cetera; or Field2 looks empty but really has one or more spaces or unprintable characters.

Please let us know as soon as possible, because now you have me curious, and I don't wait very well (people say) .

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:20 (16Oct04) UTC (aka "GMT" and "Zulu"), 18:20 (15Oct04) Mountain Time)

 

[lawlerpat]

Mufasa,

I would love to believe you. However, I have had a lot of prior experience with Oracle (at lease 8 or 8i) where a field that truely is NULL will not evauate ture against anything except "is Null". In my test sample I have isolated the single row and If I ask for just that row by its handle and Field2 is NULL I get that row back. Similarly if I ask for that row by its handle and Field1='aName' I also get that row back. So it stands to reason that If I asked for (Field1='aName' or {anything}) that I would still get my row back. but as soon as I replace {anything} with "Is Not Null" the record is not returned.
I hope/wish I am wrong but as I said, I have had prior experience with ORACLE where the only way to evaluate a null field is to use NVL, but in this case that will cause records that I do not want returned to return.
You read this while I look at another thought?
fun stuff...

 

[lawlerpat]

Rest assured, I was wrong.
Thank you for pushing me.
You are correct that "Is Not Null" evaluates just fine.
I made a stupid mistake with case sensitivity and I should know better in oracle. For what its worth I had Upper(Field2) like.... but did not have the right side of the equation in upper case? Da!

Have a great weekend - Mine starts now.

Patrick

 

[SantaMufasa]

Patrick,

Now you really have me fascinated. Can you please copy and paste the exact scenario of which you speak? Show me the code and results proving what you say. I've never heard of such a situation. You have my attention!

Waiting anxiously,

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:44 (16Oct04) UTC (aka "GMT" and "Zulu"), 18:44 (15Oct04) Mountain Time)

(BTW, what time zone are you in?)

 

[SantaMufasa]

As you can tell, Patrick, we cross posted. I'm glad you resolved it to both of our satisfactions.

And, yes, have a great weekend !

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:59 (16Oct04) UTC (aka "GMT" and "Zulu"), 18:59 (15Oct04) Mountain Time)

 

[lawlerpat]

So this was my Error:
and (upper(u.sStatus) like 'Vacant Rented%' or u.dtAvailable is Not Null )

As soon as I put 'Vacant Rented' in all caps I got the record back even though the u.dtAvailable was NULL.

West Coast, off to dinner.
patrick