www.mimer.com/developer

Evaluating Arithmetical Expressions

---

Expressions within parentheses are evaluated first. When the order of evaluation is not specified by parentheses, the customary arithmetical rules apply, i.e.

• multiplication and division are performed before addition and subtraction
• operators with the same precedence are applied from left to right.

If any operand in an expression is NULL, the whole expression evaluates to NULL. No other expressions evaluate to NULL. Division by zero results in a run-time error.

Arithmetical expressions with mixed numerical and character data are illegal. Note, however, that where host variables are used in expressions, type conversion may result in apparently incompatible data types being accepted, see Data Types in SQL Statements.

The type and precision of the result of an arithmetical expression is determined in accordance with the rules described below. If there are more than two operands in an expression, the type and precision of the result is derived in accordance with the sequence in which the component binary operations are performed.

Formally, the arithmetical rules are summarized as follows:

	FLOAT(p")	INTEGER(p")	DECIMAL(p", s")
FLOAT(p')	FLOAT(p)1	FLOAT(p)1	FLOAT(p)1
INTEGER(p')	FLOAT(p)1	INTEGER(p)2	DECIMAL(p, s)3
DECIMAL(p', s')	FLOAT(p)1	DECIMAL(p, s)3	DECIMAL(p, s)3

1 p = max(15, p', p") 2 operator +, - p = min(45, max(p', p")+1) operator * p = min(45, p'+p") operator / p = p' 3 operator +, - p = min(45, max(p'-s', p"-s")+max(s', s")+1) s = max(s', s")

operator * p = min(45, p'+p")

s = min(45, s'+s")

operator / p = min(45, max(15, p'+p"))

s = p-(p'-s')-s"

In descriptive terms, the rules are as follows:

• If any of the operands is floating point, the result is floating point.

  For all arithmetic expressions, the precision of the result is the highest operand precision. However, the precision is never less than 15. Thus:

 FLOAT(4)+FLOAT(6) gives FLOAT(15)
 
 FLOAT(20)-FLOAT(32) gives FLOAT(32)
 
 FLOAT(4)*FLOAT(4) gives FLOAT(15)
 
 FLOAT(4)/FLOAT(20) gives FLOAT(20)
 

• If all the operands are integer, the result is integer.

  For addition and subtraction, the precision of the result is the precision of the highest operand plus 1. However, the precision may not exceed 45.

  For multiplication, the precision of the result is the sum of the precisions of the operands. However, the precision may not exceed 45.

  For division, the precision of the result is the precision of the dividend. Thus:

 INTEGER(3)+INTEGER(5) gives INTEGER(6)
 
 INTEGER(20)-INTEGER(30) gives INTEGER(31)
 
 INTEGER(5)*INTEGER(18) gives INTEGER(23)
 
 INTEGER(4)/INTEGER(6) gives INTEGER(4)
 

• If all the operands are decimal, or decimal and integer operands are mixed, the result is decimal. For expressions mixing decimal and integer operands, INTEGER(p) is treated as DECIMAL(p,0).

  For addition and subtraction, the number of positions to the left of the decimal point (i.e. the difference between precision and scale) in the result is the greatest number of positions in any operand plus 1. The scale of the result is the greatest scale of any of the operands. The precision may not exceed 45.

  Thus:

 INTEGER(3)+ DECIMAL(6,3) gives DECIMAL(7,3)
 
 DECIMAL(4,2)- DECIMAL(8,5) gives DECIMAL(9,5)
 

   For multiplication, the precision of the result is the sum of the precisions of the operands. 


   The scale of the result is the sum of the scales of the operands. Neither the precision nor the scale may exceed 45. If the value of the result does not fit into the precision and scale, overflow occurs.


   Thus:

 INTEGER(3)*DECIMAL(6,3) gives DECIMAL(9,3)
 
 DECIMAL(4,2)*DECIMAL(8,5) gives DECIMAL(12,7)
 
 DECIMAL(12,7)*DECIMAL(10,2) gives DECIMAL(22,9)
 
 DECIMAL(25,0)*DECIMAL(25,25) gives DECIMAL(45,25)
 

   For division, the precision of the result is the sum of the precisions of the operands. The precision is however never less than 15 and may not exceed 45. 


   The scale of the result is calculated as the precision of the result, less the number of positions to the left of the decimal point in the dividend, less the scale of the divisor. An error occurs if this calculation gives a negative value for the scale. 


   Thus:

 INTEGER(3)/DECIMAL(6,3) gives DECIMAL(15,9)
 
 DECIMAL(4,2)/DECIMAL(8,5) gives DECIMAL(15,8)
 
 DECIMAL(12,7)/DECIMAL(10,2) gives DECIMAL(22,15)
 
 DECIMAL(25,0)/DECIMAL(25,25) gives DECIMAL(45,0)
 
 DECIMAL(20,0)/DECIMAL(20,20) gives DECIMAL(40,0)
 
 DECIMAL(45,0)/DECIMAL(45,45) gives error (scale= -45)

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se