Using Exact Numeric Data Types in Arithmetic

Go Up to Fixed-decimal Data Types

In SQL dialect 1, when you divide two integers or two DECIMAL(9,2) values, the quotient has type DOUBLE PRECISION; in other words, it is a floating-point value.

In SQL dialect 3, the quotient of two exact numeric values (SMALLINT, INTEGER, NUMERIC(n,m) or DECIMAL(n.m)) is an exact numeric, with scale factor equal to the sum of the scales of the dividend and divisor. Because a SMALLINT or INTEGER has a scale of 0, the quotient of two INTEGERs is an INTEGER, the quotient of a DECIMAL(9,2) and a DECIMAL(12,3) is a DECIMAL(18,5).

In dialect 1, the fraction 1/3 is 0.33333333333333e0; in dialect 3 it is 0. When an application does something that causes a CHECK condition to be checked, or a stored procedure to be executed, or a trigger to fire, the processing that takes place is based on the dialect under which the check, stored procedure, or trigger was defined, not the dialect in effect when the application causes the check, stored procedure, or trigger to be executed.

For example, suppose that a database is migrated from InterBase 5 and thus has dialect 1; that MYCOL1 and MYCOL2 are SQL INTEGERs; and that a table definition includes the following:

CHECK(MYCOL1/MYCOL2>0.5)

which was defined using client dialect 1.

Now suppose that a dialect 3 client tries to insert a row in which MYCOL1 is 3 and MYCOL2 is 5; because the CHECK was defined in dialect 1, the quotient will be 0.600000000000e0 and the row will pass the check condition, even though in the current client’s dialect 3, the quotient would have been the integer 0 and the row would have failed the check, so the insertion would have been refused.