Re: Re Where != and NULLS

In article <322C83B7.5DB7@paging.mot.com>, Tim Schaefer <tschaefe@paging.mot.com> writes >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 >----------------------------------------------------------- I usually write a function along the lines of FN_is_empty(my_char) DEFINE my_char CHAR(100) If (my_char is NULL) OR ((LENGTH(my_char CLIPPED)) = 0) THEN RETURN TRUE else RETURN FALSE End if As use IF FN_is_empty(<whatever>) THEN.... -- David Williams