|
- 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, ...).
Remark: From version 19, HFSQL is the new name of HyperFileSQL. Mathematical SQL functions ABS ABS is used to find out the absolute value of a number. Use 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. Use format: ATAN ATAN is used to find out the tangent angle of a number. Use format: Versions 16 and laterATAN2 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. Use format: New in version 16ATAN2 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. Use 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. Use 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 63-1. Use format: Versions 17 and laterCAST CAST is used to convert a number into another one. Use format: - Converts the GrandTotal(currency) into an integer (no decimal):
CAST(orders.grandtotal AS INTEGER)
- Convert the totalIOT (currency) into 6-digit numeric among which 2 digits for the decimal part.
CAST(orders.grandtotal AS decimal (6,2))
New in version 17CAST CAST is used to convert a number into another one. Use format: - Converts the GrandTotal(currency) into an integer (no decimal):
CAST(orders.grandtotal AS INTEGER)
- Convert the totalIOT (currency) into 6-digit numeric among which 2 digits for the decimal part.
CAST(orders.grandtotal AS decimal (6,2))
CAST CAST is used to convert a number into another one. Use format: - Converts the GrandTotal(currency) into an integer (no decimal):
CAST(orders.grandtotal AS INTEGER)
- Convert the totalIOT (currency) into 6-digit numeric among which 2 digits for the decimal part.
CAST(orders.grandtotal AS decimal (6,2))
Versions 15 and laterCBRT CBRT returns the cube root of a number. Use format: New in version 15CBRT CBRT returns the cube root of a number. Use format: CBRT CBRT returns the cube root of a number. Use format: CEILING Versions 15 and laterCEIL New in version 15CEIL CEIL CEILING and CEIL return the rounded-up value of a number. Use format:
CEILING(Number) CEIL(Number)
COS COS is used to find out the cosine of a number. Use format: Versions 15 and laterDEGREES DEGREES is used to convert an angle expressed in radians into degrees. Use format: New in version 15DEGREES DEGREES is used to convert an angle expressed in radians into degrees. Use format: DEGREES DEGREES is used to convert an angle expressed in radians into degrees. Use format: Versions 15 and laterDIV DIV performs an entire division. Use format: New in version 15DIV DIV performs an entire division. Use format: DIV DIV performs an entire division. Use format: EXP EXP is used to find out the exponential value of a number. Use format: FLOOR FLOOR is used to find out the rounded-down value of a number. Use 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 63-1. 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. Use format: LOG LOG is used to find out the Napierian logarithm of a number. Use format: Other possible syntax: Base-x logarithm of a number: LOG10 LOG10 is used to find out the decimal logarithm of a number. Use format: MOD MOD is used to find out the remainder of a division between two numbers (modulo). Use 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 63-1. Use format: PI PI is used to find out the value of PI. Use format: POWER POWER is used to find out the value of a number raised to a power. Use format: Versions 15 and laterRADIANS RADIANS converts an angle expressed in degrees into radians. Use format: New in version 15RADIANS RADIANS converts an angle expressed in degrees into radians. Use format: RADIANS RADIANS converts an angle expressed in degrees into radians. Use format: Versions 15 and laterRANDOM RANDOM returns a number included between 0.0 and 1.0 (inclusive). Versions 22 and laterRAND is equivalent: it returns a number included between 0.0 and 1.0 (inclusive). New in version 22RAND is equivalent: it 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).Use format: - RANDOM:
Versions 22 and laterRAND:
New in version 22RAND:
RAND:
New in version 15RANDOM RANDOM returns a number included between 0.0 and 1.0 (inclusive). Versions 22 and laterRAND is equivalent: it returns a number included between 0.0 and 1.0 (inclusive). New in version 22RAND is equivalent: it 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).Use format: - RANDOM:
Versions 22 and laterRAND:
New in version 22RAND:
RAND:
RANDOM RANDOM returns a number included between 0.0 and 1.0 (inclusive). Versions 22 and laterRAND is equivalent: it returns a number included between 0.0 and 1.0 (inclusive). New in version 22RAND is equivalent: it 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).Use format: - RANDOM:
Versions 22 and laterRAND:
New in version 22RAND:
RAND:
ROUND ROUND is used to round up a number according to the number of decimal places. Use 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. Use format: SIN SIN is used to find out the sine of a number. Use format:
Remark: The Number parameter is expressed in radians. SQRT SQRT is used to find out the square root of a number. Use format: Versions 17 and laterSTDDEV 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. Use 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
New in version 17STDDEV 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. Use 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 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. Use 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
Versions 17 and laterSTDDEV_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. Use 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
New in version 17STDDEV_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. Use 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_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. Use 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
Versions 17 and laterSTDDEV_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. Use 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
New in version 17STDDEV_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. Use 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_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. Use 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. Use format: TRUNC TRUNC is used to find out the integer part. Use format: Versions 16 and laterUNHEX 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. Use format:
UNHEX(Hexadecimal string)
Remark: If the characters passed to the function do not correspond to elements of an hexadecimal value, the function returns NULL. New in version 16UNHEX 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. Use format:
UNHEX(Hexadecimal string)
Remark: If the characters passed to the function do not correspond to elements of an hexadecimal value, the function returns NULL. 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. Use format:
UNHEX(Hexadecimal string)
Remark: If the characters passed to the function do not correspond to elements of an hexadecimal value, the function returns NULL. Versions 17 and laterVARIANCE 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. Use 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
New in version 17VARIANCE 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. Use 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
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. Use 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
Versions 17 and laterVAR_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. Use 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
New in version 17VAR_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. Use 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_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. Use 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
Versions 17 and laterVAR_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. Use 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
New in version 17VAR_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. Use 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
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. Use 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…
|
|
|
| |
| Click [Add] to post a comment |
|
| |
|
| |
| |
| |
| |
| |
| |
| | |
| |