TOC PREV NEXT

 

 

 

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:

select (count(DISTINCT product_code)) from product_tbl

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:

    1. Multiplication (*) and division (/) from left to right
    2. 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.

Examples

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.

CONCATENATION

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:

string_value || string_value [{|| string_value}...]

Examples:

'$' || ' ' || '150' ----> '$150'

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;

SUBSTRING

The SUBSTRING Function extracts a specified portion of the character string on which it is operating. The following is the SUBSTRING Function syntax:

SUBSTRING (string_value FROM start [FOR length])

In the above 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.

Examples

SUBSTRING('George Valentie' FROM 3) ----> 'orge Valentine'
SUBSTRING('George Valentie' FROM 3 FOR 2) ----> 'or'

CHARACTER_LENGTH

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:

    1. CHARACTER_LENGTH (string_value)
    2. CHAR_LENGTH (string_value).

Examples

CHAR_LENGTH('George Valentine') ----> 16
CHARACTER_LENGTH('$150') ----> 4

POSITION

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:

POSITION (string_pattern IN string_value)

Examples

POSITION(`Valentine' IN `George Valentine') ----> 8
POSITION(`' IN `George Valentine') ----> 1

TRIM

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:

TRIM (LEADING | TRAILING | BOTH 'character' FROM string_value)

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.

Examples

TRIM (LEADING ` ` FROM ` George Valentine `)
----> 'George Valentine '

TRIM (TRAILING ` ` FROM ` George Valentine `)
----> ` George Valentine `

TRIM (BOTH ` ` FROM ` George Valentine `)
----> `George Valentine'

TRIM (LEADING `$' FROM `$150')
----> `150'

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:

UPPER(string_value)
LOWER(string_value)

Examples

LOWER('George Valentine') ----> 'george valentine'
UPPER('George Valentine') ----> 'GEORGE VALENTINE'

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.

CURRENT_DATE

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.

Example

UPDATE order_tbl SET shipping_date = CURRENT_DATE

If the current date is April 4, 1998, the CURRENT_DATE Function returns: 1998-04-04.

CURRENT_TIME

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.

Example

if the current time is exactly 9:00 AM, the CURRENT_TIME Function returns: 09:00:00.

CURRENT_TIMESTAMP

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.

Example

UPDATE order_tbl SET delivery_datetime = CURRENT_DATE

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.

EXTRACT

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.

EXTRACT (extract_field FROM datetime_value)

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.

Examples

EXTRACT(YEAR FROM 1998-04-01) ----> 1998
EXTRACT(MONTH FROM 1998-04-01) ----> 04
EXTRACT(DAY FROM 1998-04-01 09:00:00) ----> 01
EXTRACT(HOUR FROM 1998-04-01 09:00:00) ----> 09
EXTRACT(MINUTE FROM 09:00:00) ----> 00
EXTRACT(SECOND FROM 09:00:00) ----> 00

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.

CAST (value AS datatype)

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

 

C

VC

B

I

SI

DEC

N

R

F

DB

D

T

TS

BB

CB

CHARACTER (C)

 Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
N
Y

VARCHAR (VC)

Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
N
Y

BOOLEAN (B)

Y
Y
Y
N
N
N
N
N
N
N
N
N
N
N
N

INTEGER (I)

Y
Y
N
Y
Y
Y
Y
Y
Y
Y
N
N
N
N
N

SMALLINT (SI)

Y
Y
N
Y
 Y
Y
Y
Y
Y
Y
N
N
N
N
N

DECIMAL (DEC)

Y
Y
N
Y
Y
Y
Y
Y
Y
Y
N
N
N
N
N

NUMERIC (N)

Y
Y
N
Y
Y
Y
 Y
Y
Y
Y
N
N
N
N
N

REAL (R)

Y
Y
N
Y
Y
Y
Y
 Y
Y
Y
N
N
N
N
N

FLOAT (F)

Y
Y
N
Y
Y
Y
Y
Y
Y 
Y
N
N
N
N
N

DOUBLE (DB)

Y
Y
N
Y
Y
Y
Y
Y
Y
Y
N
N
N
N
N

DATE (D)

Y
Y
N
N
N
N
N
N
N
N
 Y
N
Y
N
N

TIME (T)

Y
Y
N
N
N
N
N
N
N
N
N
Y
Y
N
N

TIMESTAMP (TS)

Y
Y
N
N
N
N
N
N
N
N
Y
Y
Y
N
N

BLOB (BB)

N
N
N
N
N
N
N
N
N
N
N
N
N
Y
N

CLOB (CB)

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( [ SQL_argument_list ] )

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

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.

Examples

DateConvert('01-02-1993')

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.

AVG

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.

AVG (column_name)

Example

SELECT AVG(commission_rate) FROM sales_rep_tbl

COUNT

The COUNT Function returns the number of rows in a specified result set. The following syntax is one form of the COUNT Function:

COUNT(*)

Example

SELECT COUNT(*) FROM sales_rep_tbl

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.

COUNT(DISTINCT column_name)

MAX

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.

MAX(column_name)

Example

SELECT MAX(commission_rate) FROM sales_rep_tbl

MIN

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.

MIN(column_name)

Example

SELECT MIN(commission_rate) FROM sales_rep_tbl

SUM

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.

SUM(column_name)

Example

SELECT SUM(ytd_sales) FROM sales_rep_tbl

SQL Special Registers

The PointBase RDBMS supports the following list as special registers. These can be used anywhere a scalar/value expression is allowed.

 
TOC PREV NEXT