Re: Re Where != and NULLS

Malcolm Weallans wrote: > [snipped for our mail-served folks who don't like the lengthy stuff] > > I think you'll find that NULL means the absence of a value AND don't > know. If a column has never had a value put into it it will have a NULL > value. Similarly if a field on a FORM does not have a value entered into > it then it is left NULL but in this case no answer was provided but the > question was asked so it must mean DON'T KNOW. > > Malcolm Weallans > Online Database Consultancy > Phone 01628-72154 > Fax 01628-37463 > CIX - onlinedbc Malcolm, :-) I should've dug my copy of the book out so I don't get into a memory problem here, but essentially, I did not know that NULL meant an "absence of value". I thought it simply meant there's a value here but it's not reliable for IF statements, except when you're doing the IF value IS NULL THEN... These kinds of IF clauses by themselves without some other check <can> give you a headache when your expectations are to find NO value in an IF comparison. INPUT ARRAY is one such example where checking for NULL values on AFTER FIELD can yield the correct result but not what you hoped, if you think of NULL as nothing. The field may indeed be NULL and contain garbage. You were looking for NOTHING but NULL checking did not give you this result. Instead you got the correct answer, "It's NULL, but not exactly nothing". Good logic. Wrong answer. :-) I want to see if the value is truly NOTHING. NULL has some value, but I've yet to find a use for it. A column should have a value, or the same for a program variable, or it should be "\\0" or 0, which IS a value. NULL in INFORMIX-land is not the same thing. ( Or is it in the data base? ) Coding with NULL is usually fraught with ambiguity, even though I still INITIALIZE record.* TO NULL. This is a lazy way of setting values to a value that is "unusable", but remains a value non the less. Many comparisons of values are better checked by at least using some other test along with checking for NULL. IF LENGTH( string ) = 0 THEN -->>to me<<-- is more reliable than IF string IS NULL THEN Different hardware platforms can give different results for the same comparison of NULL, but the LENGTH argument should be more reliable. I learn new things every day and eagerly await a final ruling...maybe we can think of it as a garbage value, but not exactly "nothing". Hope I didn't digress too much ... I remain a student... :-) -- ( Thank the HP/UX version of Netscape for the messy line breaks ) \\\\|// (6 6) -----------oOOo---( )---o00o------------------------------- Tim Schaefer Motorola: tschaefe@paging.mot.com Worldwide: tschaefe@shadow.net Homepage: http://www.shadow.net/~tschaefe Liberty: www.lp.org www.cato.org -----------------------------------------------------------