SQLite returns TWideStringField for NULL aggregate functions

user_7051608 0
Viewed 579

I have a SQL statement in a TFDQuery tied to an SQLite3 database. If I perform the following query it works fine if there rows that meet the WHERE clause. In this case MyMield is a float and when I pre-build the table fields using the field editor it shows up correctly as a TFloatField. However, if there are no matching rows (empty result), the MyMaxField is return as a TWideStringField. This does not match the type of field object I created at design them and returns and error (even at design time).

SELECT max(MyField) AS MyMaxfield FROM MyTable WHERE MyOtherFfield=10

I also tried using casting the max() to a REAL and it still fails.

Delphi 10.2

delphi sqlite
1 Answers

This is described in Adjusting FireDAC Mapping chapter of the FireDAC's SQLite manual:

For an expression in a SELECT list, SQLite avoids type name information. When the result set is not empty, FireDAC uses the value data types from the first record. When empty, FireDAC describes those columns as dtWideString. To explicitly specify the column data type, append ::<type name> to the column alias:

SELECT count(*) as "cnt::INT" FROM mytab

So to resolve your problem simply specify data type for your expression column. For example:

SELECT
   MAX(MyField) AS "MyMaxfield::REAL"
FROM
   MyTable
WHERE
   MyOtherFfield = 10
user_80412310
Related