PRODUCT   :  R:BASE                  VERSION      :  3.1
   From Bill Downall, Downall Consulting Services, 5411 White Willow
   Court, Indianapolis, IN 46254-9633. Bill is a database consultant and
   educator. You can reach him at 317-297-3810 or through David M.
   Blocker and Associates at 617-784-1919.
   Full SQL support in R:BASE 3.x means application developers must be
   careful when comparing values that might be null. The SQL standard
   dictates that nulls can't be compared to anything, not even other
   nulls. This is a change from the way we could compare values in R:BASE
   for DOS and other earlier R:BASE versions.
   This article uses eight code segments to show what happens when you
   compare a null value to another value. It gives you guidelines to
   follow when writing programs that deal with nulls. Also, you'll see
   the differences between R:BASE 3.x and R:BASE for DOS when it comes to
   null comparisons.
   Eight Program Examples
   The following eight program methods each try to meet the same simple
   need. if a name has a middle initial, format it like this: John Q.
   Doe. Otherwise, format it like this: John Doe.
     IF vmi = ' ' THEN                             *( 1 )
       SET VAR vname = (.vfn & .vln)
       SET VAR vname = (.vfn & .vmi + '.' & .vln)
     IF vmi <> ' ' THEN                            *( 2 )
       SET VAR vname = (.vfn & .vmi + '.' & .vln)
       SET VAR vname = (.vfn & .vln)
     IF vmi = '-0-' THEN                           *( 3 )
       SET VAR vname = (.vfn & .vln)
       SET VAR vname = (.vfn & .vmi + '.' & .vln)
     IF vmi <> '-0-' THEN                        *( 4 )
       SET VAR vname = (.vfn & .vmi + '.' & .vln)
       SET VAR vname = (.vfn & .vln)
     IF vmi IS NULL THEN                  *( 5 )
       SET VAR vname = (.vfn & .vln)
       SET VAR vname = (.vfn & .vmi + '.' & .vln)
     IF vmi IS NOT NULL THEN            *( 6 )
       SET VAR vname = (.vfn & .vmi + '.' & .vln)
       SET VAR vname = (.vfn & .vln)
     IF vmi = ' ' OR vmi IS NULL THEN      *( 7 )
       SET VAR vname = (.vfn & .vln)
       SET VAR vname = (.vfn & .vmi + '.' & .vln)
     IF vmi <> ' ' AND +
       vmi IS NOT NULL THEN            *( 8 )
       SET VAR vname = (.vfn & .vmi + '.' & .vln)
       SET VAR vname = (.vfn & .vln)
   Each pair contains logical opposites. For example, the condition
   tested by method two is the opposite of the condition tested by method
   one. But the commands in the IF or ELSE portions of the IF blocks are
   also reversed, so the two programs should get exactly the same result.
   Assuming NULL is set to -0-, the conditions tested in one through four
   all work in R:BASE for DOS. But none work correctly all of the time in
   R:BASE 3.x. Methods five through eight use IF conditions that are
   available only in R:BASE 3.x.
   What Can Go Wrong?
   To demonstrate what can go wrong if you aren't careful with the NULL
   setting and your programming, I wrote a routine called MIDINIT.CMD
   (listed on the next page). It imitates all eight methods and allows
   you to control two other important details:
     o  The NULL symbol setting.
     o  The data for the middle initial - a character, a space, or a null
   Use MIDINIT.CMD to test various combinations. You'll see that in
   R:BASE 3.x, there's only one sure-fire way to test for null values.
   Set the NULL symbol to -0- and use the IS NULL or IS NOT NULL
   Before running MIDINIT.CMD, create the following three menu files, and
   place them in your current directory:
   Listing of SPACNULL.MNU
     POPUP |Pick Space or Null.|
     |Space||Make middle initial a space.|
     |Null||Make middle initial null.|
   Listing of NULLCHAR.MNU
     POPUP | Pick the NULL symbol setting.|
     |Normal: -0-||Set the NULL symbol to -0-.|
     |Space: ' '||Set the NULL symbol to a space.|
   Listing of WHAT.MNU
     |Try another one|
   All the quotation marks in MIDINIT.CMD are single quotation marks.
   How to Run MIDINIT.CMD
   To see the demonstration, enter the following at the R> prompt:
     RUN midinit.cmd
   Below are five example tests you can try with MIDINIT.CMD.
   NULL -0- & Any Middle Initial
   Enter "Elwood" for a first name and "Dowd" for a last name. When the
   program asks for a middle initial, enter any letter of the alphabet. I
   entered "P." When prompted to enter a NULL symbol, choose normal, -0-.
   Now, you'll see the results of each of the eight programs. Notice the
   strange results for three and four.
     3. vmi = '-0-'                                  F    Elwood P. Dowd
     4. vmi <> '-0-'                                 F    Elwood Dowd
   The middle initial variable (VMI) contains the letter "P," so it isn't
   null. But you apparently can't use the NULL symbol setting to test for
   this because R:BASE 3.x determined that although P isn't "equal" to '-
   0-', it also isn't "not equal" to '-0-'. Both conditions, which look
   like opposites, are false.
   NULL -0- & Space Middle Initial
   Choose "Try another one." This time enter "Margaret" for the first
   name, "Mead" for the last name, and press [Enter] when the program
   asks for a middle initial. A menu will appear asking you for a middle
   initial; choose SPACE. Then choose the normal null symbol.
     3. vmi = '-0-'                                  F    Margaret. Mead
     4. vmi <> '-0-'                                 F    Margaret Mead
     5. vmi IS NULL                                  F    Margaret. Mead
     6. vmi IS NOT NULL                              F    Margaret. Mead
   This time, one and two are correct but not three. Look at the code for
   methods three and four. Again both evaluate as false even though they
   are opposites - VMI isn't "equal" to '-0-' and it isn't "not equal" to
   '-0-'. As in the first example, when R:BASE compares a space to a
   quoted text string that looks like the current null character, the
   comparison always evaluates as false unless you use the IS NULL
   The results from five and six demonstrate another problem. Here the
   names format incorrectly because the programmer didn't consider that a
   data entry operator might press the space bar rather than tab through
   a field. Methods five and six test for a null value only, adding a
   period in all other cases.
   NULL -0- & Null Middle Initial
   This time make the middle initial null. Enter "Glenn" for the first
   name and "Ford" for the last. Press [Enter] when asked for a middle
   initial, choose NULL from the pop-up menu, and then choose the normal
   null symbol (-0-).
     1. vmi = ' '                                     F    Glenn. Ford
     2. vmi <> ' '                                    F    Glenn Ford
     3. vmi = '-0-'                                  F    Glenn. Ford
     4. vmi <> '-0-'                                 F    Glenn Ford
   The top four are unpredictable. The null value in VMI fails every
   comparison to anything, whether you use the equal sign or the not
   equal (<>) operator. VMI isn't "equal" to a space or -0-, nor is it
   "not equal" to a space or -0-.
   NULL ' '  & Any Middle Initial
   If you set the NULL symbol to a space, matters get worse. Enter any
   name and include a middle initial. Choose SPACE for the NULL symbol.
   With NULL set to a space, you can't compare a letter to a single
   space, so methods two and eight give wrong answers.
   NULL ' '  & Space Middle Initial
   Next, enter any name and a space for a middle initial. Choose SPACE
   for the NULL symbol. You'll discover that just about every method
   formats the name incorrectly. Method one produces a faulty answer
   because R:BASE can no longer evaluate the expression IF VMI = ' ',
   even though VMI is equal to a single space. Even method seven, which
   carefully tests both for a space and a null, gets the wrong answer.
   Conclusion & Summary
   It's clear that you should set the NULL symbol to -0- before all
   comparisons that may involve a null. The chart at the bottom of the
   page summarizes the results of all the possible combinations of
   conditions, NULL settings, and variable values.
   There are no surprises in the lower left quadrant of the chart. The
   opposite conditions always evaluate with opposite results. But with
   the NULL symbol set to a space, many things can go wrong.
   Even with NULL set to -0-, you can no longer compare nulls to quoted
   text that looks like the current null symbol setting because the SQL
   standard won't allow it.
   Guidelines for Null Comparisons
   Here are the guidelines to follow in R:BASE 3.x when comparing TEXT
   columns or variables that may sometimes be null. Be sure to follow the
   first three guidelines together. If you don't follow the first two,
   the third won't give predictable results.
     o  Set the NULL symbol to '-0-'.
     o  Test for empty values with the IS NULL or IS NOT NULL operator.
     o  Test for both null and blank values if it's possible that a value
        could sometimes be null and other times be blank. For example,
        this IF command tests for both conditions:
        IF vname = ' ' OR vname IS NULL THEN
     o  Avoid unexpected null value comparisons by using INDICATOR
        variables to test for null values in DECLARE CURSOR,
        SELECT...INTO, and FETCH commands.
   ZERO Setting & Logical Functions
   The NULL symbol and ZERO settings have no effect on IF condition null
   comparisons with numeric data types. All IF conditions will be false
   if the numeric variable is null unless you use IS NULL or IS NOT NULL.
   However, if ZERO is ON, you can compare a null with zero and come up
   with a match inside a logical function (IFEQ, IFLT, or IFGT). This is
   because the logical functions aren't subject to the SQL rules. For
   example, this code works:
     SET VAR vtorf TEXT=(IFEQ(.vnull, 0, 'T', 'F'))
   The variable VTORF will be set to T. But in every other place in
   R:BASE, you must use the IS NULL or IS NOT NULL operator to test for a
   null value, as required by the SQL standard.