Skip to content

Numeric Operations#

ABS#

Syntax

ABS(<expr>)

Description

Returns the absolute value of a numeric expression.

View examples

Formula Editor Example

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

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

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

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

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

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

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

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

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

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

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

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

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

SQL Editor Example

select pi();
PI()
3.141592654

POWER#

Syntax

POWER(<x>, <y>)

Description

Returns a number () raised to the specified power ().

View examples

Formula Editor Example

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

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

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

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

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

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

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

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