# 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))
```

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. Returns NULL if any of the arguments is NULL.

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

Note

In previous versions of Denodo, this function only returns NULL when all of the arguments are NULL. To restore the behavior of previous versions, execute this command from the VQL Shell:

```SET 'com.denodo.vdb.catalog.view.functions.max_min.ignoreNullValuesForComparisons'='true';
```

This change is applied immediately. You do not need to restart.

## MIN¶

Description

The `MIN` function returns the minimum value in a list of arguments. Returns NULL if any of the arguments is NULL.

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

Note

In previous versions of Denodo, the function `MIN` only returns NULL when all of the arguments are NULL. To restore this behavior, you can execute:

```SET 'com.denodo.vdb.catalog.view.functions.max_min.ignoreNullValuesForComparisons'='true'
```

## 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 precision `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

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)
```

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 subtract 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