Schultz’s PowerBuilder Notes

Nulls


I find nulls to be the cause of so many bugs. Here are a few things that have helped me get my mind around this surprisingly confusing topic.

Let’s look at the following script, what would the result be?

integer li_null

SetNull(li_null)
if li_null = li_null then
   MessageBox("li_null = li_null", "True")
else
   MessageBox("li_null = li_null", "Not True")
end if 

There has to be a trick because a variable should always be equal to itself and the expression should evaluate as true. But if you run the code, the MessageBox “Not True” comes up.

Let’s look at another script and predict the result:

integer li_null

SetNull(li_null)
if li_null <> li_null then
   MessageBox("li_null <> li_null", "True")
else
   MessageBox("li_null <> li_null", "Not True")
end if 

You might expect that changing the “=” to “<>” would be the opposite as the previous test, seems like the two tests should be mutually exclusive and collectively exhaustive.  But if you predicted that this also displays the second Message Box (“Not True”), go to the head of the class.

The reason we get these results is that “null” is not a specific value. Rather, null means “Unknown value”. In the first example, if you substitute “unknown value” for li_null you get:

if unknown value = unknown value then …

The result of the expression is neither true nor false, it is unknown. An unknown value may be equal to another unknown value, but it also might not. Since null = unknown, the result of this expression is null.

Similar issues occur in SQL, specially when a table uses null to store a particular value in a column.  But there are differences.  See how Sybase SQL treats nulls.

Advertisements

August 14, 2008 - Posted by | Powerscript | , , ,

2 Comments »

  1. isNull() function is still the best way to check null value.

    Comment by JB | August 27, 2008 | Reply

  2. how do you check for nulls in powerscript, modify statements?

    Comment by Garnet | November 5, 2014 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: