SQL Scalar and Aggregate Functions
This chapter describes the SQL Scalar Functions supported in PointBase. PointBase provides these ready to use functions to perform in-statement operations when querying or inserting data into the database. For example, you can use the CAST function to convert data types to other data types or use a numeric function to perform calculations. The following sections describe the behavior of these functions and examples of how to use them.NOTE: Unless specified otherwise, when applying any of the following functions to a column containing NULLS, the NULL rows are not counted or used and the following warning is given:
java.sql.SQLWarning: Warning--null value eliminated in set function
To eliminate this warning and ignore the NULLs in aggregate functions, you can use the DISTINCT keyword in front of the column reference, for example:
SQL Scalar Numeric Functions
The Scalar Numeric Function operates on numeric values (i.e. INTEGER, SMALLINT, DECIMAL, FLOAT, DOUBLE and NUMERIC data types). The PointBase database supports the following standard Numeric Functions:
The numeric functions are evaluated in the following order. Numeric Functions within parentheses are evaluated from the innermost set of parentheses, following the same rules of precedence:
- Multiplication (*) and division (/) from left to right
- Addition (+) and subtraction (-) from left to right
Numeric Functions are calculated as floating point numbers with a precision of 17 significant digits (and a rounding error). However, if you use these functions when inserting or updating data the accuracy is dependent up on the data type of the column for which the data is intended.
2 + 3 * 4 / 2 = 8
2 + (3 * 4) / 2 = 8
2 + 3 / 2 = 3.5
100/3 = + 3 / 2 = 33.333333333333333
SQL Scalar Character String Functions
Scalar Character String Functions operate on character strings. These functions all return either character strings or numeric values. PointBase currently supports the following functions.
The concatenation operator (||) joins the values of two or more character strings into a single string. You may use the concatenated string expression anywhere you would use a character string and there is no limit to the number of string expressions you can concatenate. The following is the CONCATENATION Function syntax:
SELECT order_num, sales_tax_st_cd, 'Shipping Cost', '$' || shipping_cost FROM order_tbl WHERE shipping_cost > 300 AND UPPER(sales_tax_st_cd) NOT LIKE '%FL' ORDER BY order_num ASC;
The SUBSTRING Function extracts a specified portion of the character string on which it is operating. The following is the SUBSTRING Function syntax:
In the previous syntax, the start variable is an integer that represents the starting position for the sub string. The first character in a string is considered to be position 1. The length variable is optional and indicates the length of the sub string; if it is missing, the SUBSTRING Function returns the characters from the start position to the end of the character string.
The CHARACTER_LENGTH function returns the length of a character string as the numeric data type. There are two syntax variations for the CHARACTER_LENGTH function:
- CHARACTER_LENGTH (string_value)
- CHAR_LENGTH (string_value).
The POSITION function searches for a specified string pattern in another string. If the pattern is found, a value is returned that indicates the beginning position of the location of the pattern. If the pattern is not found, then a value of zero is returned. If the pattern is a string length of zero (0, a NULL string), then a value of one is returned. All returned values are of the numeric data type. The following illustrates the syntax for the POSITION Function:
The TRIM function allows you to strip trailing and/or leading characters from a character string. The following illustrates the syntax for the TRIM Function:
Although it is common only to strip a blank characters (' ') from the start and ends of character strings, using the TRIM function you can strip any character. The character variable, enclosed in single quotes, represents the character that is to be stripped from the character string. The keywords LEADING, TRAILING, and BOTH indicate whether you strip the character variable from the front of the character string, at the end of the character string, or both.
UPPER and LOWER
The UPPER function returns the value specified in the character string entirely in upper case letters, regardless of the initial capitalization of the character string. The LOWER Function returns the value specified in the character string entirely in lower case letters, regardless of the initial capitalization of the character string variable. The following syntax is used for the Case Functions:
SQL Scalar Date/Time Functions
The SQL Scalar Date Time Functions operate on date/time values and return of date/time values. PointBase supports the following Date/Time Functions.
The CURRENT_DATE Function returns the current system date from the machine that is hosting the PointBase database as a DATE data type. You may use the CURRENT_DATE Function anywhere you specify a DATE value.
If the current date is April 4, 1998, the CURRENT_DATE Function returns: 1998-04-04.
The CURRENT_TIME Function returns the current system time from the machine that is hosting the PointBase database as a TIME data type. You may use the CURRENT_TIME Function anywhere you specify a time value.
if the current time is exactly 9:00 AM, the CURRENT_TIME Function returns: 09:00:00.
The CURRENT_TIMESTAMP Function returns the current system date and time from the machine that is hosting the PointBase database as a TIMESTAMP data type. You may use the CURRENT_TIMESTAMP Function anywhere you specify a timestamp value.
If the current date and time is 9:00 AM on April 4, 1998, the CURRENT_TIMESTAMP
Function returns: 1998-04-04 09:00:00.
The EXTRACT Function returns a portion of a DATE, TIME, or TIMESTAMP value. It extracts the year, month, or day from a DATE value; an hour, minute, or second from a TIME value; or any of these intervals from a TIMESTAMP value. The EXTRACT Function always returns a numeric data type. The following syntax is for the EXTRACT Function.
Use one of the keywords YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND in place of the extract_field. Format the datetime_value inside the single quotes appropriately, according to the value the extract_field seeks.
SQL Scalar CAST Function
The SQL Scalar CAST Function explicitly converts a value from one PointBase data type to another. To perform an explicit data conversion, use the following syntax for the SQL Scalar CAST Function.
Table 1 lists the data types that can be CAST into other data types. If there is a Y in the intersection of two data types, the CAST Function can perform an explicit conversion from the data type in the vertical axis to the data type on the horizontal axis.
Table 1 : Converting Data Types With the CAST Function Y Y Y Y Y Y Y Y Y Y Y Y Y N Y Y Y Y Y Y Y Y Y Y Y Y Y Y N Y Y Y Y N N N N N N N N N N N N Y Y N Y Y Y Y Y Y Y N N N N N Y Y N Y Y Y Y Y Y Y N N N N N Y Y N Y Y Y Y Y Y Y N N N N N Y Y N Y Y Y Y Y Y Y N N N N N Y Y N Y Y Y Y Y Y Y N N N N N Y Y N Y Y Y Y Y Y Y N N N N N Y Y N Y Y Y Y Y Y Y N N N N N Y Y N N N N N N N N Y N Y N N Y Y N N N N N N N N N Y Y N N Y Y N N N N N N N N Y Y Y N N N N N N N N N N N N N N N Y N Y Y N N N N N N N N N N N N Y
NOTE: A VARCHAR(10) cast to CHAR(5) will be truncated at the 5th character. The system will display a warning if the truncated characters are nonwhite spaces.
The CAST function throws an exception if the data is not convertible, for example: CAST('a' AS INT) --------> Exception
SQL Scalar Routine Invocation
Using SQL Scalar Routine Invocation, you can call a pre-defined SQL Routine that returns a scalar value. The Routine Invocation can be used anywhere you use a scalar expression. The following syntax is for the Routine Invocation Function. For more information about creating SQL routines (functions and procedures) refer to "Appendix A: SQL Reference."
Routine_name is the name of the routine (SQL Function or Procedure). SQL_argument_list consists of expressions separated by commas. Each expression will result in a SQL data type dependent on the routine called.NOTE: If you use a Routine Invocation Function as a scalar expression, it must only return a single value, otherwise an error is raised.
Routine determination is the process that determines the routine to invoke, based on the routine name, SQL argument list, and the current path of schemas. The routine name and SQL arguments make up the signature of the routine. It is possible that more than one routine could have the same signature. If more than one possible routine has the same signature, then PointBase uses a precedence list to match each argument of each routine, to determine which one is the best match.
SQL Aggregate Functions
SQL Aggregate Functions operate on complete sets of data and return a single result. PointBase supports five Aggregate Functions: AVG, COUNT, MAX, MIN, and SUM.
The AVG Function returns the average value for the column when applied to a column containing numeric data. The following is the syntax for the AVG Function.
The COUNT Function returns the number of rows in a specified result set. The following syntax is one form of the COUNT Function:
The second form of the COUNT Function returns the number of rows in a result set where the specified column has a distinct, non-NULL value. The following syntax is the second form of the COUNT Function.
The MAX Function returns the data item with the highest value for a column when applied to a column containing numeric data. If you apply the MAX Function to a CHARACTER value, it returns the last value in the sorted values for that column. The following syntax is for the MAX Function.
The MIN Function returns the data item with the lowest value for a column when applied to a column containing numeric data. If you apply the MIN Function to a CHARACTER value, it returns the first value in the sorted values for that column. The following syntax is for the MIN Function.
The SUM Function returns the sum of all values in the specified column. The result of the SUM Function has the same precision as the column on which it is operating. The following syntax is for the SUM Function.
SQL Special Registers
The PointBase RDBMS supports the following list as special registers. These can be used anywhere a scalar/value expression is allowed.
- CURRENT_USER: is the current user on the system and is an SQL varchar data type of maximal length 128.
- CURRENT_SCHEMA: is the name of the current schema in use and is an SQL varchar data type of maximal length 128.
- CURRENT_DATABASE: is the name of the database in use and is an SQL varchar data type of maximal length 128.
- CURRENT_SESSION: gives the current session ID.
- CURRENT_PATH: is the list of schemas in the path of the current user. The return data type is an SQL varchar of undetermined length. The length depends upon the number of schema names in the path.