At 06:59 PM 11/10/2007, you wrote:

>I have an SQL which is
>
>SELECT COLA, ... ,sum(COL1),sum(COL2),(((COl3 * sum(col2)/(col4 +
>0.0001)) FROM ... GROUP BY ...
>
>All of columns (COL1..COL4) are defined as NUMERIC - the 0.0001 is
>protection against COL4=0
>
>I'm displaying this in a DevExpress grid which identifies the field
>type from the presented results. It identifies both of the SUM's as
>type currency, and the calculated field as type FMTbcd.
>
>The data appears to be displayed without problems, however if I try
>and use the DevExpress features to group the data I sometimes get
>BCDoverflow errors for no apparent reason.
>
>I had expected a calculation involving numerics and using floating
>point calculations to result in a float. I fixed the problem by using
>CAST(((COl3 * sum(col2)/(col4 + 0.0001)) as FLOAT)
>DevExpress recognises this, which confirms that the 'incorrect' type
>is included in the SQL results.
>
>Can someone explain how this works?

BCD is Binary Coded Decimal and every implementor of Delphi
components seems to have his own ideas about it. Delphi's TBCDField
is a decimal with a scale of 4; check the docs of the DevExpress
components to see what their BCD field type is.

Not all BCD implementations are very tidy about adjusting the scale
of numerics from the database when converting. You're really taking
a risk not looking after the scale of your caclculated field result,
anyway. Fixed numerics (Numeric and decimal types) are not floats
(or doubles, either, except in sub-v.5.6 InterBase). And a fixed
numeric multiplied or divided by another fixed numeric produces a
fixed numeric, not a floating point type.

You'll overflow a numeric quite easily because multiplication or
division produces a result whose scale is the sum of all the scales
of the operands. The exception will happen in the database, then,
instead of in the component. Include a cast in the expression that
calculates your output field. Test the maximum scale that your
component will tolerate and cast to that.

./heLen