How do I use an aggregate field in a TClientDataSet that is a BIGINT (TLargeintField)?

Question

I need to calculate the Max value of a field, but I'm having troubles doing so. Let's say that my field is named 'VALUE0'. I would like to use the aggregate functions of TClientDataSet to do so. What should I do?

This code will fail only with fields that are BIGINT in my SQL table:

function TFrmIo.GetMaxY(): Integer;
var
  Max0: Integer;
  FieldMax0: TAggregateField;
begin
  if cds.Active then cds.Close;

  FieldMax0 := TAggregateField.Create(cds);
  FieldMax0.FieldName := 'MAX0';
  FieldMax0.Calculated := true;
  FieldMax0.ResultType := ftLargeint;
  FieldMax0.FieldKind := fkAggregate;
  FieldMax0.DataSet := cds;
  FieldMax0.Expression := 'MAX(VALUE0)';
  FieldMax0.Active := true;

  cds.Open;

  Max0 := Integer(FieldMax0.Value);
end;

I get this exception on the "cds.Open" line:

Exception class EDBClient with message 'Type mismatch in expression.'

EDIT

As requested in the comment, the class name of VALUE0's field is TLargeintField and the FieldKind is fkData.

EDIT 2

Changed the question and some parts in the text because now I know that the problem is about BIGINT vs INTEGER in TClientDataSet aggregate functions.

tell us some of the things you tried and what problems you had. — PA., Nov 24 '11 at 14:05
What type field is Value0 in the ClientDataset? And the value of FieldKind? — Arjen van der Spek, Nov 24 '11 at 15:00
@Haole I tested it with integers and it works as expected, but can you verify that with: showmessage(cds.FieldByName('value0').ClassName) ? And what is the value of cds.FieldByName('value0').FieldKind ? — Arjen van der Spek, Nov 24 '11 at 16:08
Just out of curiosity, why are you using `ResultType := ftInteger` when you know it's returning a `BIGINT`? Did you try `ftLargeint` instead? — Ken White, Nov 24 '11 at 22:46
Your chances may be slim... [QC#83610](http://qc.embarcadero.com/wc/qcmain.aspx?d=83610) - "Unable to use TLargeint in Aggregate Field". — Sertac Akyuz, Nov 25 '11 at 00:45
@KenWhite its a typo... the code fails with ftLargeint as well. — ivarec, Nov 25 '11 at 13:08
@haole - I don't have anything to add to what you have written in the answer, and it's at least as good as if I'd write it. :) Please accept your own answer.. — Sertac Akyuz, Nov 25 '11 at 21:18

score 1 · Answer 1 · answered May 17 '17 at 10:26

As a workaround one could use 'MAX(VALUE0 * 1)' or 'MAX(VALUE0 + 0)' for Expression. Then your ResultType will be ftFloat. Just don't forget to round the value when you access it: Max0 := Round(FieldMax0.Value); (floats tend to have a tiny tail sometimes :)

score 1 · Accepted Answer · edited Nov 25 '11 at 21:16

1

As pointed out by Sertac Akyuz, it is not possible to do so on Delphi 2010 and below. Seems to be fixed in Delphi XE, although I haven't tested it.

http://qc.embarcadero.com/wc/qcmain.aspx?d=83610

edited Nov 25 '11 at 21:16

Sertac Akyuz

54,131
4
102
169

answered Nov 25 '11 at 13:12

ivarec

2,542
2
34
57

Note that [QualityCentral has now been shut down](https://community.embarcadero.com/blogs/entry/quality-keeps-moving-forward), so you can't access `qc.embarcadero.com` links anymore. If you need access to old QC data, look at [QCScraper](http://www.uweraabe.de/Blog/2017/06/09/how-to-save-qualitycentral/). – Remy Lebeau Jun 09 '17 at 17:33

How do I use an aggregate field in a TClientDataSet that is a BIGINT (TLargeintField)?

2 Answers2