Numeric Functions

Use numeric functions to manipulate numeric values in SELECT, INSERT, UPDATE, or DELETE queries. DBISAM's SQL supports the following numeric functions:

Function

Description

ABS	Converts a number to its absolute value (non-negative).

ACOS	Returns the arccosine of a number as an angle expressed in radians.

ASIN	Returns the arcsine of a number as an angle expressed in radians.

ATAN	Returns the arctangent of a number as an angle expressed in radians.

ATAN2

Returns the arctangent of x and y coordinates as an angle expressed in radians.

CEIL or CEILING

Returns the lowest integer greater than or equal to a number.

COS	Returns the cosine of an angle.

COT	Returns the cotangent of an angle.

DEGREES

Converts a number representing radians into degrees.

EXP	Returns the exponential value of a number.

FLOOR

Returns the highest integer less than or equal to a number.

LOG	Returns the natural logarithm of a number.

LOG10

Returns the base 10 logarithm of a number.

MOD	Returns the modulus of two integers as an integer.

PI	Returns the ratio of a circle's circumference to its diameter - approximated as 3.1415926535897932385.

POWER

Returns the value of a base number raised to the specified power.

RADIANS

Converts a number representing degrees into radians.

RAND	Returns a random number.

ROUND

Rounds a number to a specified number of decimal places.

SIGN	Returns -1 if a number is less than 0, 0 if a number is 0, or 1 if a number is greater than 0.

SIN	Returns the sine of an angle.

SQRT	Returns the square root of a number.

TAN	Returns the tangent of an angle.

TRUNC or TRUNCATE

Truncates a numeric argument to the specified number of decimal places

ABS Function

The ABS function converts a numeric value to its absolute, or non-negative value:

ABS(column_reference or numeric constant)

ABS can only be used with numeric columns or constants.

ACOS Function

The ACOS function returns the arccosine of a number as an angle expressed in radians:

ACOS(column_reference or numeric constant)

ACOS can only be used with numeric columns or constants.

ASIN Function

The ASIN function returns the arcsine of a number as an angle expressed in radians:

ASIN(column_reference or numeric constant)

ASIN can only be used with numeric columns or constants.

ATAN Function

The ATAN function returns the arctangent of a number as an angle expressed in radians:

ATAN(column_reference or numeric constant)

ATAN can only be used with numeric columns or constants.

ATAN2 Function

The ATAN2 function returns the arctangent of x and y coordinates as an angle expressed in radians:

ATAN2(column_reference or numeric constant,
column_reference or numeric constant)

ATAN2 can only be used with numeric columns or constants.

CEIL or CEILING Function

The CEIL or CEILING function returns the lowest integer greater than or equal to a number:

CEIL(column_reference or numeric constant)
CEILING(column_reference or numeric constant)

CEIL or CEILING can only be used with numeric columns or constants.

COS Function

The COS function returns the cosine of an angle:

COS(column_reference or numeric constant)

COS can only be used with numeric columns or constants.

COT Function

The COT function returns the cotangent of an angle:

COT(column_reference or numeric constant)

COT can only be used with numeric columns or constants.

DEGREES Function

The DEGREES function converts a number representing radians into degrees:

DEGREES(column_reference or numeric constant)

DEGREES can only be used with numeric columns or constants.

EXP Function

The EXP function returns the exponential value of a number:

EXP(column_reference or numeric constant)

EXP can only be used with numeric columns or constants.

FLOOR Function

The FLOOR function returns the highest integer less than or equal to a number:

FLOOR(column_reference or numeric constant)

FLOOR can only be used with numeric columns or constants.

LOG Function

The LOG function returns the natural logarithm of a number:

LOG(column_reference or numeric constant)

LOG can only be used with numeric columns or constants.

LOG10 Function

The LOG10 function returns the base 10 logarithm of a number:

LOG10(column_reference or numeric constant)

LOG10 can only be used with numeric columns or constants.

MOD Function

The MOD function returns the modulus of two integers. The modulus is the remainder that is present when dividing the first integer by the second integer:

MOD(column_reference or integer constant,
column_reference or integer constant)

MOD can only be used with integer columns or constants.

PI Function

The PI function returns the ratio of a circle's circumference to its diameter - approximated as 3.1415926535897932385:

PI()

POWER Function

The POWER function returns the value of a base number raised to the specified power:

POWER(column_reference or numeric constant
TO column_reference or numeric constant)
POWER(column_reference or numeric constant,
column_reference or numeric constant)

POWER can only be used with numeric columns or constants.

RADIANS Function

The RADIANS function converts a number representing degrees into radians:

RADIANS(column_reference or numeric constant)

RADIANS can only be used with numeric columns or constants.

RAND Function

The RAND function returns a random number:

RAND([RANGE range of random values])

The range value is optional and is used to limit the random numbers returned to between 0 and the range value specified. If the range is not specified then any number within the full range of numeric values may be returned.

ROUND Function

The ROUND function rounds a numeric value to a specified number of decimal places:

ROUND(column_reference or numeric constant
[TO number of decimal places])
ROUND(column_reference or numeric constant
[, number of decimal places])

The number of decimal places is optional, and if not specified the value returned will be rounded to 0 decimal places.

ROUND can only be used with numeric columns or constants.

Note

The ROUND function performs "normal" rounding where the number is rounded up if the fractional portion beyond the number of decimal places being rounded to is greater than or equal to 5 and down if the fractional portion is less than 5. Also, if using the ROUND function with floating-point values, it is possible to encounter rounding errors due to the nature of floating-point values and their inability to accurately express certain numbers. If you want to eliminate this possibility you should use the CAST function to convert the floating-point column or constant to a BCD value (DECIMAL or NUMERIC data type in SQL). This will allow for the rounding to occur as desired since BCD values can accurately represent these numbers without errors.

SIGN Function

The SIGN function returns -1 if a number is less than 0, 0 if a number is 0, or 1 if a number is greater than 0:

SIGN(column_reference or numeric constant)

SIGN can only be used with numeric columns or constants.

SIN Function

The SIN function returns the sine of an angle:

SIN(column_reference or numeric constant)

SIN can only be used with numeric columns or constants.

SQRT Function

The SQRT function returns the square root of a number:

SQRT(column_reference or numeric constant)

SQRT can only be used with numeric columns or constants.

TAN Function

The TAN function returns the tangent of an angle:

TAN(column_reference or numeric constant)

TAN can only be used with numeric columns or constants.

TRUNC or TRUNCATE Function

The TRUNC or TRUNCATE function truncates a numeric value to a specified number of decimal places:

TRUNC(column_reference or numeric constant
     [TO number of decimal places])
TRUNCATE(column_reference or numeric constant
        [TO number of decimal places])
TRUNC(column_reference or numeric constant
     [, number of decimal places])
TRUNCATE(column_reference or numeric constant
        [, number of decimal places])

The number of decimal places is optional, and if not specified the value returned will be truncated to 0 decimal places.

TRUNC or TRUNCATE can only be used with numeric columns or constants.

Note

If using the TRUNC or TRUNCATE function with floating-point values, it is possible to encounter truncation errors due to the nature of floating-point values and their inability to accurately express certain numbers. If you want to eliminate this possibility you should use the CAST function to convert the floating-point column or constant to a BCD value (DECIMAL or NUMERIC data type in SQL). This will allow for the truncation to occur as desired since BCD values can accurately represent these numbers without errors.