Search code examples
delphifiredacaggregates

Using FireDac Aggregates

I am using XE6 with FireDac. I have a FDMemTable with ItemVals as a ftfloat field. I would like to use the aggregates function of SUM(ItemVals) but I would like only ItemVals with a value >0 to be added in. ItemVals is a ftfloat but as far as I can find, I cannot assign a null value to a float. So I am using a value of -1 to indicate (to me) a null value. I tried SUM(ItemVals)>0 but it just returns a TRUE. Can anyone point me in the right direction? Thanks.

Solution

  • You can use extended expression syntax and write for your SUM expression condition with IIF and return the field value when it's greater than 0, and 0 when it's less or equals to 0. Like this way:

    Aggregate.Expression := 'SUM(IIF(ItemVals > 0, ItemVals, 0))';

    But back to the root of your question. You've said, that you cannot find a way to assign a NULL value to a field of ftFloat data type. My guess is that you're assigning values this way:

    FDMemTable.FieldByName('ItemVals').AsFloat := 1.23;

    Yes, that way you really cannot assign a NULL value, but you can do it through the Value property of the field, e.g.:

    FDMemTable.FieldByName('ItemVals').Value := NULL;

    Assigning NULL value instead of -1 will much better describe the intention of no value, and simplify your aggregate expression back to SUM(ItemVals).