Arithmetic Functions

Arithmetic functions are applied to values of the types decimal, int, long, float and double.

In general, if a function accepts numeric arguments of different types, the type of the result will be the most generic type. For instance, the addition of an int value and a double value returns a double value.

Arithmetic functions supported by Virtual DataPort

ABS

Description

The ABS function returns the absolute value of a number.

Syntax

ABS( <value:numeric> ):numeric
  • value. Required. The number of which absolute value will be calculated.

Example

SELECT ABS(-5) as absolute_value
FROM Dual()
absolute_value
5

ACOS

Description

The ACOS function returns the arc cosine of an angle.

The input has to be a number between -1.0 and +1.0.

Syntax

ACOS( <value:numeric> ):double
  • value. Required. It cannot be a value of type decimal.

Example

SELECT ACOS(0), ACOS(-1), ACOS(1)
acos acos_1 acos_2
1.57 3.14 0.0

ASIN

Description

The ASIN function returns the arc sine of an angle.

The input has to be a number between -1.0 and +1.0.

Syntax

ASIN( <value:numeric> ):double
  • value. Required. It cannot be a value of type decimal.

Example

SELECT ASIN(0), ASIN(1), ASIN(-1)
asin asin_1 asin_2
0.0 1.57 -1.57

ATAN

Description

The ATAN function returns the arc tangent of an angle.

The input has to be a number between -1.0 and +1.0.

Syntax

ATAN( <value:numeric> ):double
  • value. Required. It cannot be a value of type decimal.

Example

SELECT RADIANS(90), ATAN(RADIANS(-90)), ATAN(RADIANS(90))
radians atan atan_1
1.57 -1.0 1.0

Note

The function RADIANS converts an angle in degrees to radians. See more about this function in the section RADIANS.

ATAN2

Description

The ATAN2 function converts rectangular coordinates (x, y) to polar (r, theta). It computes the phase theta by computing an arc tangent of y/x in the range of -pi to +pi.

Syntax

ATAN2( <x:numeric>, <y:numeric>):double
  • x. Required.
  • y. Required

Neither x nor y can be values of type decimal.

CEIL

Description

The CEIL function returns the smallest integer not less that the argument.

Syntax

CEIL( <value:decimal> ):long

CEIL( <value:int> ):int

CEIL( <value:long> ):long
  • value. Required. The value to round off.

Example

SELECT CEIL(5.08) as ceil_value
FROM Dual()
ceil_value
6

COS

Description

The COS function returns the cosine of an angle in radians.

The output is a double value between -1.0 and +1.0.

Syntax

COS( <angle:numeric> ):double
  • angle. Required. It cannot be a value of type decimal.

Example

SELECT COS(0), COS(RADIANS(180))
cos cos_1
1.0 -1.0

COT

Description

The COT function returns the cotangent of an angle in radians.

Syntax

COT( <angle:numeric> ):double
  • angle. Required.

Example

SELECT COT( RADIANS (45))
cot
1.0

DEGREES

Description

The DEGREES function, given an angle in radians, returns the corresponding angle in degrees.

Syntax

DEGREES( <angle:numeric> ):double
  • angle. Required. It cannot be a value of type decimal.

Example

SELECT DEGREES(0), DEGREES(3.15 * 2)
degrees degrees_1
0.0 360.96

DIV

Description

The DIV function divides two numbers.

Syntax

DIV( <dividend:numeric>, <divisor:numeric> ):numeric
  • dividend. Required. The dividend of the operation.
  • divisor. Required. The divisor of the operation.

Examples

Example 1

SELECT DIV(10, 2.5) as div_value
FROM Dual();
div_value
4.0

Example 2

SELECT (10 / CAST('double', 2)) as div_value
FROM Dual();
div_value
5.0

EXP

Description

The EXP function returns the exponential value of a number.

Syntax

EXP( <value:numeric> ):double
  • value. Required.

Example

SELECT EXP(0), EXP(1)
exp exp_1
1.0 2.72

FLOOR

Description

The FLOOR function returns the largest integer not greater than the argument.

Syntax

FLOOR( <value:decimal> ):long

FLOOR( <value:int> ):int

FLOOR( <value:long> ):long
  • value. Required. Value to round off.

Example

SELECT FLOOR(5.98) as floor_value
FROM Dual();
floor_value
5

LN

Description

The LN function returns the natural logarithm (base e) of a value.

Syntax

LN( <value:numeric> ):double
  • value. Required. It cannot be a value of type decimal.

Example

SELECT LN( EXP( 0 ) ), LN ( EXP(1) )
FROM Dual();
ln ln_1
0.0 1.0

LOG

Description

The LOG function returns the logarithm of a number.

Syntax

LOG( <value:numeric> [, <base:numeric> ]):double
  • value. Required. Positive real number for which you want the logarithm. It cannot be a value of type decimal.
  • base. Optional. If not present, the function returns the logarithm of the number in base-ten.

Example

SELECT log(100), log(100, 10);
FROM Dual();
log log_1
2.0 2.0

MAX

Description

The MAX function returns the maximum value in a list of arguments.

Syntax

MAX( <value 1:numeric>, <value 2:numeric> [, <value N:numeric> ]* ):numeric
  • value 1. Required.
  • value 2. Required.
  • value N. Optional. One or more values.

Example

SELECT MAX(5, 10, 3.2) as max_value
FROM Dual();
max_value
10.0

MIN

Description

The MIN function returns the minimum value in a list of arguments.

Syntax

MIN( <value 1:numeric> [, <value N:numeric> ]* ):numeric
  • value 1. Required.
  • value N. Optional. One or more values.

Example

SELECT MIN(5, 10, 3.2) as min_value
FROM Dual();
min_value
3.2

MOD

Description

The MOD function returns the result of the module operation: the remainder of the integer division of the first and second arguments.

This function has an infix version and its operator is %.

Syntax

MOD( <dividend:decimal>, <divisor:decimal> ):decimal

MOD( <dividend:double>, <divisor:double> ):double

MOD( <dividend:float>, <divisor:float> ):double

MOD( <dividend:int>, <divisor:int> ):int

MOD( <dividend:long>, <divisor:int> ):int

MOD( <dividend:long>, <divisor:long> ):long
  • dividend. Required.
  • divisor. Required.

Examples

Consider the following view V:

int_sample long_sample
1 10
-4 -55
8 70

And the view modView created with the command:

Example 1
CREATE VIEW mod_view AS
    SELECT int_sample
        , MOD(int_sample, 2) AS s1
        , long_sample
        , MOD(long_sample, 2) as s2
FROM V;
SELECT *
FROM mod_view
int_sample s1 long_sample s2
1 1 10 0
-4 0 -55 -1
8 0 70 0

Example 2

SELECT 10%2
FROM mod_view
mod
0
0
0

MULT

Description

The MULT function multiplies its arguments.

Syntax

MULT ( <value 1:numeric>, <value 2:numeric> [, <value N:numeric> ]* ):numeric
  • value 1. Required. First number to be multiplied.
  • value 2. Required. Second number to be multiplied.
  • value N. Optional. One or more arguments to be multiplied.

Examples

Example 1

SELECT MULT(10, 2.5) as mult_value
FROM Dual();
mult_value
25.0

Example 2

SELECT (10 * 2.5) as mult_value
FROM Dual();
mult_value
25.0

PI

Description

The PI function returns the Pi number with precission double.

Syntax

PI():double

Example

SELECT PI() as pi_constant
FROM Dual();
pi_constant
3.141592653589793

POWER

Description

The POWER function returns the result of a number raised to a power.

Syntax

POWER( <number:numeric>, <power:numeric> ):double
  • number. Required. Base number.
  • power. Required. Exponent to which the base number is raised.

Neither number nor power can be values of type decimal.

Example

SELECT POWER(5, 2) as power_value
FROM Dual();
power_value
25

RADIANS

Description

The RADIANS function, given an angle in degrees, returns the corresponding angle in radians.

Syntax

RADIANS( <angle:numeric> ):double
  • angle. Required. It cannot be a value of type decimal.

Example

SELECT RADIANS(0), RADIANS(360)
radians radians_1
0.0 6.28

RAND

Description

The RAND function returns a random value between zero and one.

Syntax

It does not receive any parameter.

Example

Consider the view V:

int_sample
1
-4
8
SELECT int_sample, int_sample * RAND() AS random
FROM V
int_sample random
-4 -3.551409143605859
1 0.6443357973998833
8 1.5061178485934867

ROUND

Description

The ROUND function returns a number rounded to the specified number of places to the right or left of the decimal place.

Syntax

ROUND( <value:numeric [, n : integer ] ):numeric
  • value. Required. Value to round off.
  • n. Optional.

If n is omitted, value is rounded to 0 places. If the argument has int type, it returns an int value. If the argument has long type, float or double, it returns a long value.

If n is negative, value is rounded to digits left of the decimal point. In this case, the function will return a value of the same type as the value parameter. I.e., if the type of value is int, it returns an int value, if it is float, it returns a float value, etc.

Example

SELECT ROUND(5.98), ROUND(7.08733, 2), ROUND(315.28, -2)
FROM Dual();
round round_1 round_2
6 7.09 300.0

SIGN

Description

The SIGN function returns -1, 0 or 1, depending on whether the value is negative, zero, or positive respectively. Use this function to know the sign of a number.

Syntax

SIGN( <value:numeric> ):int
  • value. Required. If NULL, the function returns NULL.

Example

SELECT SIGN(100), SIGN(-50)
sign sign_1
1 -1

SIN

Description

The SIN function returns the sine of an angle in radians.

The output is a double value between -1.0 and +1.0.

Syntax

SIN( <angle:numeric> ):double
  • angle. Required. It cannot be a value of type decimal.

Example

SELECT SIN(0), SIN(RADIANS(90))
sin sin_1
0.0 1.0

SQRT

Description

The SQRT function returns a positive square root.

Syntax

SQRT( <value:numeric> ):double
  • value. Required. Number for which you want the square root. It cannot be a value of type decimal.

Example

SELECT SQRT(25) as sqrt_value
FROM Dual();
sqrt_value
5.0

SUBTRACT

Description

The SUBTRACT function subtracts two numbers or two datetime typed values.

If you substract two values of type localdate, timestamp, timestamptz or date, you get the number of whole days between the second date and the first date. If both dates belong to the same day, the function returns 0 even if the time is different. When subtracting two values of type time, the function will return the number of milliseconds between the two values.

This function also provides an infix notation. I.e. subtract(a, b) is equivalent to a - b.

Syntax

SUBTRACT( <value 1:numeric>, <value 2:numeric> ):numeric

SUBTRACT( <value 1:localdate>, <value 2:localdate> ):long

SUBTRACT( <value 1:timestamp>, <value 2:timestamp> ):long

SUBTRACT( <value 1:timestamptz>, <value 2:timestamptz> ):long

SUBTRACT( <value 1:time>, <value 2:time> ):long

SUBTRACT( <value 1:date>, <value 2:date> ):long
  • value 1. Required. First value to be subtracted from.
  • value 2. Required. Second value to be subtracted.

Examples

Example 1

SELECT SUBTRACT(10, 2.5) as subtract_value
FROM Dual();
subtract_value
7.5

Example 2

SELECT (10 - CAST('int', 2.5)) as subtract_value
FROM Dual();
subtract_value
8

Example 3

SELECT
    SUBTRACT (
          DATE '2015-01-02'
        , DATE '2015-01-01') as value_1
    , SUBTRACT (
          TIMESTAMP '2015-01-01 01:00'
        , TIMESTAMP '2015-01-01 08:00') as value_2
value_1 value_2
1 0

SUM

Description

The SUM function adds its arguments.

Syntax

SUM( <value1:numeric, value2:numeric [, valueN:numeric ]* ):numeric
  • value1. Required. First number to be added.
  • value2. Required. Second number to be added.
  • valueN. Optional. One or more arguments to be added.

Examples

Example 1

SELECT SUM(1, CAST('double', 2.5), 4.6) as sum_value
FROM Dual();
sum_value
8.1

Example 2

SELECT (1 + CAST('int', 2.9) + 4.6) as sum_value
FROM Dual();
sum_value
7.6

TAN

The TAN function returns the tangent of an angle in radians.

Syntax

TAN ( <angle:numeric> ):double
  • angle. Required.

Example

SELECT TAN(0), TAN(radians(45))
tan tan_1
0.0 1.0

TRUNC

The TRUNC function returns the integer part of a number.

Syntax

TRUNC( <value:numeric> ):long
  • value. Required.

Example

SELECT TRUNC(1), TRUNC(2.8), TRUNC(-3.9)
trunc trunc_1 trunc_2
1 2 -3