Re: Q: Suppressing NULLs using Forms 4.5

[Quoted] In article <33949800.49219898_at_www.sigov.si>,   jurij.modic_at_mf.sigov.mail.si (Jurij Modic) wrote:
>
> On 03 Jun 1997 15:40:25 GMT, andrewto_at_kingfish.cse.tek.com (Andrew
> Tompkins) wrote:
>
> >In article <5n15uh$skl$2_at_orion-fddi.rz.uni-ulm.de>
> > wzimmer_at_atair.rz.uni-ulm.de (Walter Zimmer) writes:
> >
> >>
> >> Sorry for not having an answer to your problem, but I always wanted to
> >> know where someone would use a space as a valid value.
> >>
> >> Can you enlighten me ?
> >
> >We used to use a space as an indicator of an open column in a row in place
> >NULL because of the nastiness that NULL does when retrieved from the
> >database and used in conditions for 'if' or 'while' statements. We now
> >use 'chr(0)' for this purpose. We now don't have to check for NULL after
> >every retrieval.
>
> I can't see how CHR(0) or space can be of more help then NULL in IF or
> WHILE statements. In fact, it can give you wrong ansver while NULL
> can't. What exactly are this nastiness?

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?

[Quoted] [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.

Steve Cosner
(Emailed and posted)

[Quoted] -------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet