Re: Nulls working strange

Because that's the way that NULLs are supposed to work. A NULL represents an "UNKNOWN" value in a column. If the engine does not know what value should be in that column how can it tell if the row matches "X" or not? This behavior is exactly as described by Codd and Date. The query you want is: select count(*) from tcc_subscription where acodes matches "*D*" and (ad_or_m != "X" OR ad_or_m IS NULL); Which will return the expected count of 372. Art S. Kagel ----- Original Message ----- From: Jay <jay2@iafalls.com> At: 6/ 9 12:16 > I am on Unix Sco 5 and Informix 7.2 > > I don't think my queries are running correctly. For example > > If I run the query > > select count(*) from tcc_subscription > where acodes matches "*D*"> > I get 440 -- which is correct. > > If I run > select count(*) from tcc_subscription > where acodes matches "*D*" > and ad_or_m = "X"> > I get 68 -- which is also correct > > If I run > > select count(*) from tcc_subscription > where acodes matches "*D*" > and ad_or_m != "X"> > I get 0 -- I should get 372 > > the field ad_or_m is either null or it contains X. I don't understand why if > the field is null, it won't register as != X or not matches X > > > Any suggestions