Firebird Null Guide

doesn't include people of unknown age, which is also defendable. But:

if (Age >= 18) then CanVote = 'Yes'; else CanVote = 'No';

seems less right: if you don't know a person's age, you shouldn't explicitly deny her the right to vote. Worse, this:

if (Age < 18) then CanVote = 'No'; else CanVote = 'Yes';

won't have the same effect as the previous. If some of the NULL ages are in reality under 18, you're now letting minors vote!

The right approach here is to test for NULL explicitly:

if (Age is null) then CanVote = 'Unsure'; else

if (Age >= 18) then CanVote = 'Yes'; else CanVote = 'No';

Note

else always refers back to the last if in the same block. But it's often good to avoid confusion by putting begin...end keywords around a group of lines. I didn't do that here though - I wanted to keep the number of lines down. And then I made up for it by adding this note ;-)

Finding out if fields are the same

Sometimes you want to find out if two fields or variables are the same and you want to consider them equal if they are both NULL. The correct test for this is:

if (A = B or A is null and B is null) then...

or, if you want to make the precedence of the operations explicit:

if ((A = B) or (A is null and B is null)) then...

A word of warning though: if exactly one of A and B is NULL, the test expression becomes NULL, not false! This is OK in an if statement, and we can even add an else clause which will be executed if A and B are not equal (including when one is NULL and the other isn't):

But you shouldn't get the bright idea of inverting the expression and using it as an inequality test (like I once did):

/* Don't do this! */

if (not(A = B or A is null and B is null))

then ...stuff to be done if A differs from B...

The above code will work correctly if A and B are both NULL or both non-NULL. But it will fail to execute the then clause if exactly one of them is NULL.

10