8. Arithmetic Operators

This chapter describes the arithmetic operators that can be used in SQL statements in Altibase.

Arithmetic Operators

Arithmetic operators are used to sign, add, subtract, multiply, and divide numeric values. Some of these operators can also be used to perform arithmetic on date values. Arithmetic operations can only be performed on numeric data types or on data types that can be implicitly converted to numeric data types.

The Type of Arithmetic Operators

The following is a brief description of the kinds of arithmetic operators that are supported in Altibase

Operator	Description
"+" Unary operator	Explicitly specifies a positive number
"-" Unary operator	Reserves the sign of the input number
Four basic arthmetic operators	Perform respective operations on two input numbers (arguments) to yield a result
Concatenation operator	Joins two character strings

Unary Operator

Positive Sign

Syntax

+ number

Description

This operator is used to explicitly indicate that n is a positive number.

Negative Sign

Syntax

- number

Description

This operator is used to change the sign of n.

Binary Operators

Addition

Syntax

number1 + number2

Description

This operator adds n1 and n2 and outputs the result.

Subtraction

Syntax

number1 - number2

Description

This operator subtracts n2 from n1 and outputs the result.

Multiplication

Syntax

number1 x number2

Description

This operator multiplies n1 by n2 and outputs the result.

Division

Syntax

number1 / number2

Description

This operator divides n1 by n2 and outputs the result.

Performing Arithmetic on DATE Type Values

Syntax

When a number is added to or subtracted from a DATE type value, it is interpreted in units of days. Therefore, to add or subtract hours, minutes, or seconds from a DATE type value, the number of hours, minutes or seconds to be added or subtracted must be converted to days, as shown below:

date [ + | - ] n
date – date
date [ + | - ] days (plus/minus n day: n)
date [ + | - ] hours (plus/minus n hour: n/24) )
date [ + | - ] minutes (plus/minus n minute: n/(24*60) )
date [ + | - ] seconds (plus/minus n second: n/(24*60*60))

Note:

It is not possible, or meaningful, to perform multiplication or division on DATE type values.

Example

<Query> Display the name and the number of weeks worked for every employee in the department 4001:

SELECT e_firstname
     , e_lastname
     , (SYSDATE-join_date)/7 weeks_worked
  FROM employees
 WHERE dno = 4001;

E_FIRSTNAME           E_LASTNAME            WEEKS_WORKED
-----------------------------------------------------------------------
Xiong                 Wang                  115.778199044248
Curtis                Diaz                  87.6353419013905
John                  Huxley                224.492484758533
3 rows selected

<Query> Display the time 10 minutes in the future, that is, 10 minutes from the current time:

SELECT SYSDATE + (10/(24*60)) '10 MINUTES LATER'
  FROM DUAL;

10 MINUTES LATER 
-----------------------
2005/01/20 09:59:34 
1 row selected.

Concatenation Operator

Syntax

char1 || char2

Description

The concatenation operator is used to join two strings together.

Example

<Query> Put the text ' is a ' between the employee name and the position to create a sentence, and display the sentence in a single column.

SELECT RTRIM(e_firstname) || ' ' || RTRIM(e_lastname) || ' is a ' || emp_job || '.' job_description
  FROM employees;

JOB_DESCRIPTION
-------------------------------------------------------------------
.
.
.
Aaron Foster is a PL.
Farhad Ghorbani is a PL.
Ryu Momoi is a programmer.
Gottlieb Fleischer is a manager.
.
.
.
20 rows selected.

CAST Operator

Syntax

CAST (expr AS data_type)

Description

CAST converts expr to a value of the specified data_type. (All data types except BLOB and CLOB are supported.)

Example

<Query> Convert a string to a DOUBLE value.

SELECT CAST('3.14159265359' AS DOUBLE) pi
  FROM DUAL;

PI
------------------------- 
3.14159265359
1 row selected.