Numeric Operations#
ABS#
Syntax
ABS(<expr>)
Description
Returns the absolute value of a numeric expression.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 0.1 |
| -0.1 |
| NULL |
Applied formula:
ABS(VALUE)
Output:
| VALUE_ABS |
|---|
| 0.1 |
| 0.1 |
| NULL |
SQL Editor Example
select column1, abs(column1)
from (values (0), (1), (-2), (3.5), (-4.5), (null));
| COLUMN1 | ABS(COLUMN1) |
|---|---|
| 0.0 | 0.0 |
| 1.0 | 1.0 |
| -2.0 | 2.0 |
| 3.5 | 3.5 |
| -4.5 | 4.5 |
| NULL | NULL |
ACOS#
Syntax
ACOS(<expr>)
Description
Computes the inverse cosine (arc cosine) of its input. The result is a number in the interval [0, pi].
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 0.0 |
| 0.5 |
| 1.0 |
Applied formula:
ACOS(VALUE)
Output:
| VALUE_ACOS |
|---|
| 1.570796327 |
| 1.047197551 |
| 0.0 |
SQL Editor Example
select acos(0), acos(0.5), acos(1);
| ACOS(0) | ACOS(0.5) | ACOS(1) |
|---|---|---|
| 1.570796327 | 1.047197551 | 0 |
ASIN#
Syntax
ASIN(<expr>)
Description
Computes the inverse sine (arc sine) of its argument. Returns the arc sine in radians (not degrees) in the range [-pi/2, pi/2].
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 0.0 |
| 0.5 |
| 1.0 |
Applied formula:
ASIN(VALUE)
Output:
| VALUE_ASIN |
|---|
| 0.0 |
| 0.5235987756 |
| 1.570796327 |
SQL Editor Example
select asin(0), asin(0.5), asin(1);
| ASIN(0) | ASIN(0.5) | ASIN(1) |
|---|---|---|
| 0 | 0.5235987756 | 1.570796327 |
ATAN#
Syntax
ATAN(<expr>)
Description
Computes the inverse tangent (arc tangent) of its argument.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 1.0 |
Applied formula:
ATAN(VALUE)
Output:
| VALUE_ATAN |
|---|
| 0.7853981634 |
SQL Editor Example
select atan(1);
| ATAN(1) |
|---|
| 0.7853981634 |
CBRT#
Syntax
CBRT(<expr>)
Description
Returns the cubic root of a numeric expression.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 0.0 |
| 2.0 |
| -10.0 |
Applied formula:
CBRT(VALUE)
Output:
| VALUE_CBRT |
|---|
| 0.0 |
| 1.25992105 |
| -2.15443469 |
SQL Editor Example
select x, cbrt(x) from tab;
| x | cbrt(x) |
|---|---|
| 0 | 0 |
| 2 | 1.25992105 |
| -10 | -2.15443469 |
| [NULL] | [NULL] |
CEIL#
Syntax
CEIL(<expr>)
Description
Returns values from the expression rounded to the nearest equal or larger integer, or to the nearest equal or larger value with the specified number of places after the decimal point.
- If the input scale was greater than or equal to zero, then the output scale generally matches the input scale.
- If the input scale was negative, then the output scale is 0.
- Example: The data type returned by CEIL(3.14, 1) is NUMBER(4, 1).
- If the scale is zero, then the value is effectively an integer.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 0.0 |
| 742.46 |
| -485.69 |
Applied formula:
CEIL(VALUE)
Output:
| VALUE_CEIL |
|---|
| 0.0 |
| 743.0 |
| -485.0 |
SQL Editor Example
Create and fill a table:
create transient table test_ceiling (n float, scale integer);
insert into test_ceiling (n, scale) values
(-975.975, -1),
(-975.975, 0),
(-975.975, 2),
( 135.135, -2),
( 135.135, 0),
( 135.135, 1),
( 135.135, 3),
( 135.135, 50),
( 135.135, null)
;
select n, scale, ceil(n, scale)
from test_ceiling
order by n, scale;
| N | SCALE | CEIL(N, SCALE) |
|---|---|---|
| -975.975 | -1 | -970 |
| -975.975 | 0 | -975 |
| -975.975 | 2 | -975.97 |
| 135.135 | -2 | 200 |
| 135.135 | 0 | 136 |
| 135.135 | 1 | 135.2 |
| 135.135 | 3 | 135.135 |
| 135.135 | 50 | 135.135 |
| 135.135 | NULL | NULL |
COS#
Syntax
COS(<expr>)
Description
Computes the cosine of its argument. The value should be in radians.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 0.0 |
| 2.0 |
| 1.0 |
Applied formula:
COS(VALUE)
Output:
| VALUE_COS |
|---|
| 1.0 |
| -0.4161468365471424 |
| 0.5403023058681398 |
SQL Editor Example
select cos(0), cos(pi()/3), cos(radians(90));
| COS(0) | COS(PI()/3) | COS(RADIANS(90)) |
|---|---|---|
| 1 | 0.5 | 6.123233996e-17 |
COT#
Syntax
COT(<expr>)
Description
Computes the cotangent of its argument.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 0.0 |
| 2.0 |
| 1.0 |
Applied formula:
COT(VALUE)
Output:
| VALUE_COT |
|---|
| Infinity |
| -0.45765755436028577 |
| 0.6420926159343306 |
SQL Editor Example
select cot(0), cot(pi()/3), cot(radians(90));
| COT(0) | COT(PI()/3) | COT(RADIANS(90)) |
|---|---|---|
| inf | 0.5773502692 | 6.123233996e-17 |
DEGREES#
Syntax
DEGREES(<expr>)
Description
Converts radians to degrees.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 0.0 |
| 10.0 |
| 20.0 |
Applied formula:
DEGREES(VALUE)
Output:
| VALUE_DEGREES |
|---|
| 0.0 |
| 572.9577951308232 |
| 1145.9155902616465 |
SQL Editor Example
Show the number of degrees for 1/3 of a radian, 1 radian, and 3 radians:
select degrees(pi()/3), degrees(pi()), degrees(3 * pi()), degrees(1);
| DEGREES(PI()/3) | DEGREES(PI()) | DEGREES(3 * PI()) | DEGREES(1) |
|---|---|---|---|
| 60 | 180 | 540 | 57.295779513 |
DIVO#
Syntax
DIV0(<dividend>, <divisor>)
Description
Performs division like the division operator (/). Returns 0 when the divisor is 0 rather than reporting an error. Returns the quotient.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 3.0 |
| 10.0 |
| 20.0 |
Applied formula:
DIVO(VALUE)
Output:
| VALUE_DIVO |
|---|
| 1.5 |
| 5.0 |
| 10.0 |
SQL Editor Example
select 1/2;
| 1/2 |
|---|
| 0.500000 |
select div0(1, 2);
| DIV0(1, 2) |
|---|
| 0.500000 |
select div0(1, 0);
| DIV0(1, 0) |
|---|
| 0.000000 |
EXP#
Syntax
EXP(<expr>)
Description
Computes Euler’s number e (approximately 2.718281) raised to a floating-point value.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 1.0 |
| 2.0 |
| 0.0 |
Applied formula:
EXP(VALUE)
Output:
| VALUE_EXP |
|---|
| 2.718281828459045 |
| 7.38905609893065 |
| 1.0 |
SQL Editor Example
select exp(1), exp(ln(10));
| EXP(1) | EXP(LN(10)) |
|---|---|
| 2.718281828 | 10 |
FLOOR#
Syntax
FLOOR(<expr>)
Description
Returns values from the expression rounded to the nearest equal or smaller integer.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 9957.56 |
| 742.46 |
| -485.69 |
Applied formula:
FLOOR(VALUE)
Output:
| VALUE_FLOOR |
|---|
| 9957.0 |
| 742.0 |
| -486.0 |
SQL Editor Example
select floor(135.135), floor(-975.975);
| FLOOR(135.135) | FLOOR(-975.975) |
|---|---|
| 135 | -976 |
LN#
Syntax
LN(<expr>)
Description
Returns the natural logarithm of a numeric expression. Returns a floating point number, even if the input expression is of type integer. If the input expression is less than or equal to 0, an error is returned.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 1.0 |
| 10.0 |
| 100.0 |
| NULL |
Applied formula:
LN(VALUE)
Output:
| VALUE_LN |
|---|
| 0.0 |
| 2.302585093 |
| 4.605170186 |
| NULL |
SQL Editor Example
select x, ln(x) from tab;
| X | LN(X) |
|---|---|
| 1 | 0 |
| 10 | 2.302585093 |
| 100 | 4.605170186 |
| [NULL] | [NULL] |
LOG#
Syntax
LOG(<base>, <expr>)
Description
Returns the logarithm of a numeric expression. If the base is 1 or less than or equal to 0, an error is returned. If the expression is less than or equal to 0, an error is returned.
View examples
Formula Editor Example
SQL Editor Example
select x, y, log(x, y) from tab;
| X | Y | LOG(X, Y) |
|---|---|---|
| 2 | 0.5 | -1 |
| 2 | 1 | 0 |
| 2 | 8 | 3 |
| 2 | 16 | 4 |
| 10 | 10 | 1 |
| 10 | 20 | 1.301029996 |
| 10 | [NULL] | [NULL] |
| [NULL] | 10 | [NULL] |
| [NULL] | [NULL] | [NULL] |
MOD#
Syntax
MOD(<expr1>, <expr2>)
Description
Returns the remainder of input expression 1 divided by input expression 2. Both expressions must be numeric expressions but they are not required to be integers.
View examples
Formula Editor Example
SQL Editor Example
select mod(3, 2) as mod1, mod(4.5, 1.2) as mod2;
| MOD1 | MOD2 |
|---|---|
| 1 | 0.9 |
PI#
Syntax
PI()
Description
Returns the value of pi as a floating-point value.
View examples
Formula Editor Example
Given an empty column in a table:
| VALUE |
|---|
Applied formula:
PI()
Output:
| VALUE_PI |
|---|
| 3.141592653589793 |
SQL Editor Example
select pi();
| PI() |
|---|
| 3.141592654 |
POWER#
Syntax
POWER(<x>, <y>)
Description
Returns a number (
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 0.0 |
| 2.0 |
| 9.0 |
Applied formula:
POWER(VALUE)
Output:
| VALUE_POWER |
|---|
| 0.0 |
| 8.0 |
| 729.0 |
SQL Editor Example
select x, y, pow(x, y) from tab;
| X | Y | POW(X, Y) |
|---|---|---|
| 0.1 | 2 | 0.01 |
| 2 | 3 | 8 |
| 2 | 0.5 | 1.414213562 |
| 2 | -1 | 0.5 |
RADIANS#
Syntax
RADIANS( <real_expr> )
Description
Converts degrees to radians.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 0.0 |
| 23.0 |
| 9.0 |
Applied formula:
RADIANS(VALUE)
Output:
| VALUE_RADIANS |
|---|
| 0.0 |
| 0.40142572795869574 |
| 0.15707963267948966 |
SQL Editor Example
select radians(0), radians(60), radians(180), radians(360), radians(720);
| RADIANS(0) | RADIANS(60) | RADIANS(180) | RADIANS(360) | RADIANS(720) |
|---|---|---|---|---|
| 0 | 1.047197551 | 3.141592654 | 6.283185307 | 12.566370614 |
ROUND#
Syntax
ROUND( <input_expr> [, <scale_expr> ] )
Description
Returns rounded values for input_expr. If the input scale is greater than or equal to zero, then the output scale generally matches the input scale. If the input scale is negative, then the output scale is 0.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| -485.69 |
| -6.64 |
| 7897.9 |
Applied formula:
ROUND(VALUE)
Output:
| VALUE_ROUND |
|---|
| -486.0 |
| -7.0 |
| 7898.0 |
SQL Editor Example
select n, scale, round(n, scale)
from test_ceiling
order by n, scale;
| N | SCALE | ROUND(N, SCALE) |
|---|---|---|
| -975.975 | -1 | -980 |
| -975.975 | 0 | -976 |
| -975.975 | 2 | -975.98 |
| 135.135 | -2 | 100 |
| 135.135 | 0 | 135 |
| 135.135 | 1 | 135.1 |
| 135.135 | 3 | 135.135 |
| 135.135 | 50 | 135.135 |
| 135.135 | NULL | NULL |
SIGN#
Syntax
SIGN( <expr> )
Description
Returns the sign of its argument. Returns -1 if the argument is negativ, returns 1 if it is positive, returns 0 if it is 0.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 0.0 |
| -6.64 |
| 7897.9 |
Applied formula:
SIGN(VALUE)
Output:
| VALUE_SIGN |
|---|
| 0.0 |
| -1.0 |
| 1.0 |
SQL Editor Example
select sign(5), sign(-1.35e-10), sign(0);
| SIGN(5) | SIGN(-1.35E-10) | SIGN(0) |
|---|---|---|
| 1 | -1 | 0 |
SIN#
Syntax
SIN( <expr> )
Description
Computes the sine of its argument. The value should be in radians and not in degrees.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 0.0 |
| 2.0 |
| 1.0 |
Applied formula:
SIN(VALUE)
Output:
| VALUE_SIN |
|---|
| 0.0 |
| 0.9092974268256817 |
| 0.8414709848078965 |
SQL Editor Example
select sin(0), sin(pi()/3), sin(radians(90));
| SIN(0) | SIN(PI()/3) | SIN(RADIANS(90)) |
|---|---|---|
| 0 | 0.8660254038 | 1 |
SQRT#
Syntax
SQRT(<expr>)
Description
Returns the square-root of a non-negative numeric expression. If the input expression is negative, an error will be reported.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 0.0 |
| 2.0 |
| 1.0 |
Applied formula:
SQRT(VALUE)
Output:
| VALUE_SQRT |
|---|
| 0.0 |
| 1.4142135623730951 |
| 1.0 |
SQL Editor Example
select x, sqrt(x) from tab;
| x | sqrt(x) |
|---|---|
| 0 | 0 |
| 2 | 1.414213562 |
| 10 | 3.16227766 |
| [NULL] | [NULL] |
TAN#
Syntax
TAN( <real_expr> )
Description
Computes the tangent of its argument. The value should be in radians and not in degrees.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 0.0 |
| 2.0 |
| 1.0 |
Applied formula:
TAN(VALUE)
Output:
| VALUE_TAN |
|---|
| 0.0 |
| -2.185039863261519 |
| 1.5574077246549023 |
SQL Editor Example
select tan(0), tan(pi()/3), tan(radians(90));
| TAN(0) | TAN(PI()/3) | TAN(RADIANS(90)) |
|---|---|---|
| 0 | 1.732050808 | 1.63312393531954e+16 |
TRUNCATE#
Syntax
TRUNCATE(<input_expr> [, <scale> ] )
Description
Rounds the input expression down to the nearest (or equal) integer closer to zero, or to the nearest equal or smaller value with the specified number of places after the decimal point.
The following applies:
- If 'scale_expr' is negative, then it specifies the number of places before the decimal point to which to adjust the number. For example, if the scale is -2, then the result is a multiple of 100.
- If 'scale_expr' is larger than the input expression scale, the function does not have any effect.
- If either the 'input_expr' or the 'scale_expr' is NULL, then the result is NULL.
- Truncation is performed towards 0, not towards the smaller number. For example, TRUNCATE(-9.6) results in -9, not -10. If the input scale was greater than or equal to zero, then the output scale generally matches the input scale. If the input scale was negative, then the output scale is 0.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 711.56 |
| -272.6 |
| 7470.96 |
Applied formula:
TRUNCATE(VALUE)
Output:
| VALUE_TRUNCATE |
|---|
| 711.0 |
| -272.0 |
| 7470.0 |
SQL Editor Example
select n, scale, trunc(n, scale)
from test_1
order by n, scale;
| N | SCALE | TRUNC(N, SCALE) |
|---|---|---|
| -975.975 | -1 | -970 |
| -975.975 | 0 | -975 |
| -975.975 | 2 | -975.97 |
| 135.135 | -2 | 100 |
| 135.135 | 0 | 135 |
| 135.135 | 1 | 135.1 |
| 135.135 | 3 | 135.135 |
| 135.135 | 50 | 135.135 |
| 135.135 | NULL | NULL |