TIBCO Live Datamart

Null-value handling in aggregate functions is different from null handling in simple equations, for example making sum(a+b) functionally different from sum(a)+sum(b). This may result in complex expressions involving aggregation which are incorrect for the target use-case.

Live Datamart Aggregate Query Null-value Handling

Care should be taken to determine whether the simple-expression always returns the desired results prior to using it within the bounds of an aggregation function. When a null value is input into a non-aggregate equation, the result is null regardless of the other field values. When a null is present in the set of inputs into an aggregate function, the null is ignored.

For example, shown here is a LiveView table of double values indexed on field 'id':

Here are the columns summed individually:

Here the individual values are added (a simple equation) before aggregation:

This result shows that rows 1 and 2 each result in 'null' which is ignored and only the values from row 3 participate.

Here the columns are individually summed and then the result added:

This result shows that individually summed, all values from column a and b participate.

For example, shown here is a LiveView table of double values indexed on field 'id':

lv-client "select * from datatable" +----+------+------+ | id | a | b | +----+------+------+ | 1 | null | 2.0 | | 2 | 4.0 | null | | 3 | 1.0 | 5.0 | +----+------+------+ Rows = 3

Here are the columns summed individually:

lv-client "select sum(a) as sum_a, sum(b) as sum_b from datatable" +-------+-------+ | sum_a | sum_b | +-------+-------+ | 5.0 | 7.0 | +-------+-------+ Rows = 1

Here the individual values are added (a simple equation) before aggregation:

lv-client "select sum(a+b) as sum_ab from datatable" +--------+ | sum_ab | +--------+ | 6.0 | +--------+ Rows = 1

This result shows that rows 1 and 2 each result in 'null' which is ignored and only the values from row 3 participate.

Here the columns are individually summed and then the result added:

lv-client "select sum(a)+sum(b) as ab_sum from datatable" +--------+ | ab_sum | +--------+ | 12.0 | +--------+ Rows = 1

This result shows that individually summed, all values from column a and b participate.

Yes

No