Null Values in Spectre

Null indicates "no data". Here are some ways to get a null value in Spectre:

  • A blank field, that is a zero-length field, in a source file read by Spectre build
  • A value flagged as null using the nulls tag in Spectre build
  • A NULL value in an ODBC table read by Spectre build
  • The result of a summary expression in a dive window using multiple inputs, when the dimension value is not present in one of the inputs (that is, a multi-level multi-model)
  • The result of a summary expression when the working set is empty
  • The expression `null`
  • The expression `""`

You can check for null using the is_null() and notnull() functions, and use the coalesce() function to substitute alternative values for null.

Every null value has a type, based on what the type of the value would be if it were not null. Null behaves differently depending on how it is used and what type it is:

  • For numeric types (integer, fixed100, and double), null is sometimes treated as zero, but there are exceptions depending on how it is used
  • For strings, null is the empty string and vice versa, for example, `"" == null` is true
  • For dates and periods, null is never treated as similar to a normal date or period, it is always considered special
  • For boolean types, null is treated as false

See also: