Ask Reuben

NULL (Test for Success)

How to code expressions to cater for NULL?

I was looking at a support case recently where the value returned from a function was NULL when it was expected to contain a value.  The puzzling thing was the change that led to the function returning NULL had occurred a few years ago and we were wondering why it had not been detected earlier.  The reason it had not been detected was that a test to check the value was bad, and did not consider the possibility that the value could be NULL.

If you look carefully at the documentation for IF it has a clause …

A NULL expression is considered as FALSE.

Similarly in the documentation for Boolean Expressions it says …

The result of a boolean expression is a TRUE or FALSE boolean value, but it can also be NULL if one of the operands is NULL.

What this means is that if you have code of the form

IF condition THEN
    # Path A
ELSE
    # Path B
END IF

if either side of the expression in the condition is NULL, the value of the condition will be FALSE, and Path B will be chosen.

The trap you can fall into is that code of the form …

IF simple-test-if-value-is-bad THEN
    # Error code ..
END IF

… if the value is NULL, and you don’t explicitly test for NULL then the error code you are expecting to be executed is not.

Run the following code …

MAIN
    
    DEFINE expr STRING

    DISPLAY "--------------------"
    DISPLAY "Bad Test for Failure "
    DISPLAY "--------------------"
    IF expr NOT MATCHES "A*" THEN
        DISPLAY "Value must begin with A"
    END IF

    DISPLAY "------------------------"
    DISPLAY "Correct Test for Failure "
    DISPLAY "------------------------"
    IF expr IS NULL OR  expr NOT MATCHES "A*" THEN
        DISPLAY "Value must begin with A"
    END IF

    DISPLAY "--------------------"
    DISPLAY "nvl Test for Failure "
    DISPLAY "--------------------"
    IF nvl(expr," ") NOT MATCHES "A*" THEN
        DISPLAY "Value must begin with A"
    END IF

    DISPLAY "----------------"
    DISPLAY "Test for Success"
    DISPLAY "----------------"
    IF expr MATCHES "A*" THEN
        # value is OK
    ELSE
        DISPLAY "Value must begin with A"
    END IF    
   
END MAIN

…you should get the following …

--------------------
Bad Test for Failure 
--------------------
------------------------
Correct Test for Failure 
------------------------
Value must begin with A
--------------------
nvl Test for Failure 
--------------------
Value must begin with A
----------------
Test for Success
----------------
Value must begin with A

You should observe that the first test

IF expr NOT MATCHES "A*" THEN

simply tested for failure and did not cater for the fact that expr might be NULL.  As expr was NULL then the value of the condition was FALSE and the DISPLAY did not occur.

The next two tests explicitly consider that the value of expr could be NULL.  Both …

 IF expr IS NULL OR expr NOT MATCHES "A*"

… and …

IF nvl(expr," ") NOT MATCHES "A*" THEN

… both explicitly consider that the value of expr may be NULL and make sure that this possibility is catered and tested for, and hence the expected DISPLAY does occur.

The IS NULL operator is used to test if an expression is NULL and returns TRUE if it is.

The nvl operator if you have not seen it before, substitutes the second value for the first expression if the first expression is NULL.

The final approach is the one I personally like to take and that is to have the mindset of testing for success.

IF expr MATCHES "A*" THEN
    # value is OK, carry on
ELSE
    # error condition, do something
END IF

If something unexpected happens and expr is NULL then the result of the boolean-expression is considered as FALSE and the ELSE path is executed.  I like this approach as I don’t have to explicitly code the IS NULL case for every variable in the expression, and I don’t have to come up with an arbitrary value to use in the nvl operator.

If I relate the Test for Success strategy as I like to call it it to the case I mentioned at the beginning, the code was of the form

IF get_version() < 1974 THEN
   # stop because version is not new enough
   ...
END IF

get_version() was returning NULL and the program was not stopping.

By adopting the Test for Success mindset with this example then this is coded as ...

IF get_version() >= 1974 THEN
    # carry on, version is good
ELSE
   # stop because version is not new enough
END IF

If get_version() unexpectedly returns NULL then the ELSE clause is evaluated, just as if the version number was less than 1974.