Standard decimal handling rules are used by default (the decimal_rule parameter in config.dat is set to Standard rather than Classic).

In expressions that combine decimal values and return decimal results, the precision (total number of digits) and scale (number of digits to the right of the decimal point) of the Standard result can be determined, as shown in the following table:

If the result precision is larger than 39, the result scale is reduced by the amount of the precision excess, and the result precision is set to 39. The result scale is not reduced below 4 if the input scales are 4 or more; if both input scales are less than 4, the result scale is not reduced to less than the larger.

For example, in the following decimal addition operation:

the scale and precision of the result is calculated as follows:

Precision = 7, calculated as 3 (largest number of fractional digits) + 3 (largest number of non-fractional digits) + 1 = 7.

Scale = 3. The first operand has the largest number of digits to the right of the decimal point.

Result: 0569.124

If exponentiation is performed on a decimal value, the resulting data type is float.

Decimal arithmetic is handled according to the setting on the decimal_rule parameter in config.dat--either Classic or Standard.

The following examples compare Classic and Standard decimal handling. Given the input precision and scale, the result precision and scale for Classic and Standard settings are shown.

The Classic rules follow the SQL Standard specifications for result scale, even when the precision has to be limited to the maximum of 39. The effect is that in a complex expression, the result scale tends to be large, which reduces the number of available non-fractional digits and makes numeric overflow more likely.

The Standard rules reduce both precision and scale when precision gets too large, which favors non-fractional digits at the expense of fractional digits and makes numeric overflow less likely.

To specify error handling for numeric overflow, underflow, and division by zero, use one of these methods:

where option is one of the following: