ActiveSpaces software supports a restricted subset of the SQL query language.
To specify a query, supply a filter expression. For example:
column_name > 100
Queries return all columns of a table rather than a subset of specific columns. That is, all queries implicitly begin with SELECT * FROM table_name WHERE. Nonetheless, programs do not specify this string, they specify only the filter that would follow the WHERE keyword.
Filter expressions are not case sensitive. Query evaluation converts keywords and column names to lower case before evaluation.
Filter Expression Syntax Reference
Query filter expressions have the following form.
[ NOT ] column operator value { [ AND | OR ] [ NOT ] column operator value }*
The following sections describe further details of filter expression syntax and semantics.
Column
column can be the name of any column defined in the table.
Operator
operator can be any operator in the following table:
Operator Description
=
==
IS
!=
<>
IS NOT
>
<
>=
<=
ISNULL IS NULL
Tests that the row does not contain a value in this column.
Operator Description NOTNULL
NOT NULL IS NOT NULL
Tests that the row contains a value in this column.
BETWEEN value_1 and value_2 Requires two values, separated by the keyword and. The range includes the end values.
IN ( value [, value ]* ) Requires a set of values, separated by commas, surrounded by parentheses.
Value
value can be any value of the same data type as the column's data type.
Surround string values in single quote characters: for example, 'My Value'. Conjunctions
● AND joins multiple conditions. The overall condition is true if and only if every individual condition is true.
● OR joins multiple conditions. The overall condition is true if at least one of the individual conditions is true.
Negation
NOT reverses the boolean value of a logical expression that follows it. For example, you can use the operators NOT BETWEEN and NOT IN.
You can also precede an operator clause with NOT, for example:
NOT column operator value
Order of Operations
Order of operations is similar to SQL. NOT takes precedence over conjunctions. The conjunction AND takes precedence over OR.
You can use parentheses to group expressions, overriding that order.
Performance
See Efficiency of Filters.
Unsupported SQL Operators
TIBCO ActiveSpaces® does not support the following SQL operators and clauses.
Unsupported GLOB
UNIQUE
Unsupported
EXISTS ORDER BY LIMIT
Special Characters in Column Names
Column names with special characters require special treatment.
It is good practice for administrators to define column names that follow the SQL identifier rules. (See
"Column Names" in TIBCO ActiveSpaces Administration.)
Nonetheless, in some situations, a table might contain non-standard column names. For example, a table copied from a legacy data base might have columns with names that contain a space character.
If you must refer to non-standard column names in a filter expression, surround the column name with any of the following escape characters:
The efficient use of queries depends in part upon the way you construct filter expressions, and in part upon the way the administrator constructs table indexes. Programmers and administrators can use these rules of thumb to help promote efficiency and high performance.
Programmers: consult your data base administrator for information about the definition of indexes.
Keys and Indexes
Rule of Thumb: Construct filter expressions in which every conjunct refers to a key or index.
A filter expression that does not refer to a key or index results in a full table scan, which is inefficient.
A compound filter expression, which combines conjuncts using AND or OR, results in a full table scan for each conjunct that does not refer to a key or index.
Left-Most Columns
Rule of Thumb: Construct filter expressions that reference the left-most columns of an index using the predicates =, ==, <=, >=, <, >, IN, or BETWEEN.
When an index includes more than one column, the administrator has defined them in a specific order:
from left to right. Queries with filter expressions that refer to the left-most column of an index can be more efficient than filter expressions that skip the left-most column and instead refer only to columns to
its right. Similarly, queries with filter expressions that refer to the left-most two columns can be even more efficient. Queries can achieve maximum efficiency when they use filter expressions that refer to all the columns of an index.
In contrast, omitting the left-most column from the filter expression results in a full table scan, which is the least efficient.
The order in which the columns appear within the filter expression does not affect efficiency. Only the order of columns when defining the index matters.
Avoid Left-Most NOT
Rule of Thumb: Do not construct filter expressions that reference the left-most columns of an index using the predicates NOT, IS NOT, !=, <>, IS, ISNULL, IS NULL, NOTNULL, NOT NULL, and IS NOT NULL.
In contrast to the rule of left-most columns, filter expressions that reference the left-most columns with these operators have the opposite effect: to guarantee a full table scan, which is the least efficient.
The presence of the predicate IS in this list could be counterintuitive. See the next rule.
However, this rule does not imply that predicates in the NOT family are always inefficient. For example, a query can still be efficient if it obeys the left-most columns rule and also tests columns further to the right using NOT. For example, if the administrator defined an index on the columns
lastname and firstname, then this filter expression can be efficient:
lastname='Smith' and firstname IS NOT 'Dan'
Avoid IS
Rule of Thumb: Use the predicates = or ==, rather than IS.
Even though the predicates =, ==, and IS are semantic synonyms, the behavior of IS differs dramatically. Namely, IS guarantees a full table scan, which is the least efficient.
Bound Ranges from Both Ends
Rule of Thumb: When using the predicates > or >=, which specify a lower limit on a column's value, also include the opposite predicates, < or <=, to specify an upper limit on the same column.
A query searches an index from its lower limit to its upper limit. If you omit the upper limit, the query continues searching to the end of the index. If you omit the lower limit, the query begins with the first row of the index.