Re: Peeve

Richard Ridley writes: >Jack Parker writes: > >>I hate fixing a statement from: >>IF a != b THEN ..... >>TO >>IF a != b >>OR (a IS NULL AND b IS NOT NULL) >>OR (a IS NOT NULL AND b IS NULL ) THEN ..... >>Are there others out there with this prob? Or is it just us? > >Yup - this always p***ed me off too - somebody told me once that if >a value is null, then this is a special case as the value as far >as *logic* goes is indeterminate and you are therefore not allowed >to assume that it is not necessarily unequal to anything :) Whew! What's "whew" about it? NULL is a designated value with a very precise meaning of "NOT KNOWN" (or not available or not applicable). Is x greater than ?, or is ? greater than x? As Alan Popiel pointed out on this thread, relational expressions are evaluated very sensibly (and Boolean combinations also, I'd add); the problem is that the IF statement has only two branches. The "not known" answer had to be put down one of those branches, and whichever one it was, would cause problems. As Cln. Panic pointed out on this thread, the simplest workaround is to write your IF so that the cases of interest all end up in the ELSE leg. Other workarounds: * design the schema so that NULLs don't appear in columns on which you have to make choices. After all, isn't making choices based on a variable whose value might be "NOT KNOWN" quite a bit like making choices based on an unitialized variable? * check (using IF x IS NULL) and supply a non-NULL default value early, so the rest of your code can assume "x IS NOT NULL." * write a function like this: FUNCTION unlike(a,b) DEFINE a,b WHATEVER_TYPE IF (A IS NULL) OR (B IS NULL) THEN RETURN TRUE ELSE RETURN A <> B END IF END FUNCTION Then ponder that fact that you can NOT write a function isLike(a,b) whose body consists of "RETURN NOT unlike(a,b)" Dave Cortesi cortesi@informix.com