Re: NULL problem

} Date: Fri, 24 Feb 95 17:10:52 +0800 } From: Ti Lian Hwang <tilh@sin-co.sin-ro.DHL.COM> } To: informix-list@rmy.emory.edu } Subject: NULL problem } } { } Anyone knows how to resolve this ? } } If the value of b in the } } select * from test where a = b } } is a NULL , the select statment will not find anything, even though the } relevent record exists. See example below. } } The select works if } } select * from test where a is NULL } } But, if the value of b is a variable in a 4GL, you can't code everything as } } IF b is null then } select statment 1 } else } select statment 2 } end if } } Is this a bug or a feature of 4GL :-) } } } { } ---------------------------------- } Ti Lian Hwang - DHL Singapore } email : tilh@sin-co.sin-ro.dhl.com } ---------------------------------- } } This is neither a bug nor a feature of 4GL. This is *defined* behavior for SQL. A NULL is by definition an unknown value. It is neither equal nor not-equal to any other value *including* another NULL. The only where clause that will return true for a NULL is "where column_val is null". All other where clause variations will return false for any column containing a NULL. Regards, Alan +---------------------------+-----------------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | Martin Marietta, SLS | Voice: 303-977-9998 | | P.O. Box 179, M/S 3810 | Standard disclaimers apply. Cutesy ones, too. | | Denver, CO 80201-0179 USA | Your mileage may vary. Void where prohibited. | +---------------------------+-----------------------------------------------+