ADQL uses two primary categories of expressions in ADQL queries:
- Field expressions used in the SELECT clause
- Condition expressions used in the WHERE clause
To build an expression, use field names, functions, numbers, and operators indicated in the described syntax.
Syntax of field_expression (SELECT clause)
- {field_name | function | numeric_literal}
- (expression): surround the expression with parentheses to denote the order of operation in the query
- Math expressions such as:
- unary_operator expression
- expression binary_operator expression
A condition_expression such as [NOT] expression COMPARISON_OPERATOR value can also be an alias used in a field_expression in the SELECT clause.
For example:SELECT field_name AS myField... WHERE myField > 2
Syntax of condition_expression (WHERE clause)
- [NOT] expression COMPARISON_OPERATOR value
- (condition_expression)
- condition_expression LOGICAL_OPERATOR condition_expression
The field expression syntax in the SELECT clause can also contain functions. The syntax of a function_expression is: function(field_name, arguments, ...)
| Syntax Element | Description |
|---|---|
| field_name or numeric_field_name | The name of a field in the specified event type. Field names require back quotes when the name contains spaces or special characters. For example, the field name, `full name`, is surrounded by back quotes because it contains a space. See Special Characters for the full list. Use the Events Service internal names in your ADQL queries. The list of default fields and their internal names are described under ADQL Data. |
| function | See Analytics Functions. |
| numeric_literal | A sequence of digits, optionally including a decimal point ". " or sign {+ | -}. Example: 2, -4, 3.14 |
| unary_operator | The - unary operator negates the value of the operand. |
| binary_operator | Arithmetic operators: {*, /, %, +, -}. See Math Expressions. |
| COMPARISON_OPERATOR | A set of operators that compare values. See Comparison Operators for the full list. |
| LOGICAL_OPERATOR | See Logical Operators. |
| value | The syntax for a value = {numeric_literal | string | boolean}. Values are text, numbers, dates or boolean values used to compare with the value in the specified field_name. The data type of the value must match the type of the specified field. Text strings require quotes. Numbers, dates, boolean values (true or false) and null do not need quotes. Quotes can be either single or double quotes. |
Instead of specifying that the expression applies to a particular array element, the expression can apply to any array element.
Fields with Spaces or Special Characters
If the Events Service internal name contains a special character, then the name must be surrounded by back quotes when it is used in a field expression in the WHERE clause. For a field name such as segments.httpData.headers.User-Agent, the ADQL query must use segments.httpData.headers.`User-Agent`.
For example:
SELECT segments.httpData.headers.`User-Agent`, count(segments.httpData.headers.`User-Agent`) FROM transactions WHERE segments.httpData.url = 'http://localhost:29990/packages'
The most likely situation in which spaces and other special characters may be used in naming are when field names are added with data collectors.
Special Characters
Special characters in ADQL are:
- equals : '='
- not equals: '<>' or '!='
- less than: '<'
- less than or equals: '<='
- greater than: '>'
- greater than or equals: '>='
- plus: '+'
- minus: '-'
- asterisk: '*'
- slash: '/'
- percent: '%'
- concatenation: '||'
- question mark: '?'
- open parens: '('
- open square bracket/brace: '['
- close parens: ')'
- close square bracket/brace: ']'
- double quotes: '""'
- underscore: '_'
Add Comment