Value of SQLITE Sum Is TWideStringField?

[TinTin]

I have 2 tables,it is master-detail Relation,they have a Float Field( Name is AMT,meaning is Amount ).
for example,the Sql is

select A.*, iFNull( (Select Sum(B.AMT) from F_LoanSub B Where B.LoanID = A.ID),0.00) As AMT2 from F_Loan A

Whether or not Using "IFNULL", "AM2 " Field In The UniQuery Is TWideStringField. If Table F_LoanSubSo Have No Data To Match Master Table ,It raise Error "

qryBase:Type Missing for Field "AMT2",expecting: Widestring actual: Float ".
I try modify SQL for Cast AM2 Field to Real,BUT IT Still error!

how to solve this problem? Thanks!

develop environment:
delphi xe+unidac(3.60.0.16)+sqlite expert,AMT Field is Float Type!

[AlexP]

Hello,

The problem is connected with thefact that DataSet in Delphi should have only one type for a column, and we can't change it.
If the float field in SQLite is empty, its type is string. If it is not empty, the type of this field is float.

You can try to add AMT2 field as a float field to your DataSet manually.

[TinTin]

I have Sent sample to [email protected],Please Use my Sqlite3.dll AND SQLITE DB.

Thanks !

[AlexP]

Hello,

As I wrote you earlier, the problem is connected with the SQLLite behaviour, and you should contact to SQLite developers.

[TinTin]

If I add All Field in designTime or Create Calc Field in Run Time,It raise error:Expect TWidestringField Actual TFloatField . But if dont do that,it runs normal:TFloatField!

[TinTin]

I both love SQLITE and hate it. Sometimes it feels Eggs-pain!

[PlutoPlanet]

A workaround for this issue is to add the typename within the SQL statement:
"<colname>::<typename>"

Example:

Code: Select all

SELECT count(*) as "mycnt::INT" FROM mytable

[MaximG]

The provided syntax ("mycnt :: INT") is not applicable when using SQLite. UniDAC allows obtaining the required type result by using Data Type Mapping : when using the

Code: Select all

SELECT count(*) as mycnt FROM mytable

query, you can get data as an integer value using the following rule:

Code: Select all

UniQuery.DataTypeMap.AddFieldNameRule ('mycnt', ftInteger);