Re: Q: Suppressing NULLs using Forms 4.5
Date: 1997/06/05
Message-ID: <33971f60.49304564_at_www.sigov.si>
On Thu, 05 Jun 1997 11:24:06 -0600, stevec_at_zimmer.csufresno.edu (Steve Cosner) wrote:
>In article <33949800.49219898_at_www.sigov.si>,
> jurij.modic_at_mf.sigov.mail.si (Jurij Modic) wrote:
>> ...[SNIP]...
>
>First, I would say that using CHR(0) sounds like real trouble, worse than
>using space. I tried it in a Personal Oracle 7 database table with
>Forms, and like a space, it was stored in the database, but not retrieved
>by the form.
>
>To answer the "nastiness" question: It is the problem of having to
>remember which columns returned from your table can be null. In those
>columns, whenever you write an IF statement, you have to make sure to use
>the NVL function, and also invent a value to replace the null that cannot
>possibly be a true value from the database.
>
>Instead of writing IF MYFIELD <> 'A' THEN....
>it must be IF NVL(MYFIELD,'*NULL*') <> 'A' THEN....
>
>For numeric columns, you have to remember to use a numeric replacement
>value in the NVL function. However, if you are using the '=' operator,
>then you don't have to worry about null values. All the evaluation of
>each situation, and testing too, really slows down the development
>process.
>
>Further nastiness appears when you are trying to use character functions
>like instr, length, rpad and lpad. These return null instead of a more
>conventional solution when the input string is null. So you wind up
>embedding NVL functions within these functions. Makes reading the code
>soooo easy! Sure glad they didn't make the concat function (||) return
>null if one of the strings is null.
>
>Granted, doing this just once in a while is not a problem, but when you
>are trying to code complex business logic into a procedure, forgetting to
>code the NVL can be one of the worst types of bugs-- Lots of testing will
>not flush out the problem. When the unexpected null value does come
>along in actual production, and then the process throws a monkey wrench.
>
>It would have been so much easier on everyone if the A<>B condition would
>be true when one or the other is null. Trying to shove 3-value logic
>(True, False, and Null) into a language set up for 2-value logic (True,
>False) is a terrible mistake.
>
>While using the term "null value" may be an oxymoron, to a computer, null
>is just another value. Forcing 3-value logic probably required lots of
>extra coding and testing for the people developing the PL/SQL language,
>too.
>
>Can anyone point out a situation where it is beneficial to treat Null
>differently from any other concrete value in conditional statements?
>Anyone?
IF 1 > NULL .... what would be your answer? TRUE? FALSE? IF 0 > NULL .... TRUE, FALSE ? IF -1 > NULL .... TRUE, FALSE ?
What concrete value would you replace NULL with in such situation to get answer in 2-value logic?
I know you anderstand the NULL logic perfectly well, just wan't to remind you that operators in equations are not allways = or <>. I completely agree with you that coding and debuging with possibility of NULL values is much more compicated as when you can exclude NULLs. I partly agree with you that some of functions and operators applied upon NULLs return unlogical results - at least at first sight. But if you look at NULL's logic more generaly, I doubt you could find any different definitions to cover all possible scenarios better as they do now. NULL simply means DON'T KNOW and with most functions and operators they return the same result: DON'T KNOW. (OK, here you could argue about implementation of concatenation operator being in contradiction. BTW, I am glad they implemented it "wrong" as well as you are ;-)).
But I can't agree with assumption (not yours, it's from one of the previous posters) that it's better to insert some special value instead of NULL into a database simply to avoid problems in conditional statements. If you have NUMBER(1) field wich can have any of possible values, what value will you insert if you don't know it's actual value? You will think: 'Oh, what the heck, I'll insert NULL and be carefull later when I'll apply some logic stuff on that column'. See? Would you do this only as an exception or would you admit with this that the NULL concept is here because it is the only (proper) way to do it?
>
>[Warning... Soap Box Time] I wish the people who invented 3-value logic
>would do all of the following: 1. Get their heads out of the sand, 2.
>Get off of their stack of text books, 3. Climb down from their ivory
>towers, and 4. Experience the real world.
>
Have you ever designed any database with no NULL columns in it? Don't think so. And I know that you haven't simply forgotten to add NOT NULL constraints, you have done it on purpose, to allow NULL values.Because with only 2-value logic you simply couldn't do it. 3-value logic is OK although we all sometimes do have troubles with it. But on such situations I wouldn't blame it on those guys with books in the ivory towers :-)
>Steve Cosner
Regards,
[Quoted] [Quoted] Jurij Modic Republic of Slovenia tel: +386 61 178 55 14 Ministry of Finance fax: +386 61 21 45 84 Zupanciceva 3e-mail: jurij.modic_at_mf.sigov.mail.si Ljubljana 1000
Received on Thu Jun 05 1997 - 00:00:00 CEST