TQL

• The LIKE statement can take regular expressions as well as the SQL wildcard characters. To switch from the normal SQL syntax (ie using the % and _ characters in the same way as * and ? are used in DOS) to using regular expressions, you must prefix string literals with the REGEX keyword.
• The Escape option is not supported; the escape character is always '\'.
• Where table-expressions are used in SQL, TQL takes this to be object-types.
• Object attribute references can be qualified with the 'BASELINE (enumerator)' phrase - this changes the meaning of the reference such that it refers to the given baseline of the object in question.
• The enumerator can be either the baseline date or the baseline ID. They can also be prefixed with the ALL, ANY or NONE keywords if the attribute is a collection rather than a simple attribute. For example, a bar might have several tasks and we are interested in bars where ALL its tasks start after 30/12/97:
  
  SELECT # FROM bar WHERE ALL bar.tasks IN ( SELECT # FROM task WHERE task.start > DATE '1997-12-30' )
• The meaning of an asterisk in the SELECT statement clause is changed to stand for the ID objects in the result set rather than all of the attributes of the objects in the result set.
• SQL has several in built functions: COUNT, MAX, MIN, SUM and AVG etc. Of these, only COUNT is supported by TQL. The COUNT function comes in two variations - COUNT (*) and COUNT (expr). In SQL, expr can be any scalar expression, but for the purposes of TQL, this can only be an object attribute. COUNT(*) returns the number of objects that pass the rest of the query, COUNT (attr) gives the count of the number of objects that pass the rest of the query and whose attr field is not empty/null/zero.
• Instead of the initial SELECT... part of the query, TEST (object_id_list) is legal. This indicates that the objects represented by the given IDs and type in the FROM clause is to be tested for conformance to the remainder of the query. This is in effect a filter.
• TQL also accepts a variety of date/time formats (including the use of Asta Powerproject's variable dates) in addition to the standard SQL DATE format. These are introduced by the DATE key word.
• TIMESTAMP is not supported.

All SQL reserved words are kept for compatibility.

In TQL most queries start with a SELECT statement, although in the case of filters, SELECT # is assumed when you enter a query manually in the Filter Properties dialog. Queries can also start with TEST (object id list).

The SELECT statement specifies the attribute(s) of the objects of interest that are to be returned to the caller. You can request more than one attribute and those values will be returned in the requested order for each object.

The general syntax is:

SELECT <attribute> FROM <object type>

The '*' in a SELECT statement is an alternative for 'object identifier'; it does not mean every attribute of an object, but the type and id of the object.

You can request more than one attribute at a time - use a comma separated list.

Where an attribute is the ID of another object, this can be prefixed with the keyword ASNAME, resulting in the name of the object being returned rather than its ID.

Notes

• You can only request multiple attributes in the first SELECT statement and not in any sub-query.
• # means the object's id only.

The WHERE clause forms the search conditions that are used to determine which objects of the request type to return. Where there is a WHERE clause, this always comes after the SELECT statement.

A WHERE clause is not required; it simply means that the attributes of all objects of the requested type will be returned. The objects that qualify for inclusion in the result set are precisely those which the WHERE clause as a whole evaluates to true.

The WHERE clause consists of any number of predicate expressions that can be combined with logical AND and OR operators to form more complex conditions. You can use several types of predicates.

This means that the value of the expression on the left must lie between those on the right of the BETWEEN keyword. The BETWEEN predicate has the general form:

expression [NOT] BETWEEN expression AND expression

The LIKE predicate is intended for string pattern matching. The given attribute of the object is compared with the string literal for a match. There is the option of using the standard SQL wildcards (% and _ ) or using regular expressions. The general form of the LIKE predicate is:

expression [NOT] LIKE [REGEX] string literal

The IN predicate tests the value of the expression on the left of the IN keyword for presence in the collection of values on the right. The collection can either be a comma-separated list of literal values or a sub-query. The general form is:

scalar expression [NOT] IN (value list)

Comparison operations come in two forms: quantified and unquantified. Both use the scalar comparison operators: < , >, <= , >= and <>.

Unquantified comparisons

The general form of an unquantified comparison is:

scalar expression OPERATOR scalar expression

where OPERATOR is as defined above.

Quantified comparisons

Quantified comparisons are similar to unquantified comparisons, but the scalar comparison operator is immediately followed by one of the ANY or ALL keywords, meaning that ANY or ALL of the comparisons respectively must match for the expression to evaluate to true. An unquantified comparison is effectively the same as the ANY quantified equivalent.

TQL can reference object fields where there is a 'many' relationship - the tasks that are on a bar are an example of this. You can use them in just the same way as any other field. In addition, you can reference a particular item in a many field using brackets.

SELECT # FROM bar where bar.tasks IN (SELECT # FROM task WHERE task.name = 'Fred')

This selects all the bars that have one or more tasks with the name Fred.

SELECT # FROM bar where bar.tasks[3] IN (SELECT # FROM task WHERE task.name = 'Fred')

This selects all the bars where task 3 has the name Fred.

Literals come in two varieties - string literals and numeric literals. A string literal is a sequence of characters enclosed in single quotes. Numeric literals are decimal numbers. Where the number is not an integer, the decimal separator MUST be a full point, not a comma.

The meanings of the symbols used in regular expressions are:

Symbol	Definition
^	Start of string
$	End of string
.	Any single character
\	Escape the special meaning of the following character

Regular expressions can contain bracketed expressions. These are regular expressions that match a single character. If the initial character in the bracketed expression is '^' then the bracketed expression is complemented, matching any character except for one in the brackets. If the first character after any potential circumflex is either a '-' or a closing bracket, then the expression shall match that character. A bracketed expression can contain a range of values, for example [a-z] matches any character between a and z. You can also use parentheses and the OR | operator to group expressions.

A regular expression can be followed by special characters having the following meanings:

Character	Meaning
regexp*	Matches a string of zero or more characters that would match regexp
regexp+	Matches a string of one or more characters that would match regexp
regexp?	Matches a string of zero or one character that would match regexp

Examples

Example	Description
[Hh]ello	Matches any string containing Hello or hello
^[Hh]ello	Matches any string that starts with Hello or hello

Are bars with IDs 1234 and 5678 both red?

TEST ALL ( bar.1234, bar.5678 ) FROM bar WHERE bar.colour = red

---

Are the objects with IDs 1234 and 5678 both bars?

TEST ALL (1234, 5678) FROM bar

---

Are either of the objects with IDs 1234 and 5678 bars?

TEST (1234, 5678) FROM bar

---

Bars where all the tasks start after 30/12/97

SELECT # FROM bar WHERE ALL bar.task IN (SELECT # FROM task WHERE task.start > DATE '1997-12-30')

---

Bars where all the tasks start after today's date

SELECT # FROM bar WHERE ALL bar.task IN (SELECT # FROM task WHERE task.start > DATE 'today')

Note that this example makes use of the variable dates feature.

---

Bars where any of the tasks start after 30/12/97

SELECT # FROM bar WHERE ANY bar.task IN (SELECT # FROM task WHERE task.start > DATE '1997-12-30')

---

Bars where any of the tasks start after the end of the current month

SELECT # FROM bar WHERE ANY bar.task IN (SELECT # FROM task WHERE task.start > DATE 'month end')

Note that this example makes use of the variable dates feature.

---

Bars where none of the tasks start after 30/12/97

SELECT # FROM bar WHERE NONE bar.task IN (SELECT # FROM task WHERE task.start > DATE '1997-12-30')

SELECT # FROM bar WHERE ALL bar.task IN (SELECT # FROM task WHERE task.start <= DATE '1997-12-30')

SELECT # FROM bar WHERE ALL bar.task NOT IN (SELECT # FROM task WHERE task.start > DATE '1997-12-30')

---

Tasks that start on or after today's date and finish before 01/01/09

SELECT # FROM task WHERE (task.start >= DATE 'today' AND task.finish < DATE '2009-01-01')

Note that this example makes use of the variable dates feature.

---

A mini link-report

SELECT objectid, ASNAME startTask, ASNAME endTask FROM link