Numeric Functions¶

Numeric 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.

The numeric functions are:

ABS
ACOS
ASIN
ATAN
ATAN2
CEIL
COS
COT
DEGREES
DIV
EXP
FLOOR
LN
LOG
MAX
MIN
MOD
MULT
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUBTRACT
SUM
TAN
TRUNC

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. 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( <base:numeric>, <exponent:double> ):double

base. Required. Base number.
exponent. Required. Exponent to which the base number is raised.

Consider this:

The base can be a value of type decimal, if the exponent is a value of type integer.
If “base” and/or “exponent” are null, the function returns null.
If “base” is 0 and “exponent” is negative, the function returns “infinity”.
If “base” and “exponent” are 0, the function returns 1.
If “base” is 0 and “exponent” is positive, the function returns 0.
If “base” is negative:
- If “exponent” is an even integer, the result is equal to the result of raising the absolute value of “base” to “exponent”.
- If “exponent” is an odd integer, the result is the negative of the result of raising the absolute value of “base” to “exponent”.
- If “exponent” is not an integer, the result is “NaN”.

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

USER MANUALS

Numeric Functions¶

ABS¶

ACOS¶

ASIN¶

ATAN¶

ATAN2¶

CEIL¶

COS¶

COT¶

DEGREES¶

DIV¶

EXP¶

FLOOR¶

LN¶

LOG¶

MAX¶

MIN¶

MOD¶

MULT¶

PI¶

POWER¶

RADIANS¶

RAND¶

ROUND¶

SIGN¶

SIN¶

SQRT¶

SUBTRACT¶

SUM¶

TAN¶

TRUNC¶