|
<< Click to Display Table of Contents >> dBASE/FoxPro numerics |
  
|
|
<< Click to Display Table of Contents >> dBASE/FoxPro numerics |
|
In the original dBASE format the only numeric field type was NUMERIC handling either integers only or decimal values according to the dimensioning of the field.
Later, with dBASE IV, the numeric field type FLOAT was added but technically being identical to the NUMERIC field. The reason for adding this technically identical field type is obscure, but maybe has been to more clearly signify the nature of the content by using either NUMERIC or FLOAT fields when exchanging data with other systems.
Until the advent of dBASE 7, the only ways to store dBASE numerics were as the field types NUMERIC as well as FLOAT storing numerics as text! This means sizing a numeric dBASE field must take into account the need also for the decimal character as well as possible minus character for negative values.
E.g. a field like NUMERIC(3,1) will never accept a negative decimal value as e.g. -0.4 taking up four characters.
This way, the NUMERIC fields specified as e.g. DECIMAL(6,2) may hold the following numerals (comma as decimal separator):
123456
123,45
12,34
-12345
1234,5
-123,4
where the truncations as a consequence of the text nature of the fields depends on whether the number is signed and if it has a decimal separator.
Thus a NUMERIC/FLOAT field intended for up to four decimals including negative values should never be given a size below 7 [NUMERIC(7,4) or FLOAT(7,4)], enabling the field to hold e.g. the value -0,1234. Without decimals such a field may hold positive values up to 9999999 and negatives down to -999999, each taking up 7 positions.
For backward compatibility dBASE 7 tables still provide the text-stored numerics NUMERIC and FLOAT along it's LONG and DOUBLE types.
In SQL the dBASE NUMERIC fields may be specified as either DECIMAL(s,p) or as NUMERIC(s,p), s giving the size/scale of the field and p the precision (number of decimals). Specifying s and omitting p or specifying it as zero defines the field for integer values only.
When using Local SQL specifying FLOAT(s,p), it will ignore the dBASE FLOAT field type and translate to NUMERIC fields. By not allowing to specify zero decimals, it may specify integer fields only by omitting the decimal specification as in FLOAT(4).
Except when specifying s = 1, all NUMERIC and FLOAT fields will also accept negative numbers.
The maximum field size for NUMERIC and FLOAT fields is 20, as in NUMERIC(20,4) or FLOAT(20,4).
In fields sized as e.g. NUMERIC(20,4) or FLOAT(20,4) the total number of positions allowed is 20 with 15 integer digits, one position for the decimal separator, four positions for the decimals, or 20 positive integer digits not using the space for decimals.
The maximum allowed space for decimals is the total size minus 2.
With dBASE tables using SQL, the command DECIMAL(s,p) is an alternative for specifying NUMERIC fields.
Do note, that SQL FLOAT is not related to the dBASE FLOAT type field!
SQL Float will with dBASE create either NUMERIC or the dBASE 7 Double type fields.
Also, do note the odd translation to dBASE from SQL SmallInt to Numeric(6,0) and Integer to either Numeric(11,0) or the incompatible Long (dBASE 7).
Please see SQL data type translation BDE.
Even though the maximum number of digits (i.e. positions) for NUMERIC and FLOAT is specified as 20, which for negative decimal numbers will be 18 - one position for the minus sign and one for the decimal separator. However, this must not be confused with the maximum number of significant digits, which is 15 to 16. The background for this not fixed number of significants may be related to the nature of handling large numbers by binary number crunching.
The figure of 20 digits/18 decimals does hold for e.g. values like 0,000123456789012345 displaying 18 decimals with 15 significant digits.
Also, care should be taken entering numerals into dBASE fields of limited size - e.g. entering 234567890,5 into a NUMERIC(8,3) field may end up as e.g. 235000000 due to numeric overload related to field size, though still compliant with the scale of the entered value expressed as 2,35e8.
Figures larger than 20 integer digits in a max. sized NUMERIC or FLOAT field are still accepted but transforms into numbers as exponents of 10 and mantissa with up to 15 digits.
See also Numerics/NULLs in dBASE apps.
__________________________