AQL Syntax

Use the following syntax for AQL statements:

<AQLstatement>

SELECT [(<outputfields>)]

FROM (<from>)

[ WHERE (<filter>)]

[ <jointype> (<from>) [WHERE (<filter>)]]*

[ ORDERBY (<sortfields>)]

   

<outputfields>

[<alias>.]<* | <outputfield>[,<outputfield>]*

<outputfield>

[<alias>.]<field>

   

<from>

[<parentalias>.]<infoareaid>

[AS <alias>]

[LIMITED TO <number> ROWS]

[FLAGS <flags>]

[EXTENDED FLAGS <extendedflags>]

[USING LINK <linkid>]

   

<filter>

<filterexpression>[<booleanoperator> <filterexpression>]*

<filterexpression>

<filterfield> <comparisonoperator> < filterfield >

| < filterfield > <comparisonoperator> <filtervalue>

| (<filterexpression>)

<filterfield>

F<fieldid> | <fieldname>

<filtervalue>

<quotedstring> (for more information see below)

<booleanoperator>

AND | OR

<comparisonoperator>

= | < | <= | <> | >= | >

   

<sortfields>

<sortfield>[,<sortfield>]

<sortfield>

[<alias>.][<field>][ <sortorder>][ FLAGS <sortflags>]

<sortorder>

ascending|descending

   

<jointype>

WITH | WITHOUT | PLUS | WITHSUMMED | PLUSSUMMED | WITHOPTIONAL | WITHOUTOPTIONAL | WITHSUMMEDOPTIONAL | HAVING | HAVINGOPTIONAL

   

<field>

<fieldname>|<fieldid>

<fieldname>

<string> (the XML name)

<fieldid>

<number> (the unique field ID)

<infoareaid>

<string> (The info area ID in double quotes: "FI", "KP", etc.)

<alias>

<string> (for more information see below)

<parentalias>

<alias> (The alias of the parent info area, for joins only)

<linkid>

<number> (The link ID from the data model)
   

<quotedstring>

"<string>"

<flags>

<string> | <number> (for internal use ONLY)

<extendedflags>

<string> | <number> (for internal use ONLY)

<sortflags>

<string> | <number> (for internal use ONLY)

<alias> uniquely identifies an info area within the query. By default the alias and the info area ID ("FI", "KP", etc.) are the same. If an info area is used multiple times within a query, aliases can be defined using "AS <alias>" to keep those instances apart, >> Example Example Example on page 519.

<filtervalue> has to be formatted as usual (hence with all other query APIs). Use the following prefixes:

<sortflags> can currently not be saved in the CRM.Designer database.

Note: Notes: Line feeds in queries are for readability only, they have no semantic meaning.
Note: Keywords (SELECT, FROM, etc.) are case insensitive.