Unknown Data: The Null Value

The null value has a special run-time data type in InfoSphere™ DataStage® BASIC. It was added to InfoSphere DataStage BASIC for compatibility with InfoSphere DataStage SQL. The null value represents data whose value is unknown.

Note: Do not confuse the null value with the empty string. The empty string is a character string of zero length which is known to have no value. Unlike null, whose value is defined as unknown, the value of the empty string is known. You cannot use the empty string to represent the null value, nor can you use the null value to represent "no value."

Like all other data in InfoSphere DataStage BASIC, the null value is represented internally as a character string. The string is made up of the single byte CHAR(128). At run time when explicit or implicit dynamic array extractions are executed on this character, it is assigned the data type "null." InfoSphere DataStage BASIC programs can reference the null value using the system variable @NULL. They can test whether a value is the null value using the ISNULL function and ISNULLS function.

There is no printable representation of the null value. In this manual the symbol l (lambda) is sometimes used to denote the null value.

Here is an example of the difference between an empty string and the null value. If you concatenate a string value with an empty string, the string value is returned, but if you concatenate a string value with the null value, null is returned.

A = @NULL
B = ""
C = "JONES"
X = C:B
Y = C:A

The resulting value of X is "JONES", but the value of Y is the null value. When you concatenate known data with unknown data, the result is unknown.

Programmers should also note the difference between the null value, a special constant whose type is "null," and the stored representation of the null value, the special character CHAR(128) whose type is "string." BASIC programs can reference the stored representation of null using the system variable @NULL.STR instead of @NULL.