🚀 go-pugleaf

In article <36DD8F52.A95841B5@nospam.mcbsys.com>, Mark Berry politely 
paraphrased ...
> Are "<>" and "NOT =" really supposed to give different results?  Is
> Nelson correct that it has something to do with NULL?
> 
> 
Not true= false // and not NULL
Not false=true  // and not NULL


-- 
Romain Strieff [TeamB]
Please read this newsgroup guide below before 
posting on the private Inprise newsgroups.
http://www.inprise.com/newsgroups/guide.html
--

On Wed, 03 Mar 1999 11:36:50 -0800, Mark Berry <markb@nospam.mcbsys.com>
wrote:

[...]

>> And guess what, my one record was returned! Very strange this SQL...  After
>> some reading it turns out that SQL has three logic values, TRUE, FALSE or
>> NULL. My theory is that for some reason <> returns a NULL condition on an
>> unpopulated RecType like that used by the '90CF' record. This is probably
>> due to the fact  that <> tests if a value exists (greater than or less than)
>> where as the NOT test that a value does not exist.
>
>Are "<>" and "NOT =" really supposed to give different results?  Is
>Nelson correct that it has something to do with NULL?

That's what I figured the problem was -- before I even got that far down in
the article. Yes, the methods used in SQL (not just local SQL) are
different for NULL values. You cannot use the normal comparison operators
available in local SQL: NOT =, <>, or !=. You have to use the NOT NULL
predicate.

For example:

  SELECT *
  FROM ContSupp C
  WHERE (C.AccountNo = "90CF-SYSTEM-REC") AND
    ((C.RecType != "H") OR (C.RecType IS NULL))

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

Steve Koterski wrote:
> 
> On Wed, 03 Mar 1999 11:36:50 -0800, Mark Berry <markb@nospam.mcbsys.com>
> wrote:
> 
> 
> That's what I figured the problem was -- before I even got that far down in
> the article. Yes, the methods used in SQL (not just local SQL) are
> different for NULL values. You cannot use the normal comparison operators
> available in local SQL: NOT =, <>, or !=. You have to use the NOT NULL
> predicate.
> 
> For example:
> 
>   SELECT *
>   FROM ContSupp C
>   WHERE (C.AccountNo = "90CF-SYSTEM-REC") AND
>     ((C.RecType != "H") OR (C.RecType IS NULL))

Thanks, Steve, but as Nelson said, it seems to work fine with just 

  NOT C.RecType = "H"

Let's see if I understand this...  The "=" operator will find the "H"
row(s), so NOTting it will return all the other rows, including RecType
of NULL (which is, I assume, how it sees an empty value in a table,
though I didn't think dBASE5 tables supported null values).  

I have to admit it still isn't clear to me why that gives a different
result than 

  C.RecType <> "H"

In that row, we're asking "Is NULL <> 'H'?".  So the result is either
True, or did I read somewhere that any expression with NULL in it
returns NULL?

If you can recommend a good book where I can find an explanation, by all
means...

Thanks,

Mark Berry