Advantage Developer Zone

 
 
 

Advantage Query Engine Functions

Tuesday, February 20, 2007

The Advantage SQL Engine has many useful functions built-in. This document covers mathematical, string, date/time, and a host of miscellaneous functions. These functions can be very useful for formatting and updating your data and obtaining useful information from the server for use within your application.

Aggregate Functions

Aggregate functions are used to run calculations on a set of records. These functions generally use a GROUP BY clause to organize the data to be aggregated into appropriate groups. Advantage supports the following aggregate functions; AVG, COUNT, MAX, MIN, SUM.

The following example shows the total number of orders and total sales by customer.

SELECT CustID, COUNT(OrderID) as "Orders", SUM(SubTotal) as "Total Sales", AVG(SubTotal) as "Average Sale" FROM Invoice GROUP BY 1

Mathematical Functions

Many standard mathematical functions are available in the Advantage Query Engine including conversion functions such as DEGREES and RADIANS which convert the given values. Many trigonometric functions such as SIN, COS, TAN and PI are also available.

The RAND function generates a random floating point value between 0 and 1 each time it is called. It can be initialized by passing in an integer value. It should only be initialized once per connection. It will use the system time as a seed value by default so there is generally no need to initialize the function. The following SQL statement will return 10 random customers from the customer table.

SELECT TOP 10 (RAND() * 1000) AS SortOrder, CustID, FirstName, LastName FROM Customer ORDER BY 1;

 

String Functions

Various character related functions are available and can be used to determe the length of a string such as; LENGTH, OCTET_LENGTH, BIT_LENGTH, CHAR_LENGTH and CHARACTER_LENGTH. These are very useful in SQL scripts where a specific length string is expected. String comparison functions, LOCATE and POSITION allow for finding a string within another string value.

Many functions exist which transform strings. Spaces can be removed by using the TRIM, RTRIM or LTRIM functions, conversely spaces can be added using the SPACE function. Case-insensitive searches can be accomplished on CHAR fields using the LCASE, LOWER, UCASE or UPPER functions. The CONCATENATE, REPEAT and REPLACE functions produce new string values based on the supplied values. Portions of a string can be retuned using the LEFT, RIGHT and SUBSTRING functions.

Date/Time Functions

Most applications have the need to store date and time information. This data is often used as conditions for reports and other business logic. For example, the date an invoice is paid is usually a critical item. The amount of time that has passed since an order was entered and shipped is a good measure of customer service. There are many date/time functions that assist with the manipulation of date/time values.

The DAY, HOUR, MINUTE, MONTH, SECOND, QUARTER, WEEK and YEAR functions extract a portion of the date, time or timestamp value. This information can be used very effectively in report generation. Allowing the sorting of the information by any one of these factors. The example SQL statement below shows a summary of sales by day for 2006.

SELECT SUM(SubTotal) as "Total Sales", DAYNAME(OrderDate) as "Day" FROM Invoice WHERE YEAR(OrderDate) = 2006 GROUP BY 2 ORDER BY 1 DESC

Manipulating date and time fields is relatively simple. Dates and times are stored as numbers within the database; therefore, simple math can be used to manipulate the value. However, if you need to add a specific value, 1 min 30 seconds for example, you can use the TIMESTAMPADD function. This function allows for adding the exact amount of time you wish. The interval can be in seconds, minutes, hours, days, weeks, months, quarters or years.

Determining how much time has passed between two dates is another important operation. This can be accomplished using the TIMESTAMPDIFF function. Like the TIMESTAMPADD function this function can determine the difference between two date, time or timestamp fields based on the same intervals mentioned above. The following SQL statement shows the average and maximum days between an order and the payment.

SELECT CustID, COUNT(OrderID) as "Orders", AVG(TIMESTAMPDIFF(SQL_TSI_DAY, OrderDate, PayDate))as "Average Days", MAX(TIMESTAMPDIFF(SQL_TSI_DAY, OrderDate, PayDate))as "Max Days" FROM Invoice GROUP BY 1

Miscellaneous Functions

Several other functions are available which do not fit into the categories listed above. The first set of these are administrative type functions. These include; APPLICATIONID, DATABASE, LASTAUTOINC, NEWIDSTRING and USER. The LASTAUTOINC function returns the last value assigned to an autoinc field. This is very useful when you must programmatically determine the value after an INSERT statement. The NEWIDSTRING returns a Globally Unique Identifier (GUID) in various formats. The example statement below will display all of the supported display formats. The screenshot shows two of the most commonly used GUID formats.

SELECT NEWIDSTRING("M") as "MIME", NEWIDSTRING("F") as "File",  NEWIDSTRING("N") as "Numbers", NEWIDSTRING("D") as "Delimited", NEWIDSTRING("B") as "Bracketed", NEWIDSTRING("P") as "Parenthesis" FROM system.iota

A variety of information can be obtained about the current connection using the other administrative functions. The following example SQL statement shows the current user, database and currently connected application. This functionality is very useful when creating an audit trail.

SELECT USER() as "User Name", DATABASE() as "Database",  APPLICATIONID() as "Application" FROM system.iota


There are several conditional type miscellaneous functions available as well. The IFNULL and ISNULL functions return a specified value when a NULL is encountered. COALESCE returns the first non-NULL expression result. Use COALESCE if you are evaluating more than one expression at a time. Remember that if any value in an expression is NULL, then the expression result is NULL.

Data types can be converted using the CAST or CONVERT SQL functions. Where CAST simply displays the data as the specified data type, CONVERT actually converts the data to the specified type. Both functions use the following data types; SQL_BINARY, SQL_VARBINARY, SQL_BIT (logical), SQL_CHAR, SQL_DATE, SQL_NUMERIC, SQL_DOUBLE, SQL_INTEGER, SQL_TIMESTAMP, SQL_TIME, or SQL_MONEY. Only SQL_BINARY, SQL_VARBINARY, SQL_VARCHAR, SQL_CHAR, and SQL_NUMERIC.

The CONTAINS, SCORE and SCOREDISTINCT functions are used with the Advantage Full Text Search (FTS) engine. For detailed information about using these functions and FTS refer to this tech-tip.

Summary

SQL is a powerful mechanism for retrieving and manipulating data. The Advantage Query Engine provides many useful functions to enhance this functionality. These functions are easy to implement and provide solutions to common SQL problems. For more detailed information consult the Advantage Help Files under the Advantage SQL Engine topic.