Mathematical

Operators

numeric_expr1 + numeric_expr2

Arithmetic addition of numeric_expr2 to numeric_expr1.

output type:

LONG when either of numeric_expr is INTEGER or LONG

DOUBLE when either of numeric_expr is FLOAT or DOUBLE

IPADDR when either of numeric_expr is IPADDR

TIMESTAMP when one of numeric_expr is TIMESTAMP (see also timestamp expressions)

TIMESTAMP_NANO when one of numeric_expr is TIMESTAMP_NANO

Example:

1
2
3
4
5
dual(2)
| select(i, int_add:i+1,
         d, double_add:d+1,
         ip, ipaddr_add:ip + 1,
         t, time_add: t+1000);
i int_add d double_add ip ipaddr_add t time_add
0 1 0.0 1.0 0.0.0.0 0.0.0.1 2019-09-18 09:25:00.687 +0000 2019-09-18 09:25:01.687 +0000
1 2 1.0 2.0 0.0.0.1 0.0.0.2 2019-09-18 09:25:00.688 +0000 2019-09-18 09:25:01.688 +0000

numeric_expr1 - numeric_expr2

Arithmetic subtraction of numeric_expr2 from numeric_expr1.

output type:

LONG when either of numeric_expr is INTEGER or LONG

DOUBLE when either of numeric_expr is FLOAT or DOUBLE

IPADDR when either of numeric_expr is IPADDR

TIMESTAMP when one of numeric_expr is TIMESTAMP (see also timestamp expressions)

TIMESTAMP_NANO when one of numeric_expr is TIMESTAMP_NANO

Example:

1
2
3
4
5
dual(2,2)
| select(i, int_sub:i-1,
         d, double_sub:d-1,
         ip, ipaddr_sub:ip - 1,
         t, time_sub: t-1000);
i int_sub d double_sub ip ipaddr_sub t time_sub
2 1 2.0 1.0 0.0.0.2 0.0.0.1 2019-09-19 07:17:39.034 +0000 2019-09-19 07:17:38.034 +0000
3 2 3.0 2.0 0.0.0.3 0.0.0.2 2019-09-19 07:17:39.035 +0000 2019-09-19 07:17:38.035 +0000

numeric_expr1 * numeric_expr2

Arithmetic multiplication of numeric_expr1 by numeric_expr2.

output type:

LONG when either of numeric_expr is INTEGER or LONG

DOUBLE when either of numeric_expr is FLOAT or DOUBLE

Example:

1
dual(2) | select(i, int_mult:i * 2, d, double_mult:d * 2);
i int_mult d double_mult
0 0 0.0 0.0
1 2 1.0 2.0

numeric_expr1 / numeric_expr2, DIVIDE(numeric_expr1, numeric_expr2)

Arithmetic division of numeric_expr1 by numeric_expr2.

output type:

LONG when either of numeric_expr is INTEGER or LONG

DOUBLE when either of numeric_expr is FLOAT or DOUBLE

Example:

1
dual(2,2) | select(i, int_div:i / 2, d, double_div:d / 2);
i int_div d double_div
2 1 2.0 1.0
3 1 3.0 1.5

numeric_expr1 % numeric_expr2, MODULO(numeric_expr1, numeric_expr2)

Computes remainder (modulo) of division numeric_expr1 by numeric_expr2.

output type:

LONG when either of numeric_expr is INTEGER or LONG

DOUBLE when either of numeric_expr is FLOAT or DOUBLE

Example:

1
dual(2,2) | select(i, int_mod:i % 2, d, double_mod:d % 2);
i int_mod d double_mod
2 0 2.0 0.0
3 1 3.0 1.0

ABS

ABS(numeric_expr)

Returns absolute value of numeric_expr. Returns NULL if numeric_expr evaluatess to NULL.

output type:DOUBLE

Example:

1
dual | select(ABS(-10));
abs
10

ADD

ADD(numeric_expr1, numeric_expr2)

Arithmetic addition of numeric_expr2 to numeric_expr1. Returns NULL if either of the arguments evaluates to NULL.

output type:

LONG when either of numeric_expr is INTEGER or LONG

DOUBLE when either of numeric_expr is FLOAT or DOUBLE

IPADDR when either of numeric_expr is IPADDR

TIMESTAMP when one of numeric_expr is TIMESTAMP (see also timestamp expressions)

TIMESTAMP_NANO when one of numeric_expr is TIMESTAMP_NANO

Example:

1
2
3
4
5
6
dual(2)
| select(i, int_add:ADD(i, 1),
         d, double_add:ADD(d, 1),
         ip, ipaddr_add:ADD(ip, 1),
         t, time_add:ADD(t, 1000)
        );
i int_add d double_add ip ipaddr_add t time_add
0 1 0.0 1.0 0.0.0.0 0.0.0.1 2019-09-19 07:28:24.073 +0000 2019-09-19 07:28:25.073 +0000
1 2 1.0 2.0 0.0.0.1 0.0.0.2 2019-09-19 07:28:24.074 +0000 2019-09-19 07:28:25.074 +0000

ACOS

ACOS(numeric_expr)

Computes arc cosine of numeric expr. The returned angle is in the range 0.0 through pi.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(ACOS(0.5));
acos
1.0471975511965979

ASIN

ASIN(numeric_expr)

Computes arc sine of numeric_expr. The returned angle is in the range -pi/2 through pi/2.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(ASIN(0.5));
asin
0.5235987755982989

ATAN

ATAN(numeric_expr)

Computes arc tangent of numeric_expr. The returned angle is in the range -p/2 through pi/2.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(ATAN(0.5));
atan
0.4636476090008061

ATAN2

ATAN2(numeric_expr1, numeric_expr2)

Computes the angle theta from the conversion of rectangular coordinates x,y to polar coordinates(r, theta).

Returns NULL if either of the arguments evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(ATAN2(0.5, 0.5));
atan2
0.7853981633974483

CBRT

CBRT(numeric_expr)

Computes the cube root of numeric_expr.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(CBRT(27));
cbrt
3.0

CEIL

CEIL(numeric_expr)

Computes the smallest (closest to negative infinity) double value that is greater than or equal to the numeric_expr and is equal to a mathematical integer.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(CEIL(0.7));
ceil
1.0

COS

COS(numeric_expr)

Computes the trigonometric cosine of an angle numeric_expr (in radians).

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(COS(0.5));
cos
0.8775825618903728

COSH

COSH(numeric_expr)

Computes the hyperbolic cosine of an angle numeric_expr.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(COSH(0.5));
cosh
1.1276259652063807

DEGREES

DEGREES(numeric_expr)

Converts numeric expr angle (of radians) to an approximately equivalent angle of degrees.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(DEGREES(1.571));
degrees
90.01166961505233

DIVIDE

DIVIDE(numeric_expr1, numeric_expr2)

Arithmetic division of numeric_expr1 by numeric_expr2. Returns NULL if any of the arguments evaluates to NULL.

output type:

LONG when either of numeric_expr is INTEGER or LONG

DOUBLE when either of numeric_expr is FLOAT or DOUBLE

Example:

1
dual(2,2) | select(i, int_div:DIVIDE(i, 2), d, double_div:DIVIDE(d, 2));
i int_div d double_div
2 1 2.0 1.0
3 1 3.0 1.5

E

E()

Returns Euler’s number.

output type:DOUBLE

Example:

1
dual | select(E());
e
2.718281828459045

EXP

EXP(numeric_expr)

Computes Euler’s number e raised to the power of numeric_expr.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(EXP(2));
exp
7.38905609893065

EXPM1

EXPM1(numeric_expr)

Returns e numeric_expr -1

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(EXPM1(2));
expm1
6.38905609893065

FLOOR

FLOOR(numeric_expr)

Computes the largest (closest to positive infinity) DOUBLE value, less than or equal to the numeric expr and is equal to a mathematical integer.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(FLOOR(2.71));
floor
2.0

FLOORDIV

FLOORDIV(numeric_expr1, numeric_expr2)

Computes mathematical integer floor of numeric_expr1 argument division by numeric_expr2.

Returns NULL if either of the arguments evaluates to NULL.

output type:

INTEGER when both arguments are INTEGER

LONG when any of the arguments are LONG or FLOAT or DOUBLE

Example:

1
dual | select(FLOORDIV(5, 2.0));
floordiv
2

FLOORMOD

FLOORMOD(numeric_expr1, numeric_expr2)

Computes mathematical integer floor modulus of numeric_exp1 and numeric_expr2.

Returns NULL if either of the arguments evaluates to NULL.

output type:

INTEGER when both arguments are INTEGER

LONG when any of the arguments are LONG or FLOAT or DOUBLE

Example:

1
dual | select(FLOORMOD(5, 2.71));
floormod
1

GETEXPONENT

GETEXPONENT(numeric_expr)

Computes mathematical integer unbiased exponent used in the representation of numeric_expr argument.

Returns NULL if numeric_expr evaluates to NULL.

output type:INTEGER

Example:

1
dual | select(GETEXPONENT(28.71d));
getexponent
4

HYPOT

HYPOT(numeric_expr1, numeric_expr2)

Returns sqrt(x 2 +y2).

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(HYPOT(3.0, 4))
hypot
5.0

IEEEREMAINDER

IEEEREMAINDER(numeric_expr1, numeric_expr2)

Returns DOUBLE value of the remainder operation on numeric arguments as prescribed by the IEEE 754 standard.

Returns NULL if either of the arguments evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(IEEEREMAINDER(7.389f, 5));
ieeeremainder
2.3889999389648438

LOG

LOG(numeric_expr)

Computes the natural logarithm (base e) of numeric_expr.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(LOG(7.389));
log
1.9999924078065106

LOG1P

LOG1P(numeric_expr)

Computes the natural logarithm (base e) of the sum of the numeric_expr and 1.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(LOG1P(7.389));
log1p
2.1269213238641576

LOG10

LOG10(numeric_expr)

Computes base 10 logarithm of numeric_expr.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(LOG10(7.389));
log10
0.8685856665587657

MODULO

MODULO(numeric_expr1, numeric_expr2)

Computes remainder (modulo) of division numeric_expr1 by numeric_expr2.

Returns NULL if either of the arguments evaluates to NULL.

output type:

LONG when either of numeric_expr is INTEGER or LONG

DOUBLE when either of numeric_expr is FLOAT or DOUBLE

Example:

1
dual(2,2) | select(i, int_mod:MODULO(i, 2), d, double_mod:MODULO(d, 2));
i int_mod d double_mod
2 0 2.0 0.0
3 1 3.0 1.0

MULTIPLY

MULTIPLY(numeric_expr1, numeric_expr2)

Arithmetic multiplication of numeric_expr1 by numeric_expr2.

Returns NULL if either of the arguments evaluates to NULL.

output type:

LONG when either of numeric_expr is INTEGER or LONG

DOUBLE when either of numeric_expr is FLOAT or DOUBLE

Example:

1
dual(2) | select(i, int_mult:MULTIPLY(i, 2), d, double_mult:MULTIPLY(d, 2));
i int_mult d double_mult
0 0 0.0 0.0
1 2 1.0 2.0

NEXTAFTER

NEXTAFTER(numeric_expr1, numeric_expr2)

Returns number adjacent to the numeric_expr1 in the direction of the numeric_expr2.

Returns NULL if either of the arguments evaluates to NULL.

output type:FLOAT if numeric_expr1 is FLOAT, else DOUBLE

Example:

1
dual | select(NEXTAFTER(3.33f, 2));
nextafter
3.3299997

NEXTDOWN

NEXTDOWN(numeric_expr)

Returns the number adjacent to numeric_expr argument in the direction of negative infinity.

Returns NULL if numeric_expr evaluates to NULL.

output type:FLOAT if numeric_expr is FLOAT, else DOUBLE

Example:

1
dual | select(NEXTDOWN(3.33));
nextdown
3.3299999999999996

NEXTUP

NEXTUP(numeric_expr)

Returns the number adjacent to numeric_expr argument in the direction of positive infinity.

Returns NULL if numeric_expr evaluates to NULL.

output type:FLOAT if numeric_expr is FLOAT, else DOUBLE

Example:

1
dual | select(NEXTUP(3.33));
nextup
3.3300000000000005

PI

PI()

Returns the value of pi.

output type:DOUBLE

Example:

1
dual | select(PI());
pi
3.141592653589793

POWER

POWER(numeric_expr1, numeric_expr2)

Computes the value of numeric_expr1 raised to the power of numeric_expr2.

Returns NULL if either of the arguments evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(POWER(2, 3.5));
power
11.313708498984761

RADIANS

RADIANS(numeric_expr)

Converts numeric_expr angle of degrees to an approximately equivalent angle of radians.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(RADIANS(90));
radians
1.5707963267948966

RANDOM

RANDOM()

Returns random positive double value greater than or equal to 0.0 and less than 1.0.

output type:DOUBLE

Example:

1
dual | select(RANDOM());
random
0.752088503910963

RINT

RINT(numeric_expr)

Returns the value that is closest in value to the numeric_expr argument and is equal to a mathematical integer.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(RINT(12.396));
rint
12.0

ROUND

ROUND(numeric_expr)

Computes closest mathematical integer to numeric_expr with ties rounding up.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(ROUND(12.396));
round
12.0

ROUND(numeric_expr, decimalplaces)

Computes closest mathematical integer to numeric_expr with decimalplaces number of decimals.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(ROUND(12.436,2));
round
12.44

SCALB

SCALB(numeric_expr1, scale_factor)

Returns FLOAT f×2scale_factor

Returns NULL if either of the arguments evaluates to NULL.

output type:FLOAT if numeric_expr is FLOAT, else DOUBLE

Example:

1
dual | select(SCALB(0.524f, 6));
scalb
33.536

SIGNUM

SIGNUM(numeric_expr)

Returns result of signum function of argument:

  • 0 if numeric_expr is 0,
  • 1.0 if numeric_expr is greater than 0
  • -1.0 if numeric_expr is less than 0

Returns NULL if numeric_expr evaluates to NULL.

output type:FLOAT if numeric_expr is FLOAT, else DOUBLE

Example:

1
dual | select(SIGNUM(28.71));
signum
1.0

SIN

SIN(numeric_expr)

Computes the trigonometric sine of angle numeric_expr (in radians).

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(SIN(0.524));
sin
0.5003474302699141

SINH

SINH(numeric_expr)

Computes the hyperbolic sine of numeric_expr.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(SINH(0.524));
sinh
0.5483110094354913

SQRT

SQRT(numeric_expr)

Computes the positive square root of numeric_expr.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(SQRT(9.781));
sqrt
3.127459032505462

SUBMOD

SUBMOD(numeric_expr1, numeric_expr2)

Subtracts numeric_expr1 modulo numeric_expr2 from numeric_expr1.

Returns NULL if either of the arguments evaluates to NULL.

output type:the type of numeric_expr1

Example:

1
dual | select(SUBMOD(9.2, 4));
submod
8.0

SUBTRACT

SUBTRACT(numeric_expr1, numeric_expr2)

Arithmetic subtraction of numeric_expr2 from numeric_expr1.

output type:

LONG when either of numeric_expr is INTEGER or LONG

DOUBLE when either of numeric_expr is FLOAT or DOUBLE

IPADDR when either of numeric_expr is IPADDR

TIMESTAMP when one of numeric_expr is TIMESTAMP (see also timestamp expressions)

TIMESTAMP_NANO when one of numeric_expr is TIMESTAMP_NANO

Example:

1
2
3
4
5
dual(2,2)
| select(i, int_sub:SUBTRACT(i, 1),
         d, double_sub:SUBTRACT(d,1),
         ip, ipaddr_sub:SUBTRACT(ip, 1),
         t, time_sub:SUBTRACT(t, 1000));
i int_sub d double_sub ip ipaddr_sub t time_sub
2 1 2.0 1.0 0.0.0.2 0.0.0.1 2019-09-19 07:17:39.034 +0000 2019-09-19 07:17:38.034 +0000
3 2 3.0 2.0 0.0.0.3 0.0.0.2 2019-09-19 07:17:39.035 +0000 2019-09-19 07:17:38.035 +0000

TAN

TAN(numeric_expr)

Computes the trigonometric tangent of angle numeric_expr (in radians).

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(TAN(1.524));
tan
21.353597524589244

TANH

TANH(numeric_expr)

Computes the hyperbolic tangent of numeric_expr.

Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example:

1
dual | select(TANH(1.524));
tanh
0.9093922044597188

TOHEXSTRING

TOHEXSTRING (numeric_expr)

Converts INTEGER or LONG numeric_expr to hexadecimal string.

Returns NULL if numeric_expr evaluates to NULL.

output type:STRING

Example:

1
dual | select(TOHEXSTRING(1000));
tohexstring
3e8

ULP

ULP(numeric_expr)

Returns size of an ulp of the numeric_expr argument.

Returns NULL if numeric_expr evaluates to NULL.

output type:FLOAT if numeric_expr is FLOAT, else DOUBLE

Example:

1
dual | select(ULP(1.524f));
ulp
1.1920929E-7