OpenSQL Reference Guide > OpenSQL Reference Guide > Elements of OpenSQL Statements > Operations > Arithmetic Operations
Was this helpful?
Arithmetic Operations
An arithmetic operation combines two or more expressions using the arithmetic operators to form a resulting numeric expression.
Before performing any arithmetic operation, OpenSQL converts the participating expressions to identical data types. The result is returned as the selected data type. The following sections describe this data type conversion.
Default Type Conversion
When two numeric expressions are combined, the Enterprise Access product converts as necessary to make the data types of the expressions identical and assigns that same data type to the resulting expression. If it is necessary to convert the data type of an expression, the DBMS converts the expression having the data type of lower precedence to that of the higher.
The order of precedence among the numeric data types is, in highest‑to‑lowest order:
• Money
• Float
• Real
• Decimal
• Integer
• Smallint
For example, when OpenSQL operates on an integer and a floating-point number, the integer is converted to a floating-point number. If OpenSQL operates on two integers of different sizes, the smaller is converted to the size of the larger. All conversions are done before the operation is performed.
The following table summarizes the possible results of numeric combinations:
smallint
integer
decimal
real
float
money
smallint
integer
integer
decimal
real
float
money
integer
integer
integer
decimal
real
float
money
decimal
decimal
decimal
decimal
real
float
money
real
real
real
real
real
float
money
float
float
float
float
float
float
money
money
money
money
money
money
money
money
For example, for this expression:
(job.lowsal + 1000) * 12
the first operator (+) combines a float expression (job.lowsal) with a smallint constant (1000). The result is float. The second operator (*) combines the float expression with a smallint constant (12), resulting in a float expression.
For money data type, if the above table conflicts with Host DBMS default type conversion, Host DBMS default type conversion has higher priority.
Arithmetic Operations on Decimal Data Types
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 result can be determined, as shown in the following table:
Precision
Scale
Addition and subtraction
Larger number of fractional digits plus largest number of non‑fractional digits + 1 (to a maximum of 39)
Scale of operand having the largest scale
Multiplication
Total of precisions to a maximum of 39
Total of scales to a maximum of 39
Division
39
(39 precision of first operand) + (scale of first operand) (scale of second operand)
For example, in the following decimal addition operation:
1.234 + 567.89
the scale and precision of the result is calculated as follows:
Precision = 7
Calculated as 3 (larger number of fractional digits) + 3 (larger number of non‑fractional digits) + 1 = 7
Scale = 3
The first operand has the larger number of digits to the right of the decimal point
Result:
0569.124
Note:  If the result of arithmetic using decimal data exceeds the declared precision or scale of the column to which it is assigned, OpenSQL truncates the result and does not issue an error.
Last modified date: 08/28/2024