 Overview
 Mathematical SQL functions
 ABS
 ACOS
 ASIN
 ATAN
 ATAN2
 BIN
 CAST
 CBRT
 CEILING
 CEIL
 COS
 DEGREES
 DIV
 EXP
 FLOOR
 HEX
 LN
 LOG
 LOG10
 MOD
 OCT
 PI
 POWER
 RADIANS
 RANDOM
 ROUND
 SIGN
 SIN
 SQRT
 STDDEV
 STDDEV_POP
 STDDEV_SAMP
 TAN
 TRUNC
 UNHEX
 VARIANCE
 VAR_POP
 VAR_SAMP
Mathematical SQL functions
The mathematical SQL functions that can be used on the queries written in SQL code are as follows: See a specific documentation about the SQL language for more details. Remarks:  These statements can be used:
 in the SQL code of queries created in the query editor. Then, these queries will be run by HExecuteQuery.
 in the SQL code of queries run by HExecuteSQLQuery.
 Unless stated otherwise, these functions can be used with all types of data sources (Oracle, Access, SQL Server, ...).
Mathematical SQL functions ABS ABS is used to find out the absolute value of a number. Format: ACOS ACOS is used to find out the cosine angle of a number. Use format: ASIN ASIN is used to find out the sine angle of a number. Format: ATAN ATAN is used to find out the tangent angle of a number. Format: ATAN2 ATAN2 is used to find out the arctangent of the X and Y variables. This function is equivalent to the calculation of the arctangent of Y/X except that the signs of the two arguments are used to define the quadrant of the result. Format: BIN BIN returns the character string representing an "integer" in binary format. This integer must be found between 0 and 2 to the power of 631. Format: CAST CAST is used to convert a number into another one. Format:  Converts the GrandTotal(currency) into an integer (no decimal):
CAST(orders.grandtotal AS integer)  Convert the totalIOT (currency) into 6digit numeric among which 2 digits for the decimal part.
CAST(orders.grandtotal AS decimal (6,2))
CBRT CBRT returns the cube root of a number. Format: CEILING CEIL CEILING and CEIL return the roundedup value of a number. Format: CEILING(Number) CEIL(Number) COS COS is used to find out the cosine of a number. Format: DEGREES DEGREES is used to convert an angle expressed in radians into degrees. Format: DIV DIV performs a division Format: EXP EXP is used to find out the exponential value of a number. Format: FLOOR FLOOR is used to find out the roundeddown value of a number. Format: HEX HEX returns the hexadecimal value of an integer (expressed in base 10). This integer must be found between 0 and 2 to the power of 631. Remark: Used with the HFSQL engine, HEX returns a string starting with the high byte. Example on HFSQL: HEX(258) = 0102 Example on DB2: HEX(258) = 0201 LN LN is used to find out the Napierian logarithm of a number. Format: LOG LOG is used to find out the Napierian logarithm of a number. Format: Other possible syntax: Basex logarithm of a number: LOG10 LOG10 is used to find out the decimal logarithm of a number. Format: MOD MOD is used to find out the remainder of a division between two numbers (modulo). Format: OCT OCT returns an integer in octal (expressed in base 10). This integer must be found between 0 and 2 to the power of 631. Format: PI PI is used to find out the value of PI. Format: POWER POWER is used to find out the value of a number raised to a power. Format: RADIANS RADIANS converts an angle expressed in degrees into radians. Format: RANDOM RANDOM returns a number included between 0.0 and 1.0 (inclusive). RAND is equivalent: it returns a number included between 0.0 and 1.0 (inclusive). Format: ROUND ROUND is used to round up a number according to the number of decimal places. Format: ROUND(Number, Decimal places) Example: The following SQL code is used to round up the price of products to 2 decimal places: SELECT Designation,
ROUND(PriceBT, 2) AS Price
FROM PRODUCT SIGN SIGN returns 1, 0 or 1 depending on whether the given number is positive, null or negative. Format: SIN SIN is used to find out the sine of a number. Format: Remark: The Number parameter is expressed in radians. SQRT SQRT is used to find out the square root of a number. Format: STDDEV STDDEV is used to find out the standard deviation for a series of values. STDDEV is used when the series of values represents a sample of the data population. This function is equivalent to STDDEV_SAMP. The standard deviation is the square root of the variance. Format: Example: Mean and standard deviation of TotalIOT for the orders per year: SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
STDDEV_SAMP(orders.totaliot) AS standarddev FROM orders GROUP BY year STDDEV_POP STDDEV_POP is used to find out the standard deviation for a series of values. STDDEV_POP is used when the series of values represents the entire data population. The standard deviation is the square root of the variance. Format: Example: Mean and standard deviation of TotalIOT for the orders per year: SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
STDDEV_POP(orders.totaliot) AS standarddev FROM orders GROUP BY year STDDEV_SAMP STDDEV_SAMP is used find out the standard deviation for a series of values. STDDEV_SAMP is used when the series of values represents a sample of the data population. This function is equivalent to STDDEV. The standard deviation is the square root of the variance. Format: Example: Mean and standard deviation of TotalIOT for the orders per year: SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
STDDEV_SAMP(orders.totaliot) AS standarddev FROM orders GROUP BY year TAN TAN is used to find out the tangent of a number. Format: TRUNC TRUNC is used to find out the integer part. Format: UNHEX UNHEX performs the reverse operation of HEX. Each pair of hexadecimal digits:  is interpreted as numbers.
 is converted into a character represented by the number.
The returned value is a binary string. Format: UNHEX(Hexadecimal string) Remark: If the characters passed to the function do not correspond to elements of a hexadecimal value, the function returns NULL. VARIANCE VARIANCE is used to find out the variance for a series of values. VARIANCE is used when the series of values represents a sample of the data population. This function is equivalent to VAR_SAMP. Format: Example: Mean and variance of TotalIOT for the orders per year: SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
VARIANCE(orders.totaliot) AS myvariance FROM orders GROUP BY year VAR_POP VAR_POP is used to find out the variance for a series of values. VAR_POP is used when the series of values represents the entire data population. Format: Example: Mean and variance of TotalIOT for the orders per year: SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
VAR_POP(orders.totaliot) AS myvariance FROM orders GROUP BY year VAR_SAMP VAR_SAMP is used to find out the standard deviation for a series of values. VAR_SAMP is used when the series of values represents a sample of the data population. This function is equivalent to VARIANCE. The standard deviation is the square root of the variance. Format: Example: Mean and variance of TotalIOT for the orders per year: SELECT SUBSTR(orders.orderdate,1,4) AS year, AVG(orders.totaliot) AS mean,
VAR_SAMP (orders.totaliot) AS myvariance FROM orders GROUP BY year
This page is also available forâ€¦


