Subject Re: [IBO] NUMERIC(17,2) and Calculated fields
Author Geoff Worboys
> I'm Using IB_query and I use numeric(17,2) very often. Becouse I'm
> living in a high inflation country (TURKEY).

You have a problem. The only Delphi/Pascal support that exists for
scaled integers is the currency datatype. currency is fixed to scale
4 - which makes it equivalent to numeric(18,4), which means that it
has only 14 digits of integer signficance and 4 decimal places.

To quote a previous posting of mine...

- - - - - - -
And my final recommendation is to document the restrictions that exist
with AsCurrency. They still apply even after the above adjustments
are made. That is: Given NUMERIC(x,y)

A. IB/FB will not restrict input according to the declared precision,
so it is possible to insert values to the full storage limit (if x gt
9 then this means upto 18 digits regardless of x in the declaration).
To prevent the database from storing values that may overflow the
Delphi currency type (when the scale < 4) you need to apply a check
constraint on the domain/field similar to...

CHECK( VALUE BETWEEN -99999999999999.99 AND +99999999999999.99 );

CHECK( VALUE BETWEEN -99999999999999.999 AND +99999999999999.999 );

B. If the declared scale is > 4 then Delphi currency will lose
value resolution - it only supports exactly 4 decimal places. So if
you need a scale > 4 dont use AsCurrency - AsExtended is probably the
best you can do.
- - - - - - -

I was going to suggest reverting to floating point, but IB/FB only
supports double (15 digit signficance), whereas you need extended.

The only other single field option that exists is for someone to write
a true scaled integer class to support numeric(x,y) datatypes more

You mention preferring something like (19,2) - but as you realise this
cannot be achieved to full resolution, and even if you were able to
get support for extended datatype you would be going beyond the
accuracy of that datatype.

There are some other options in regard to using compound fields and
special numeric classes in the client, but I suspect that that would
get very complicated.

> my first problem is;
> IB_Currency edit gives me only 1234567890123,12 this is not enough
> for me (I thik 17,2 will be a problem for me I need 19,2 indeed)

I will leave this to the author of that control, IMO it should be
setup to support 14 digits of integral value input.

> the second;
> when I use IB_edit it lets me write 12345678901234567,12
> but gives me a nice error "Invalid floating point value"

That will be the result of my explanation above.

> I need very urgent solution for currency I dont want to rewrite
> whole the things I do for 2 months. (I used to think native IB must
> have a solution for this I test this issue this morning)

Indeed, at the moment there are worse problems with the TDataset
stuff. And in any case you will hit the same restrictions there

> I send sample apllication about calculated field problems to Jason
> and Geoff. Geoff (Thanks for his Interest) has accept the problem
> but we don't have any solutions yet. When'll we hear sth from Jason
> ?

There is a simple enough work around for this particular problem.
Set CalculateAllFields to false and then alter your code so that you
check each field individually before calculating...

if AField.FieldName = 'MYFIELD' then
<calculate it>

The only time this approach is not efficient is if you have calculated
fields interacting with one another and need to be sure the
interactions are calculated appropriately. If such a situation does
not exist in your code then the above approach will be just as good as
CalculateAllFields (and in some some circumstances better), even when
the particular bug has been resolved.


Geoff Worboys
Telesis Computing