Mimer SQL Reference Manual TOC PREV NEXT INDEX

Mimer Developer Site

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.

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:

 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)
 
 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)
 
 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
Mimer SQL Reference Manual TOC PREV NEXT INDEX