Using Nulls

In most cases with StreamBase expressions, any time you apply an arithmetic operator or function with a field that is null, the result is null. The four exceptions are the isnull() and notnull() functions, which test whether a field is null, and the coalesce() and coalesce_tuples() functions, which select a non-null value from a set of potentially null arguments.

To use an expression that intentionally sets the result to null, use one of the data type-specific null literals, as shown in the list below.

blob(null)
bool(null)
double(null)
int(null)
long(null)
string(null)
timestamp(null)

Use the nulllist() function to create a null list, as described in Null Lists. Null tuples are described in Null Tuples.

The data type of each null is never implicit: you must specify which type of null you are using in the expression.

In general, expressions involving null evaluate to null. However, some Boolean logic statements can contain unknowns or nulls, but the rest of the expression fully determines the truth value. In the following table, the order of the arguments does not matter.

This is three-value Boolean logic as used in standard SQL. Null is not a value in the same sense as true and false, but is instead a placeholder for the absence of value. Some RDMS systems refer to this placeholder as Unknown; StreamBase uses the term null.

StreamBase provides the isnull() and notnull() functions, allowing you to test whether fields in expressions are null.

For example, an expression in a Spotfire Streaming operator could use:

if (isnull(fieldA)) then notnull(fieldB) else true

The coalesce() function returns the first non-null value from its list of arguments.

Since literal values are never null, you can specify a literal value as the last argument to provide an effective default value for the list. In this way, coalesce() can be used to emulate the NVL() function provided by Oracle PL/SQL and the two-argument ISNULL() function provided by Microsoft T/SQL. For example, the following expression returns the value of fieldA if it is non-null, or 0 if it is null:

coalesce(fieldA, 0)

The following example returns the first non-null field among fieldA, fieldB, and fieldC in that order, or returns -99999 if all three fields are null.

coalesce(fieldA, fieldB, fieldC, -99999)

To illustrate how to specify nulls, assume an example where the schema for a tuple contains these fields:

In the input data CSV file:

The same rules apply when you use the Manual Input view in the SB Test/Debug perspective.

A null list is a list with no elements, where the entire list is null. To specify a null list, use the nulllist() function.

A null list is not the same as an empty list, which is a list with zero elements. In this case, the list itself is not null. To specify an empty list, use the emptylist() function.

To understand the difference between empty and null lists, consider the following four expressions evaluated with a Map operator's Additional Expressions grid or with the Evaluate StreamBase Expression dialog.

list(99)

Result type: (list(int)
Result: [99]

list(int())

Result type: (list(int))
Result: [null]

emptylist(int())

Result type: (list(int))
Result: []

nulllist(int())

Result type: (list(int))
Result: null

The four commands in this example demonstrate the following:

A null tuple results when the entire tuple is set to null, not just the fields of the tuple. A null tuple is not the same as:

To specify a null tuple that uses a named schema, specify the schema name with empty parentheses. For example, for the schema named nyse_data, the expression nyse_data() creates a null tuple.

When using an unnamed schema, you can only create a null tuple by coercion. For example, consider the following two expressions evaluated with a Map operator's Additional Expressions grid or with the Evaluate StreamBase Expression dialog:

if true then tuple(int() as x, double() as y) else null

Result type: ((x int, y double))
Result: null,null

if false then tuple(int() as x, double() as y) else null

Result type: ((x int, y double)) 
Result: null

The commands in this example demonstrate the following:

The following table explains how each Spotfire Streaming operator handles null values in tuple fields.

When developing StreamBase client applications, you will generally interact with nulls in the StreamBase Client Libraries when you are either creating tuples to be enqueued, or receiving dequeued tuples.

When creating a tuple, it is important to note that all the values in the tuple are initialized to null, and it is your responsibility to set the value. When dequeuing a tuple, determine whether a tuple field value is null before accessing the value. The following client code snippets demonstrate these concepts:

For more information on using nulls in the Client library, see:

By default, the SB Test/Debug perspective of StreamBase Studio uses the keyword null to indicate a null value. You can specify a different null token by changing the value in the Indicate nulls using field in the StreamBase Studio>Test/Debug pane of the Window>Preferences dialog.

The null token is used in editors and views when a field contains no value. You can substitute any string to indicate nulls. This change does not affect the use of null literals in expressions, which must use the reserved keyword shown in the previous sections, such as int(null).

Back to Top ^