Re: peeve - NULL testing results

Netters, We, as a team, have agreed that writing unitialized fields to our database is a poor programming practice. We bow to the great God of ANSI and have performed many and various acts of contrition - we all cut a finger off of our left hands. Over the next x YEARS we will correct these faults wherever we find them. I've also looked through the manuals - and yes it is documented that NULLS behave in this fashion. Meanwhile - I've done a little exploring and testing in 4GL, SQL and ace trying out the various ideas that were presented. IF THEN ELSE: ace: you can't do 'if a=b then else'. You can do variable x char(1) { junk variable } if a = b then let x = "X" else (whatever) Results: a = null b = "xxxx" - else clause triggered a = "xxxxx" b = null - else clause triggered a = null b = null - else clause triggered (NULL != NULL) a = "xxxx" b = "xxxx" - else clause NOT triggered 4gl: you CAN do IF a=b THEN ELSE - get the same results This still does not solve my problem, since in our case NULL should = NULL. ----------------------------------------------------------------------------- WITHOUT NULL INPUT: xxxx.per database xxx WITHOUT NULL INPUT Results: I successfully added a row with NULLs using such a form. (did I do that one right?) I also made all of the fields required - at which point it took me to each field and insisted that I fill it in. It would accept spaces. A potential, but nasty, solution to correcting our data. ("You mean I have to hit spaces whenever I don't want anything in there?") ----------------------------------------------------------------------------- LENGTH(field)=0: SQL: SELECT * from table where length(field) = 0 Results: field IS NULL not returned (undesirable) field = " " returned field = "x" not returned 4GL: IF LENGTH(a) = 0 THEN ... Results: a IS NULL THEN triggered a = " " THEN triggered a = "x" THEN NOT triggered We can work with this, in SQL we'll just have to add an (OR field IS NULL). ----------------------------------------------------------------------------- So I have learned today that NULL means unknown. I am hard pressed to think of an occasion where this capability might have value to me, perhaps in an very strict integrity situation. (If a = NULL (unknown) then CALL WARN_SUPERVISOR_THAT_JOHN_ISN'T_FILLING_IN_ALL_OF_THE_FIELDS!()) Thank you all for your patience and comments. 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. _____________________________________________________________________________