Arithmetic Operators

Binary arithmetic operators can combine expressions that return numbers.


Arithmetic Operation	Arithmetic Operator	Operator Function		Arithmetic Operation	Arithmetic Operator	Operator Function
Addition	+	plus( )		Multiplication	*	times( )
Subtraction	–	minus( )		Division	/	divide( )

The following examples use binary arithmetic operators:

quantity * total_price 
price * 2 
COUNT(*) + 2

If you combine a DATETIME value with one or more INTERVAL values, all the fields of the INTERVAL value must be present in the DATETIME value; no implicit EXTEND function is performed. In addition, you cannot use YEAR to MONTH intervals with DAY to SECOND intervals. For additional information about binary arithmetic operators, see the HCL OneDB™ Guide to SQL: Reference.

The binary arithmetic operators have associated operator functions, as the preceding table shows. Connecting two expressions with a binary operator is equivalent to invoking the associated operator function on the expressions. For example, the following two statements both select the product of the total_price column and 2. In the first statement, the * operator implicitly invokes the times( ) function.

SELECT (total_price * 2) FROM items 
   WHERE order_num = 1001;
SELECT times(total_price, 2) FROM items
   WHERE order_num = 1001;

You cannot use arithmetic operators to combine expressions that use aggregate functions with column expressions.

The database server provides the operator functions associated with the relational operators for all built-in data types. You can define new versions of these operator functions to handle your own user-defined data types.

For more information, see HCL OneDB User-Defined Routines and Data Types Developer's Guide.

The database server also supports the following unary arithmetic operators.


Sign of Number	Unary Arithmetic Operator	Operator Function
Positive	+	positive( )
Negative	–	negate( )

The unary arithmetic operators have the associated operator functions that the preceding table shows. You can define new versions of these functions to handle your own user-defined data types. For more information on this topic, see HCL OneDB User-Defined Routines and Data Types Developer's Guide.

If any value that participates in an arithmetic expression is NULL, the value of the entire expression is NULL, as the following example shows:

SELECT order_num, ship_charge/ship_weight FROM orders
   WHERE order_num = 1023;

If either ship_charge or ship_weight is NULL, the value returned for the expression ship_charge/ship_weight is also NULL. If the NULL expression ship_charge/ship_weight is used in a condition, its truth value cannot be TRUE, and the condition is not satisfied (unless the NULL expression is an operand of the IS NULL operator).