Using Scalar, Arithmetic, and Bit Manipulation Functions
Using Scalar Functions
BI Server supports a large number of built-in functions for arithmetic, string manipulation, date/time, and more. Most of the functions supported by the expression editor are also supported in BI Server with the same name. Function names are case-insensitive.
For most functions, when a parameter is NULL, then the result of the function will also be NULL:
Examples:
SELECT 1 + NULL --returns NULL
SELECT sin(NULL) --returns NULL
COALESCE
Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. The resulting data type of the COALESCE function is the highest data type of its arguments.
The following query returns a comma-separated country and region for each customer or just the country when the region is NULL.
Example:
SELECT COALESCE(Country + ', ' + Region, Country) FROM Customers
ISNULL
Evaluates the first argument and, if it evaluates to NULL, returns the value of the second argument; otherwise, returns the first argument.
The following query returns the region for each customer and the string "no region" when the region is NULL.
Example:
SELECT ISNULL(Region, 'No Region') FROM Customers
Using Arithmetic Functions
BI Server supports basic arithmetic operators, rounding functions, trigonometric functions, etc.
ABS
Calculates the absolute value of the input expression.
Example:
SELECT abs(ProductID - 35) FROM Products
ACOS
Calculates the arccosine of the input expression.
Example:
SELECT acos(-1) --returns π
ASIN
Calculates the arcsine of the input expression.
Example:
SELECT asin(1) --returns π/2
ATAN
Calculates the arctangent of the input expression.
Example:
SELECT atan(1) --returns π/4
CEIL, CEILING
Returns the smallest integral value that is greater than or equal to the specified input expression. The result type is either Double or Decimal, depending on the input.
Examples:
SELECT ceil(0.1) --returns 1
SELECT ceiling(0.9) --returns 1
COS
Calculates the cosine of the input expression.
Example:
SELECT cos(2 * pi()) --returns 1
COT
Calculates the cotangent of the input expression.
Example:
SELECT cot(pi() /4) --returns 1
E
Returns the natural logarithmic base, specified by the constant e.
Example:
SELECT e() --returns 2.718…
EXP
Returns e raised to the specified power.
Example:
SELECT exp(2) --returns e2
FLOOR
Returns the largest integral value that is less than or equal to the specified input expression. The result type is either Double or Decimal, depending on the input.
Examples:
SELECT floor(1.1) --returns 1
SELECT floor(1.9) --returns 1
GREATEST
Returns the maximum from the specified list of input expressions. This function supports an unlimited number of parameters. The following query retrieves, for every row, the maximum value among all columns listed and the constant 30.
Example:
SELECT greatest(UnitsInStock, UnitsOnOrder, ReorderLevel, 30) FROM Products
ISINFINITY
Returns a Boolean indicating whether the specified expression evaluates to negative or positive infinity.
Examples:
SELECT isinfinity(cot(0)) --returns true
SELECT isinfinity(1) --returns false
ISNAN
Returns a Boolean indicating whether the specified value is not a number. Floating-point operations return NaN to signal that the result of the operation is not defined.
Example:
SELECT isnan(0 / 0.0) --returns true
LEAST
Returns the minimum from the specified list of input expressions. This function supports an unlimited number of parameters. The following query retrieves, for every row, the minimum value among all columns listed and the constant 10.
Example:
SELECT least(UnitsInStock, ReorderLevel, 10) FROM Products WHERE ReorderLevel > 0
LN
Returns the natural (base e) logarithm of the specified expression.
Example:
SELECT ln(e()) --returns 1
LOG
Returns the base 10 logarithm of the specified expression.
Example:
SELECT log(10) --returns 1
PI
Returns the constant π
Example:
SELECT pi() --returns π
POW, POWER
Raises a number to the specified power.
Examples:
SELECT pow(2, 3) --returns 8
SELECT power(3, 2) --returns 9
ROUND
Rounds the input expression to the nearest integral value and rounds midpoint values to the nearest even number.
Examples:
SELECT round(4.4) --returns 4
SELECT round(4.5) --returns 4
SELECT round(4.6) --returns 5
SELECT round(5.5) --returns 6
ROUNDTO
Rounds the input expression to a specified number of fractional digits and rounds midpoint values to the nearest even number.
Examples:
SELECT roundto(3.44, 1) --returns 3.4
SELECT roundto(3.45, 1) --returns 3.4
SELECT roundto(3.46, 1) --returns 3.5
SIGN
Returns an integer that indicates the sign of the specified input expression.
Examples:
SELECT sign(2) --returns 1
SELECT sign(-2) --returns -1
SIN
Calculates the sine of the input expression.
Example:
SELECT sin(pi() / 2) --returns 1
SQRT
Calculates the square root of the input expression.
Example:
SELECT sqrt(9) --returns 3
SQUARE
Calculates the square of the input expression.
Example:
SELECT square(5) --returns 25
TAN
Calculates the tangent of the input expression.
Example:
SELECT tan(pi() / 4) --returns 1
Using BIT Manipulation Functions
BI Server supports Bit-testing and bit-manipulation functions.
BITTEST
Tests if the bit at the specified position in the specified numeric expression is equal to 1. The syntax is:
bittest(numeric_expression_to_test, index_of_the_bit_to_test)
Examples:
SELECT bittest(2, 0) --returns false
SELECT bittest(2, 1) --returns true
SETBIT
Sets the bit at the specified position in the specified numeric expression to the specified value. The syntax is:
setbit(numeric_expression, index_of_the_bit_to_set, value_of_the_bit)
Examples:
SELECT setbit(2, 0, 1) --returns 3
SELECT setbit(2, 1, 0) --returns 0
SHL
Shifts the bits of the specified numeric expression to the left by the specified number of positions. The syntax is:
shl(numeric_expression, number_of_positions_by_which_to_shift_bits)
Example:
SELECT shl(1, 3) --returns 8
SHR
Shifts the bits of the specified numeric expression to the right by the specified number of positions. The syntax is:
shr(numeric_expression, number_of_positions_by_which_to_shift_bits)
Example:
SELECT shr(8, 3) --returns 1
TOGGLEBIT
Toggles the bit of the specified numeric expression at the specified index. The syntax is:
togglebit(numeric_expression, index_of_the_bit_to_toggle)
Examples:
SELECT togglebit(2, 0) --returns 3
SELECT togglebit(2, 1) --returns 0