Faq/Base/HSQLFunctions

Overview
The HSQLdb database engine included with Base supports the following functions and stored procedures for use within queries.

The Query component in Base allows for SQL commands that return a result set, i.e. SELECT and CALL.

The component supports two views:
 * GUI Query Designer (Designer View)
 * Text editor (SQL View)

Queries created and executed in Designer and SQL View support the use of named replaceable parameters in these statements, in most cases. Use of this feature is controlled by the option 'Escape Processing'.

Escape Processing is always ENABLED when using Designer View.

Escape Processing is ENABLED by default in SQL View, but may be DISABLED by selecting the 'Run SQL directly' tool button.

The SQL window in Base (opened with the menu commands TOOLS > SQL) allows any valid SQL statement to be executed, but it does not return any data results to the user. Statements executed in this window serve two main purposes. Multiple statements may be executed as a batch by ending each statement with a semicolon.
 * Data Manipulation Language ( DML ) statements. i.e. UPDATE, DELETE, INSERT.
 * Data Definition Language ( DDL ) statements. i.e. CREATE TABLE, ALTER TABLE, etc.

All functions listed below are available for use in the SQL window.

Each table below is formatted as follows:
 * Function Name — this is the actual function, along with required or optional parameters
 * Designer — Yes in this column means it may be used in Designer View. No, the function must be used in SQL View.
 * Esc Proc — Yes in this column means it may be used in SQL View, with escape processing ENABLED. No, the function is only available in SQL View with escape processing DISABLED.
 * Comments — A brief description of what the function does, the parameters expected and the type of output generated.

Aggregate Functions
Aggregate functions are used to perform some computation against group of values. Each field in a table can be thought of as one group of values. Use of the GROUP BY clause in a select statement allows the creation of multiple value groups.

Modifiers
You may want to modify the results returned by a query to either limit the number of records returned or you may want to only returns records with unique values. This is accomplished by using the SELECT statement modifiers LIMIT, TOP, or DISTINCT.

In the case of LIMIT and TOP, Base does not allow their use without turning 'Escape Processing' off first. This is done in the query design window by 1) Turn of the GUI designer 2) Select the "Run SQL Direct" toolbar button.

Example Data

Limit
The modifier LIMIT can be used in either of two ways:
 * Just after the word SELECT — When used, one must supply two parameters, "Starting Record" and "Number of rows", separated by a space.
 * At the end of a select statement, IF the statement contains a HAVING, GROUP BY or ORDER BY clause. Here one may supply one or two parameters. The "number of rows" and optionally the "Starting Record" is designated by the word OFFSET.

Examples:

This can be read as "return 2 rows, skipping the first 3 records in the result set".

The returned result set would be:

This example can be also be read as "return 2 rows, skipping the first 3 records of the result set after sorting by StartDate in descending order".

The returned result set would be:

Top
The modifier TOP is used to limit the number of records returned without an offset value. It must be included directly after the word SELECT and maybe used with any select statement.

The returned result set would be:

The returned result set would be:

Distinct
The modifier DISTINCT limits the returned to rows to only those for which every row contains unique entries.