Numeric functions
Actuate SQL supports the following numeric functions:
*FLOOR, CEILING, MOD
*ROUND
*POWER
FLOOR, CEILING, MOD
FLOOR returns the largest integer not greater than the argument’s value. The result is cast to the specified type:
Decimal FLOOR( value Decimal )
Double FLOOR( value Double )
Example
The following code:
SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR(0.0)
returns:
123,-124,0
CEILING returns the smallest integer not less than the argument’s value. The result is cast to the specified type:
Decimal CEILING( value Decimal )
Double CEILING( value Double )
Example
The following code:
SELECT CEILING(123.45), CEILING(-123.45), CEILING(0.0)
returns:
124,-123,0
MOD returns the remainder after division of two integers:
Integer MOD( v1 Integer, v2 Integer )
Example
The following code:
SELECT CUSTOMERS.CUSTID, CUSTOMERS.CUSTOMNAME
FROM "../Data Sources/MyDatabase/CUSTOMERS.SMA" CUSTOMERS
WHERE MOD(CUSTOMERS.CUSTID, 2) = 1
returns:
101,Signal Engineering
109,InfoEngineering
111,Advanced Design Inc.
…
For decimal data types, the result’s precision and scale for the FLOOR and CEILING functions are (p + 1, s), where (p, s) are the precision and scale of the operand.
ROUND
ROUND returns the number closest in value to the first argument, rounding away from zero. The second argument specifies the precision, with positive values indicating a position to the right of the decimal point, and negative values indicating a position to the left of the decimal point. All positions to the right of the specified position are zero in the result:
Integer ROUND( value integer, precision integer )
Decimal ROUND( value Decimal, precision integer )
Double ROUND( value Double, precision integer )
Example
The following code:
SELECT ROUND(123.4567, 2), ROUND(123.4567, -1)
returns:
123.46, 120
For decimal data types, the result’s precision and scale are (p + 1, s), where (p, s) are the precision and scale of the operand.
POWER
POWER raises the left argument (base) to the power of the right argument (exponent):
Integer POWER( base Integer, exponent Integer )
Decimal POWER( base Decimal, exponent Integer )
Double POWER( base Double, exponent Integer )
Example
The following code:
SELECT CUSTOMERS.CUSTID, POWER(CUSTOMERS.CUSTID, 2)
FROM "../Data Sources/MyDatabase/CUSTOMERS.SMA" CUSTOMERS
returns:
101,10201
102,10404
104,10816
…
For decimal data types, the result’s precision and scale are (P, s), where P is the maximum precision in the database or the Integration service, and s is the scale of the operand.