""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
COMPARING TEXT VALUES THAT MIGHT BE NULL
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
PRODUCT : R:BASE VERSION : 3.1
CATEGORY : NULL VALUES SUBCATEGORY : PROGRAMMING
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
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)
ELSE
SET VAR vname = (.vfn & .vmi + '.' & .vln)
ENDIF
IF vmi <> ' ' THEN *( 2 )
SET VAR vname = (.vfn & .vmi + '.' & .vln)
ELSE
SET VAR vname = (.vfn & .vln)
ENDIF
IF vmi = '-0-' THEN *( 3 )
SET VAR vname = (.vfn & .vln)
ELSE
SET VAR vname = (.vfn & .vmi + '.' & .vln)
ENDIF
IF vmi <> '-0-' THEN *( 4 )
SET VAR vname = (.vfn & .vmi + '.' & .vln)
ELSE
SET VAR vname = (.vfn & .vln)
ENDIF
IF vmi IS NULL THEN *( 5 )
SET VAR vname = (.vfn & .vln)
ELSE
SET VAR vname = (.vfn & .vmi + '.' & .vln)
ENDIF
IF vmi IS NOT NULL THEN *( 6 )
SET VAR vname = (.vfn & .vmi + '.' & .vln)
ELSE
SET VAR vname = (.vfn & .vln)
ENDIF
IF vmi = ' ' OR vmi IS NULL THEN *( 7 )
SET VAR vname = (.vfn & .vln)
ELSE
SET VAR vname = (.vfn & .vmi + '.' & .vln)
ENDIF
IF vmi <> ' ' AND +
vmi IS NOT NULL THEN *( 8 )
SET VAR vname = (.vfn & .vmi + '.' & .vln)
ELSE
SET VAR vname = (.vfn & .vln)
ENDIF
Analysis
""""""""
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
value.
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
operator.
Before running MIDINIT.CMD, create the following three menu files, and
place them in your current directory:
Listing of SPACNULL.MNU
"""""""""""""""""""""""
spacnull
POPUP |Pick Space or Null.|
|Space||Make middle initial a space.|
|Null||Make middle initial null.|
ENDC
Listing of NULLCHAR.MNU
"""""""""""""""""""""""
nullchar
POPUP | Pick the NULL symbol setting.|
|Normal: -0-||Set the NULL symbol to -0-.|
|Space: ' '||Set the NULL symbol to a space.|
ENDC
Listing of WHAT.MNU
"""""""""""""""""""
what
POPUP
|Try another one|
|Quit|
ENDC
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
condition.
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 ZERO ON
SET VAR vnull INTEGER = NULL
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.