🚀 go-pugleaf

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