Numeric functions

Overview

The numeric functions perform arithmetic and complex mathematical operations on numbers and convert numbers from one data type to another.

Abs

Returns a value of the same type that is passed to it specifying the absolute value of a number.

Syntax

Abs( number )

The required argument number must be of type Integer or Float.

Remarks

The absolute value of a number is its unsigned magnitude.

Examples

• Abs( 98.3 ) returns 98.3.

• Abs( -98.3 ) returns 98.3.

Acos

Calculates the arccosine.

Syntax

acos( number )

The required argument number is the value between –1 and 1 whose arcsine is to be calculated.

Remarks

Returns the arccosine of number in the range 0 to ð radians. If x is less than –1 or greater than 1, returns an indefinite by default.

AsDecimal

Attempts to interpret value as a fixed-point value, with the size and scale as specified. Size and scale both refer to the number of digits used in the fixed-point number.

Syntax

AsDecimal( value, size, scale )

The AsDecimal function syntax has the following arguments.

Remarks

The value argument may not contain more than size - scale digits to the left of the decimal point.

Check the result using IsValid to detect invalid conversions. For Date, Time, and DateTime arguments, the result will be the same as the corresponding Julian function.

Examples

• AsDecimal("4.123456789", 11, 6) returns 4.123456, with truncated decimal places.

• AsDecimal("1234567.12", 11, 6) returns <<error>>, because value contains more than size - scale (5) digits to the left of the decimal point.

• AsDecimal("12345.12", 11, 6) returns 12345.120000, padding scale with zeros.

AsFloat

Attempts to interpret value as a Float value.

Syntax

AsFloat( value )

The required argument value may be any type except Boolean or Binary.

Remarks

Check the result using IsValid to detect invalid conversions.

AsFloat returns a double-precision floating-point value. The range of values that can be stored in a double-precision Float is-1.7976931348623158e+308 to 1.7976931348623158e+308. The smallest allowable double-precision value is: 2.2250738585072014e–308.

Examples

• If SIZE is a Text field, then AsFloat(SIZE) attempts to convert the text to a floating-point value.

• If DATETIME is a DateTime field, then AsFloat(DATETIME) produces a floating-point value equal to Julian(DATETIME).

Asin

Calculates the arcsine.

Syntax

Asin( number )

The required argument number is the value whose arcsine is to be calculated.

Remarks

Returns the arcsine of number in the range –ð/2 to ð/2 radians. If x is less than –1 or greater than 1, Asin returns an indefinite by default.

AsInteger

Attempts to convert value to a fixed-point value. This doesn't always work for floating-point values, because floating-point values have an enormous range. When converting floating-point values, it uses a size of 15 and a scale of zero.

Syntax

AsInteger( value )

The required argument value may be any type except Boolean or Blob.

Remarks

Check the result using IsValid to detect invalid conversions. For Date, Time, and DateTime arguments, the result will be the same as the corresponding Julian function.

Examples

• If FIELD1 is a Text field, then IsValid(AsInteger(FIELD1)) returns True if the FIELD1 value of the current record could be interpreted as an integer.

• If SIZE is a Text field, then AsInteger(SIZE) attempts to convert the text to an integer with a scale of zero.

• If DATE is a Date field, then the following will produce an integer equal to Julian(DATE) AsInteger(DATE).

Atan

Calculates the arctangent of number.

Syntax

Atan( number )

The required argument number can be any number.

Remarks

Returns the arctangent of number in the range of –ð/2 to ð/2 radians. If number is 0, returns 0.

Atan2

Calculates the arctangent of y/x.

Syntax

Atan2( x, y )

The required arguments x and y can be any number.

Remarks

Returns the arctangent of y/x in the range –ð to ð radians. This function is almost the same as atan(y/x), except that it properly handles the overflow case of x=0.

BitwiseAnd

Computes the bitwise AND of value a and value b.

Syntax

BitwiseAnd( a, b )

Both required arguments must be integers in the range -2^31 to 2^31-1.

Remarks

Returns a number where bits are set only where both corresponding bits of each input are also set.

Bitwise operators are usually used for representing sets of attributes or "flags" as integers. A set of flags can be stored as a single integer by associating each flag with a power of 2. For example, given the following:

• Attribute1=1

• Attribute2=2

• Attribute3=4

• Attribute4=8

You could represent { Attribute2, Attribute3 } as the number 6.

The AND operator is typically used to extract a single flag (test whether or not it is set) out of an integer.

Example

• BitwiseAnd(32, 48) returns 32.

• 32 as binary is 00100000.

• 48 as binary is 00110000.

• AND = 00100000 = 32.

BitwiseOr

Computes the bitwise OR of value a and value b.

Syntax

BitwiseOr( a, b )

Both required arguments must be integers in the range -2^31 to 2^31-1.

Remarks

Returns a number where bits are set where either corresponding bit of each input is also set.

Bitwise operators are usually used for representing sets of attributes or "flags" as integers. A set of flags can be stored as a single integer by associating each flag with a power of 2. For example, given the following:

• Attribute1=1

• Attribute2=2

• Attribute3=4

• Attribute4=8

you could represent { Attribute2, Attribute3 } as the number 6.

The OR operator is typically used to combine flags into a set.

Example

• BitwiseOr(32, 48) returns 48.

• 32 as binary is 00100000.

• 48 as binary is 00110000.

• OR = 00110000 = 48.

BitwiseXor

Computes the bitwise AND of value a and value b.

Syntax

BitwiseXor( a, b )

Both required arguments must be integers in the range -2^31 to 2^31-1.

Remarks

Returns a number where bits are set where either corresponding bit of each input is also set, but not both.

Bitwise operators are usually used for representing sets of attributes or "flags" as integers. A set of flags can be stored as a single integer by associating each flag with a power of 2. For example, given the following:

• Attribute1=1

• Attribute2=2

• Attribute3=4

• Attribute4=8

you could represent { Attribute2, Attribute3 } as the number 6.

The XOR operator is typically used to "toggle" a flag in a set (not used very often, but added for completeness).

Example

• BitwiseXor(32, 48) returns 16.

• 32 as binary is 00100000.

• 48 as binary is 00110000.

• OR = 00010000 = 16.

Cos

Calculates the cosine.

Syntax

cos( number )

The required argument number is the angle in radians.

Remarks

Returns the cosine of number.

Cosh

Calculates the hyperbolic cosine.

Syntax

cosh( number )

The required argument number is the angle in radians.

Remarks

Returns the hyperbolic cosine of number.

Counter

Returns a sequentially-increasing integer that is unique per project instance.

Syntax

Counter( )

Generates a different number each time the function is called. It is especially useful for generating unique temporary RDBMS table names, using the ${} configuration syntax.

Example

If you have an RDBMS Output tool that uses temp tables to perform update or delete operations, you cannot use that tool in a macro without multiple instances of the tool attempting to use the same temp table. Instead, use syntax like ${"mytemptable" + Counter()}.

This will generate mytemptable1 the first time, mytemptable2 the second time, and so on. Be sure to enable the RDBMS Output tool's Drop after use option to avoid accumulating temp tables that are no longer needed.

Exp

Returns a Float that is number raised to power.

Syntax

Exp( number, power )

The Exp function syntax has the following arguments:

FormatIntegerAsHex

Produces a text output in which the input integer value is represented in hexadecimal notation.

Syntax

FormatIntegerAsHex( integer )

Example

FormatIntegerAsHex(123456) returns "1E240".

Ln

Returns a Float specifying the natural logarithm of a number.

Syntax

Ln( number )

The required argument number must be Integer or Float.

Remarks

If number is less than or equal to 0, an error value is returned. Test for errors using IsValid.

Log10

Returns a Float that is the logarithm, base-10, of number.

Syntax

Log10( number )

The required argument number must be of type Integer or Float.

Remarks

If number is less than or equal to 0, an error value is returned. Test for errors using IsValid.

Random

Generates a random integer.

Syntax

Random( range )

Accepts a single integer argument range, and outputs a pseudo-random number between zero and range-1. The required argument range must be a literal number between 2 and 2147483647.

Random( lower, upper )

Generates a random integer between lower and upper values, inclusive. The required arguments lower and upper must be integers.

Example

Random(100) returns random numbers between 0 and 99.

RandomFloat

Generates a random floating-point value between 0.0 and 1.0, inclusive.

Syntax

RandomFloat( )

RandomFloatExclusive

Generates a random floating-point value between 0.0 (inclusive) and 1.0 (exclusive).

Syntax

RandomFloatExclusive( )

Round

Rounds number to nearest digits.

Syntax

Round( number, digits )

The required argument number must be of type Integer, Decimal, or Float. The required argument digits must be a literal integer. If digits = 0, rounds to an integer.

Example

• Round(1.2345,2) returns 1.23.

• Round(1.2345,3) returns 1.235.

Remarks

Since floating-point values cannot exactly represent all decimal values, inputs converted to type Float may produce inexact results, including when used with Round().

Sin

Calculates sine.

Syntax

sin( number )

The required argument number is the angle in radians.

Remarks

Returns the sine of number. If number is greater than or equal to 263, or less than or equal to –263, a loss of significance in the result occurs.

Sinh

Calculates hyperbolic sine.

Syntax

sinh( number )

The required argument number is the angle in radians.

Remarks

Returns the hyperbolic sine of number.

Sqr

Squares a numeric value.

Syntax

Sqr( number )

The required argument number must be of type Integer or Float.

Example

Sqr(11.0) returns 121.0.

Sqrt

Finds the square root of number.

Syntax

Sqrt( number )

The required argument number must be between must be a positive value of type Fixed-point Number or Floating-point Number.

Example

Sqrt(64) returns 8.

Tan

Calculates the tangent.

Syntax

tan( number )

The required argument number is the angle in radians.

Remarks

Returns the tangent of number. If number is greater than or equal to 263, or less than or equal to –263, a loss of significance in the result occurs.

Tanh

Calculates hyperbolic tangent.

Syntax

tanh( number )

The required argument number is the angle in radians.

Remarks

Returns the hyperbolic tangent of number. There is no error return.

Truncate

Truncates number to the nearest digits.

Syntax

Truncate( number, digits )

The required argument number must be of type Integer, Decimal, or Float. The required argument digits must be a literal integer.

Example

• Truncate(1.2345,2 returns 1.23.

• Truncate(1.2345,3) returns 1.234.