Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


Breck Carter
Last modified: May 21, 1996
mail to: bcarter@bcarter.com



The Tip With No Name

Here's a Pop Quiz: Why doesn't this loop execute at all, not even once?

   li_idx = 1
   ii_sheet_ct = 3
   SetNull ( lw_sheet )
   do while li_idx <= li_sheet_ct and not IsValid ( lw_sheet )
      if li_idx <= li_sheet_ct then
         if IsValid ( iw_sheet [ li_idx ] ) then
            lw_sheet = iw_sheet [ li_idx ]
         end if
      end if
      li_idx++
   loop
If you're wondering about "The Tip With No Name" it's because the real title would give it all away: "Avoid NULLS".

And the answer? PowerScript supports 3-valued logic: true, false and NULL.

The IsValid() function doesn't always return true or false but yields NULL if the argument is NULL. This is also the case with logical operators like and, or and not: if any of the operands are NULL then so is the result.

Here's how the do while expression is evaluated:

   li_idx <= li_sheet_ct and not IsValid ( lw_sheet )
   ------    -----------                   --------
     1    <=      3      and not IsValid (   NULL   )
     --------------              --------------------
           true          and not        NULL
                             ---------------
           true          and      NULL
           ---------------------------
                      NULL
Now this 3-valued logic scheme isn't fully supported in PowerScript. Statements like do while and if use only 2-valued logic, true and false, and interpret NULL as false. And that's why the loop is not executed: do while NULL is the same as do while false.

Fans of 3-valued logic say that NULL is good, that NULL represents the "unknown" or "no value" case. They say it's better than using some special value to represent "unknown" and that we should all become good friends with the IsNull() function (which, curiously, is just about the only 2-value logic function PowerBuilder offers).

In the real world, however, NULLs cause endless grief for both programmers and users. Consider the case of an employee table where five employees are in department 100, thirty are in other departments and one employee, the president, has NULL in the department column.

These two SQL statments yield the numbers 5 and 30, giving the impression the company has only 35 employees:

   select count(*) from employee where department = 110
   select count(*) from employee where department <> 110

Banish NULLs!

This is where most NULLs come from, from database columns rather than misguided calls to SetNull() as in the Pop Quiz. If it makes no sense to you that both = and <> can have the same answer when one of the operands is NULL, you're in good company. You're in the same boat with your end users who are struggling with bogus answers from their adhoc queries.

The solution is simple: Banish NULLs from your applications. Declare all database columns as NOT NULL. Use special values like zero and the empty string "" to represent empty fields. For those columns where your really need to specify "unknown" (very rare in the real world) use a separate column like "Department Known: Y/N" to make it absolutely clear what's going on.

For every rule, of course, there are exceptions. The most severe case is the optional foreign key value: If the column is filled in, it must be a valid primary key value in the other table, but it doesn't need to be filled in. The alternatives are to declare it as a NULLable foreign key or to define a special "(None)" row in the other table. The latter solution is very attractive when defining DropDownDataWindows that allow users to change the column from some known value to "none of the above".

Another difficulty is presented by date and datetime columns: There is no good "zero" or "empty" value for a date. One solution is to use values far in the past or future, like 1901-01-01 for date_hired and 4712-12-31 for termination_date, and tell everyone what they mean: they're simply default values. If you think that's too dangerous then don't make it worse by declaring them NULLable, add columns like "Date Hired Known: Y/N" instead.


Breck Carter can be reached by phone at (416) 763-5200 or via email at bcarter@bcarter.com.