GetNullElementsIf function

Syntax

GetNullElementsIf (
	ts          TimeSeries,
	expr        lvarchar,
	begin_stamp datetime year to fraction(5) default null,
	end_stamp   datetime year to fraction(5) default null) 
returns  TimeSeries (TSMatchingCount);

GetNullElementsIf (
	ts          TimeSeries,
	col         lvarchar,
	op          lvarchar,
	value       lvarchar,
	begin_stamp datetime year to fraction(5) default null,
	end_stamp   datetime year to fraction(5) default null) 
returns TimeSeries (TSMatchingCount);

GetNullElementsIf (
	ts          TimeSeries,
	col         lvarchar,
	op          lvarchar,
	value       decimal,
	begin_stamp datetime year to fraction(5) default null,
	end_stamp   datetime year to fraction(5) default null) 
returns TimeSeries (TSMatchingCount);

ts

The time series to count.

expr

An expression to filter elements by comparing element values to a number or string. You can combine multiple expressions with the AND or the OR operator and use parentheses to nest multiple expressions. Use the following arguments within an expression:

expr_col

The name of the column within a TimeSeries data type. The supported column data types are: SMALLINT, INT, BIGINT, INT8, REAL, FLOAT, DECIMAL, DATE, MONEY, INTERVAL, DATETIME, CHAR, NCHAR, VARCHAR, NVARCHAR, LVARCHAR, BOOLEAN and BSON. If the column is of type BSON, the expr_col must be the path to a field in one or more BSON documents in the BSON column. If the BSON top-level field name is the same as another column in the TimeSeries data type, you must precede the field name with the BSON column name and a dot: bson_column_name.bson_path. The BSON field is automatically cast to a FLOAT data type, however, you can explicitly cast a BSON field to an INTEGER, BIGINT, or LVARCHAR data type. For example, the following expression extracts values as INTEGER data types from a BSON field: 'engine.temp::integer > 100'

expr_value

The value that is used in the comparison. Can be either a number, a string, or NULL. When comparing a column of type BOOLEAN, INTERVAL or DATETIME, the expr_value must be a string that is surrounded by quotation marks. This string is cast to a appropriate type depending on the type of the expr_col column in the expression. Provide the string in the appropriate format. If the expr_col is a DATEMTIME column, the expr_value must be in DATETIME format: 'datetime_col < "2016-04-12" '. If the expr_col is an INTERVAL column, the expr_value must be in INTERVAL format, for example: 'interval_col > "2 12:00:00" '. If the expr_col is a BOOLEAN column, the expr_value must be in BOOLEAN format, for example: 'boolean_col = "t" '.

logical_operator

The AND or the OR operator.

begin_stamp (optional)

The begin point of the range. Can be NULL. By default, begin_stamp is the beginning of the time series.

end_stamp (optional)

The end point of the range. Can be NULL. By default, end_stamp is the end of the time series.

col

The name of the column within a TimeSeries data type. Can be prefixed with the words IS NULL OR. Must be surrounded by quotation marks. The supported column data types are: SMALLINT, INT, BIGINT, INT8, REAL, FLOAT, DECIMAL, DATE, MONEY, INTERVAL, DATETIME, CHAR, NCHAR, VARCHAR, NVARCHAR, LVARCHAR, BOOLEAN and BSON. If the column is of type BSON, the col must be the path to a field in one or more BSON documents in the BSON column. If the BSON top-level field name is the same as another column in the TimeSeries data type, you must precede the field name with the BSON column name and a dot: bson_column_name.bson_path. The BSON field is automatically cast to a FLOAT data type, however, you can explicitly cast a BSON field to an INTEGER, BIGINT, or LVARCHAR data type. For example, the following expression extracts values as INTEGER data types from a BSON field: 'engine.temp::integer > 100'

op

An operator. Can be <, <=, =, !=, >=, or >. Must be surrounded by quotation marks.

value

The value that is used in the comparison. Can be either a number, a string, or NULL. When comparing a column of type BOOLEAN, INTERVAL or DATETIME, the value must be a string that is surrounded by quotation marks. This string is cast to a appropriate type depending on the type of the col column in the expression. Provide the string in the appropriate format. If the col is a DATEMTIME column, the value must be in DATETIME format: 'datetime_col < "2016-04-12" '. If the col is an INTERVAL column, the value must be in INTERVAL format, for example: 'interval_col > "2 12:00:00" '. If the col is a BOOLEAN column, the value must be in BOOLEAN format, for example: 'boolean_col = "t" '.

Usage

The GetNullElementsIf function is an extension of the CountIf function. Using this function instead of GetElementsIf will allow a timeseries to be returned that includes information on where NULL elements existed in a regular timeseries. In this case the row type returned is the same as the original timeseries but what the user will see are the individual time stamps for the missing slots.

Returns

CREATE ROW TYPE TSMatchingCount (
        tstamp      datetime year to fraction(5),
        count       integer
);

The tstamp is the position in the timeseries where the start of the expression match occurs. The count is the number of consecutive elements that match the expression.

If the expression contains other clauses that retrieve normal data then that will be included in the result set and be presented with the correct values like GetElementsIf.