Type mismatch for field 'SumVal', expecting: FMTBcd actual: Float'.

[emileverh 22]

Team!

 

I have one large application which I compile twice, one is for MySQL and the other for SQLite. For regular queries like select * from everything goes fine. But it goes wrong with SUM() functions.

The query defined in a TFDQuery below works fine in MySQL, but when I compile the application for SQLite I get an exception "Type mismatch for field 'SumVal', expecting: FMTBcd actual: Float'. "

 

The definition of AmountValue in the database is:   AmountValue DECIMAL(13,4) NOT NULL

 

And here is the query:

 

select SUM(AmountValue) AS SumVal
from camtentries

 

Is there a property for this one and only TFDQuery or for the field qrySumPositiveSumVal.AsCurrency where it compiles fine in both applications? I prefer not to set an overall property in the TFDConnection.

 

Thanks!!!

-Emile

[Dmitry Arefiev 105]

https://docwiki.embarcadero.com/RADStudio/Athens/en/Using_SQLite_with_FireDAC#Adjusting_FireDAC_Mapping

[emileverh 22]

Just now, Dmitry Arefiev said:

https://docwiki.embarcadero.com/RADStudio/Athens/en/Using_SQLite_with_FireDAC#Adjusting_FireDAC_Mapping

Nice. But this is overwhelming, I don't see any practical info for me now....

[emileverh 22]

2 hours ago, emileverh said:

Nice. But this is overwhelming, I don't see any practical info for me now....

I am sorry! Yes it was a bit overwhelming, but you helped me. So thanks 😉 I have a direction now.

The code as shown on the EMB website does not compile. With this code it compiles for both versions..... But I am losing data? The dtFloat type does not exist. I am accurate enough with this code? Please no discussions about the "with"-statements, I need to rewrite this....

 

Thanks in advance!
 

  with qrySumPositive.FormatOptions.MapRules.Add do  // Need to add the .Add else it does not compile
  begin
    SourceDataType := dtDouble;  // <<<< THIS IS A GUESS. THE TYPE dtFLOAT DOES NOT EXISTS
    TargetDataType := dtFmtBCD;
  end;
  qrySumPositive.FormatOptions.OwnMapRules := true;



  with qrySumPositive do
  begin
    ParamByName('prmDateStart').AsDateTime := MyStartOfaDay(ADateStart);
    ParamByName('prmDateEnd').AsDateTime := MyEndOfaDay(ADateEnd);
    Open;
    Result := qrySumPositiveSumVal.AsCurrency;
    Close;
  end;

 

[Dmitry Arefiev 105]

33 minutes ago, emileverh said:

The dtFloat type does not exist. I am accurate enough with this code?

Yes, it is.

Did you read about "...::type" syntax ?

[emileverh 22]

The ::TYPE syntax is even more elegant! 

 

Thanks you helped me a lot!!

[weirdo12 21]

20 hours ago, emileverh said:

Thanks you helped me a lot!!

You should post your final syntax to help others who encounter that same issue.

[emileverh 22]

The final solution is different than proposed. I did not know but the SUM() function can return NULL values. So on larger datasets I did get 'WideString'!!! conversion error messages on SQLite. So I had to use IFNULL.

May be not for all the most elegant solution. But this TFDQUERY works now for both MySQL and SQLite

 

select IFNULL(SUM(AmountValue),0) AS SumVal
from camtentries
 

And in the code:

 

var mycurr: currency := qryMyQuery.FieldByName('SumValue').Value;

 

Normally I don't work with .FieldByName and .Value. I prefer this: 

 

var mycurr: currency := qryMyQuerySumValue.AsCurrency;

 

But know the app is not complaining. I have hundreds of queries, but only 2 SUM()'s. So I can live with that. I don't use them in a loop, so performance is not an issue. And I develop the app with MySQL. Because I can easy test my data together with HeidiSQL. The field names become also strange

select IFNULL(SUM(AmountValue),0) AS "SumVal::DOUBLE:" will be  qryMyQuerySumValueDOUBLE.AsCurrency;

 

Although the first answers where not the implemented one. I want to thank all the people in general on this forum. As solo entrepreneur I don't have a collegae to ask. So thank you all!  

Edited December 7 by emileverh