[yunqa.de] Strange data types returned by DISQLiteUNIDirQuery ?
- From: H M <docshotmail2@xxxxxxxxxxx>
- To: DISQLite3 mailing list <yunqa@xxxxxxxxxxxxx>
- Date: Thu, 4 Oct 2012 11:13:46 +0000
I use DISQLiteDataBase--> DISQLiteUNIDirQuery --> DataSetProvider -->
ClientDataSet --> DataSource --> DBgrid
I store UK money amounts as integers, ie the number of pennies, and then
convert to pounds and pence in the SQL using a calculation (eg 'TheAmount
*1.0/100'). Then I use a dbgrid to show the amount, using the ClientDataSet
field editor to set the DisplayFormat to £#,##0.00
The problem is that DISQLiteUNIDirQuery does not seem consistant in the data
types its returns for similar SQL, so sometimes the money amount comes back as
a TDSQLite3WideStringField type and so there is no DisplayFormat property
available in the ClientDataSet and the amount displays incorrectly.
I've tried using OnInitFieldDef to force the datatype to be TFloatField but
then get errors along the lines of 'expecting TFloatField but received
TDSQLite3widestringField '.
I've tried playing with query field editor and even tried altering the type in
the source code of the datamodule class, all to no avail.
Is there a 'proper' way to make sure that SQL calculations resulting in floats
get returned by DISQLiteUNIDirQuery as floats ?
Howard
Excuse the length of this post but below are some typical examples from the
work in front of me now.
eg
..................................................................................................
Query1 - InvoiceTotal is returned as TFloatField - I have csd.DisplayFormat and
can set it to £#,##0.00
SELECT
OrderID,
Sum(OrderLineTotal*1.0/100) as InvoiceTotal
FROM
TblOrderItems
GROUP BY
OrderID
....................................................................................................
Query1 - InvoiceTotal is now returned as TLargeIntField - but I still have
csd.DisplayFormat and can set it to £#,##0.00
SELECT
InvoiceID,
Sum(InvoiceLineTotal*1.0/100) as InvoiceTotal
FROM
TblInvoiceLines
GROUP BY
InvoiceID
.....................................................................................................
Query1 - InvoiceTotal is returned as a TDSQLite3widestringField - I DO NOT have
csd.DisplayFormat so can't set it to £#,##0.00
SELECT
TblInvoices.InvoiceID,
(SELECT
sum(TblInvoiceLines.OrderLineTotal*1.0/100)
FROM
TblInvoiceLines
WHERE
TblInvoiceLines.InvoiceID = TblInvoices.InvoiceID
) AS InvoiceTotal
FROM
TblInvoices
.................................................................................................
Other related posts:
