peeve

Reply-To: Jack Parker <jparker@hpbs2561.boi.hp.com> X-Informix-List-To: mrichardso@shl.com X-Informix-List-Id: <list.2255> >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? This is a pretty standard problem in practise, which of course makes perfect sense in theory (see below)! >Why is it that NULL is NOT EQUAL to "xxx", but it is not NOT EQUAL to "xxx" >either? NULL is intended to mean "value unknown". Hence the purist interpretation "I can't tell you if it is equal or not, since we just don't know". The fundamental problem is that the use of NULLs is not handled by "two-valued" logic (logic typically used in 3GLs and in SQL). Accordingly people like Codd and Date feel that SQL is an inadequate language for relational algebra since it IS two-valued. Of course, that doesn't solve our practical problem of dealing with this in the languages we use. >I'm not looking for the real answer, I'm looking for the explanation >of why grunts like me have to deal with this when it's 'intuitively obvious >to the most casual observer' that NULL should not not NOT EQUAL "xxx". 1 2 3 >Even better, when is it going to be fixed? Unfortunately it's never (never say never!) going to be fixed, since NULLs work like father Codd intended. Some languages and SQL extensions (ORACLE) support an IFNULL or equivalent function which returns a value if the variable is NULL. Then you could translate: IF a != b THEN into IF IFNULL(a,RidiculousValueX) != IFNULL(b,RidiculousValueX) THEN and ensure that if you have NULL for a or b or both the statement will work like you want. This requires you to be able to specify a "ridiculous value", but that discussion leads us to the reason for NULLs in the first place... If you can write these kind of functions in the languages you use, maybe your peeve can be more bearable, even if it won't go away! regards, marv >HP/UX 8.02 >Online 5.0 >ISQL/4GL 4.10.UD2 >j. _____________________________________________________________________________ Jack Parker - Contractor | Hewlett Packard, BSMC Boise, Idaho, USA| If you keep staring at it like that, jparker@hpbs2561.boi.hp.com | your nose is going to grow (208) 396-5388 (W) (208) 384-1623 (H) | into the bark. _____________________________________________________________________________