Defining Numeric Fields with Decimals
Zim supports the specification of decimal places for fields whose internal representation is in the form of integers (data types “int”, “longint” and “vastint”). This is done by maintaining the logical position of the decimal point external to the physical storage of the data. SQL servers do not do this – integer data types contain integer data only. If the data for a field has either explicit or implicit decimal places, then it must be defined with the appropriate SQL data type (DECIMAL, NUMERIC, NUMBER, FLOAT, DOUBLE PRECISION, etc.). To formalize the translation of field definitions between Zim and SQL, Zim insists that fields of data type “int”, “longint” and “vastint”, which have non-zero decimal places, also have an appropriate value (greater than zero) in the “Length” field of the Data Dictionary entity set “Fields”. The value ranges for the “Length” field are
|Data Type||Range for “Length”|
|“int”||1 – 4|
|“longint”||5 – 9|
|“vastint”||10 – 15|
The values specify the maximum number of significant digits that the field is required to support. This number is the sum of the number of digits to the left of and to the right of the (implied) decimal point. For example, if it is known that data values for a particular field range from 0.01 to 9.99, then in standard Zim, the definition of the field could be
Type = ‘int’ Length = 0 Decimals = 2
For SQL tables, the definition must be expressed as
Type = ‘int’ Length = 3 Decimals = 2
In SQL servers, these two numbers are generally referred to as the “Precision” (Length) and “Scale” (Decimals) for columns of data type NUMERIC, DECIMAL, and so on.
The specification of a non-zero value for the “Length” in Zim is not a concern. For these data types, Zim ignores the “Length” value. On the other hand, for SQL tables, Zim checks to ensure that the “Length” value lies within the ranges stated above. The Zim Definition Interface uses the “Length” values to translate Zim number field definitions to the appropriate SQL definitions and vice versa. Even though an “int” value has limited support for 5 digits (i.e. up to 32767), it cannot support up to 99999 and accordingly, its real maximum precision is 4. Consider an SQL data type of DECIMAL(5,2). Such a definition can support values from -999.99 to 999.99. A Zim data type of “int” cannot support this complete range. The Definition Interface translates such a definition into a Zim “longint” data type with a “Length” of 5 and “Decimals” of 2.
To a Zim application developer, the impact of this requirement is relatively small. For all fields of type “int”, “longint” and “vastint” which have a “Decimals” value greater than zero, a “Length” value from the ranges listed above must also be specified. The “Length” value must also be sufficiently large to support the maximum expected number of significant digits for data values for the field.