SQL - Numeric Functions


The table below lists the numeric functions specified in the ODBC 3.51 and JDBC 2.0 specifications that are supported by the BASIS DBMS.

Arguments denoted as numeric_exp can be the name of a column (of any numeric type), the result of another scalar function that returns a numeric value, a mathematical operation, or a numeric literal.

Arguments denoted as float_exp can be the name of a column (of type FLOAT, DOUBLE, REAL, or DECIMAL), the result of another scalar function that returns a float value, a mathematical operation, or a numeric literal.

Arguments denoted as integer_exp can be the name of a column (of type signed or unsigned INTEGER), the result of another scalar function that returns an integer value, a mathematical operation, or an integer literal.

Function

Description

ABS(numeric_exp)

Returns the absolute value of numeric_exp.

ACOS(numeric_exp)

Returns the arccosine of float_exp as an angle expressed in radians.

ASIN(numeric_exp)

Returns the arcsine of float_exp as an angle expressed in radians.

ATAN(float_exp)

Returns the arctangent of float_exp as an angle expressed in radians.

ATAN2(double,double)

Returns the arctangent of the x and y coordinates, specified by float_exp1 and float_exp2, respectively, as an angle, expressed in radians.

CEILING(numeric_exp)

Returns the smallest integer greater than or equal to numeric_exp.

COS(float_exp)

Returns the cosine of the float_exp as an angle expressed in radians.

COT(numeric_exp)

Returns the cotangent of numeric_exp as an angle expressed in radians.

DEGREES(numeric_exp)

Returns the number of degrees converted from numeric_exp radians.

EXP(numeric_exp)

Returns the exponential value of float_exp.

FLOOR(numeric_exp)

Returns the largest integer less than or equal to numeric_exp.

LOG(numeric_exp)

Returns the natural logarithm of float_exp.

LOG10(numeric_exp)

Returns the base 10 logarithm of float_exp.

MEDIAN(numeric_exp)

In BBj 8.0 and higher, returns the median of a group of numbers.

MOD(integer_exp1, integer_exp2)

Returns the remainder (modulus) of integer_exp1 divided by integer_exp2.

PI()

The numeric literal for PI defined as:

3.14159265358979323846264338327950288419716939937510.

POWER(numeric_exp)

Returns the value of numeric_exp to the power of integer_exp.

RADIANS(numeric_exp)

Returns the number of radians concerted from numeric_exp degrees.

RAND(numeric_exp)

Generates a random number using numeric_exp as a seed.

ROUND(numeric_exp, integer_exp)

Rounds numeric_exp to integer_exp decimal places.

SIGN(numeric_exp)

If numeric_exp is less than zero, -1 is returned. If numeric_exp equals zero, 0 is returned. If numeric_exp is greater than zero, 1 is returned.

SIN(float_exp)

Returns the sine of the float_exp as an angle expressed in radians.

SQRT(numeric_exp)

Returns the square root of numeric_exp.

STDDEV(numeric_exp)

In BBj 8.0 and higher, returns the standard deviation of a group of numbers.

TAN(numeric_exp)

Returns the tangent of numeric_exp as an angle expressed in radians.

TRUNCATE(numeric_exp)

Returns numeric_exp with the decimal portion removed.

Example

Select IFF(SIGN(curr_bal)>=0, '+', '-')+STR(ABS(curr_bal)) from customer



______________________________________________________________________________________

Copyright BASIS International Ltd. BBj®, Visual PRO/5®, PRO/5®, and BBx® are registered trademarks.