Re: NULL

Jack Parker <jparker@hpbs2561.boi.hp.com> writes: -> ->Netters, -> ... omitted ... -> ->Thank you all for your input - with a post like that I really deserved ->a flame - but this is such a kinder gentler net..... I won't credit ->responses, because I'm using elm and only do one note at a time. -> ->What I got out of all your replies was: -> ->a) NULL really means unknown. -> ... omitted ... -> ->b) use NOT NULL in the schema. -> ->c) use 'without null input' within the form (or words to that effect). -> ->d) use IF a=b THEN ELSE -> -> I like that one. -> ->e) What I have done in the past is testing for LENGTH(a)=0 which will -> be true if a = " " or a IS NULL. This works fine when there is -> only one variable. (Note - that won't work in SQL - because -> length(NULL) is not != 0. -> ... omitted ... -> ->Is this really a three level compare? If I have two apples and ->Bill has diddly - is my number of apples not NOT EQUAL to Bills'? ---------------------------- Does Bill have *zero* apples, or *diddly* apples? Indeed, (2 ?= 0) = true. But (2 ?= diddly) = diddly, where "diddly" is "NULL" spelled sideways. ---------------------------- -> ->If this is really a three level compare then we need an instruction ->to deal with it, I liked: -> ->IF a = b THEN ->ELSE ->NULL ->END IF -> ->But then which one was NULL? ----------------------------- Well, IF a = b THEN { assuming no NULLs } { Ah, they are equal. } ELSE { Okay, but is a > b or is b > a? How is this clearer than which one is NULL? } END IF By the way, does anybody remember the Fortran 3-way IF statement? IF (A-B) 100, 200, 300 It still doesn't handle NULLs, but at least it tells us whether a > b, etc., in one test. ----------------------------- -> ->Perhaps someone with a better theoretical knowledge than mine could ->explain exactly why a NULL is not NOT EQUAL to "x"? ----------------------------- Because with a NULL, we don't know that it *is* and we also don't know that it *isn't*; all we know is that WE DON'T KNOW. "a := NOT (NOT A)" is still dependent on two-value logic, not three value logic. ----------------------------- Along these lines, Fred Berns <kestnb!fhb@uunet.UU.NET> writes: -> IF expression THEN -> statement(s) -> ELSE -> statement(s) -> NULL { or maybe ON NULL } -> statement(s) -> END IF -> ->Sounds like a great idea ... now how about math with NULLS, can't we ->have some way of treating them like zeros? ----------------------------- This is probably not a good idea. If the value is zero, use zero. If you *don't know*, use NULL. Example: Today's high temperature was 70 F (~21c). Was that higher than yesterday's high temperature? If you don't know, say so; don't use zero as yesterday's high and therefore say "yes". Even better example: today's high was 23 F (=-5c). If you don't know yesterday's high, assuming zero gives different answers in USA (Fahrenheit) or Europe (Celcius). Using NULL gives the correct answer ("I don't know.") in both locations. NULLs are a difficult conceptual leap. I hope the example about comparing high temperatures helps. Regards, Alan +------------------------------+---------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | Martin Marietta, LSC | ( Please note: My opinions do not ) | | P.O. Box 179, M/S 5422 | ( represent official Martin policy. ) | | Denver, Colorado 80201-0179 | Voice: 303-977-9998 | +------------------------------+---------------------------------------+