3

I have a ClientDataSet which loads its data from a table in a SQLite database using a TFDQuery.

The SQL query selects fields from a table of bank accounts: the AccountID, Name, StartBalance and CurrentBalance. CurrentBalance is a calculated field.

This is the query:

SELECT 
  p1.AccountID, 
  p1.Name, 
  p1.StartBalance,
  cast(
    p1.StartBalance 
    + (SELECT TOTAL(Amount) 
       FROM Trx p2
       WHERE (p2.AccountID1 = p1.AccountID
              AND p2.TrxType = 'income')
          OR (p2.AccountID2 = p1.AccountID
              AND p2.TrxType = 'transfer'))
    - (SELECT TOTAL(Amount) 
       FROM Trx p3
       WHERE p3.AccountID1 = p1.AccountID
         AND (p3.TrxType = 'expense' OR p3.TrxType = 'transfer'))
   as Currency) AS CurrentBalance
FROM Account p1;

This query runs perfectly well and produces the expected results. Also, when there are existing records in the Account table, I am able to successfully load the results into a ClientDataSet with the following FieldDefs:

  • AccountID (ftInteger)
  • Name (ftString)
  • StartBalance (ftCurrency)
  • CurrentBalance (ftCurrency)

However, when there are no records in the table and I attempt to open the ClientDataSet (cdsAccount.Open), I receive the runtime error: "Invalid field type". If I remove the calculated field (CurrentBalance) from both the query and the ClientDataSet's FieldDefs, then no error is produced.

Scott Hallauer
  • 389
  • 2
  • 8
  • 16
  • 3
    http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Using_SQLite_with_FireDAC#Adjusting_FireDAC_Mapping – CL. Aug 09 '17 at 12:56
  • Oh great! Thank you, this solved my problem. Just needed to select AS "CurrentBalance::Currency" – Scott Hallauer Aug 09 '17 at 13:05
  • I was [trying to rephrase](https://stackoverflow.com/review/suggested-edits/16741951) that question to make it more general. – Victoria Aug 09 '17 at 13:42

0 Answers0