awe Base Query Schema

Generated from query.xsd. It includes variable.xsd.

This Schema defines the way an awe Query must be structured

28 elements, 76 declared attributes (76 documented).

Elements

Elements: table, field, constant, operation, case, when, then, else, over, filter, and, or, left-operand, right-operand, query, computed, compound, where, having, join, union, totalize, totalize-by, totalize-field, group-by, partition-by, order-by, variable

table

Table, view or subquery the statement reads from or modifies.

Type: tableType

Contains: no child elements

Contained by: retrieve-data, multiple, delete, insert, update, query, join

Attribute	Type	Required	Default	Description
id	identifierType	no		Name of the database table or view.
schema	identifierType	no		Database schema (owner) of the table.
query	identifierType	no		Id of another query used as a subquery in place of a table.
alias	string	no		Alias of the table. Fields refer to it through their table attribute.

field

Column of a table, the result of a subquery, or the value of a variable.

Type: fieldType

Contains: no child elements

Contained by: retrieve-data, multiple, delete, insert, update, operation, then, else, over, left-operand, right-operand, query

Attribute	Type	Required	Default	Description
id	identifierType	no		Name of the table column. Ignored when query or variable is also defined.
table	identifierType	no		Table, or table alias, the column belongs to.
function	functionTypeEnum (21 values)	no		SQL function applied to the field. A value the engine does not support makes the query fail.
cast	INTEGER, LONG, FLOAT, DOUBLE, STRING	no		Casts the value to the given type. A value the engine does not support makes the query fail.
noprint	true, false	no		Hides the column from the output. The column is removed after the other processing steps (computed fields, transforms) have run. Default false.
transform	transformType (21 values)	no		Transformation applied to the value after it is retrieved, for example to format a date or a number.
pattern	string	no		Format pattern. For the NUMBER and NUMBER_PLAIN transforms it is a Java DecimalFormat pattern; for the ARRAY and LIST transforms it is the separator used to split the value.
translate	string	no		Id of an enumerated group. When the value matches one of its values, the localized label is output instead.
format-from	string	no		Source date format of the value. Used by the GENERIC_DATE transform.
format-to	string	no		Target date format of the value. Used by the GENERIC_DATE transform.
alias	identifierType	no		Name of the output column. Computed fields, totalize and screen components refer to the column by it. When omitted, the id is used.
audit	true, false	no		Only used in maintain operations. When true the column is written only to the audit table and not to the target table; when false it is written only to the target table and not audited; when omitted it is written to both. Ignored in queries.
key	true, false	no		Only used in maintain operations. Marks a key column. In a multiple maintain the key columns build the filter of each update and delete. In an insert, a key column with no sequence and no value is left out so the database generates it.
query	identifierType	no		Id of a query used as a subquery to obtain the field value. It takes precedence over variable and id.
variable	identifierType	no		Id of a variable of the query that supplies the field value. It takes precedence over id and table, and is ignored if query is also defined.
sequence	identifierType	no		Only used in maintain inserts. Name of an AWE sequence, stored in the key table, whose next value is written to the column.
auto-incremental	identifierType	no		Only used in maintain inserts. The column is generated by the database (identity or auto-increment): it is left out of the insert and the generated value is stored in the field variable.
optional	true, false	no		Only used in maintain operations. When true and the field variable is null or empty, the field is left out of the statement: an insert uses the column default and an update keeps the stored value. Default false.

constant

Static value used as a field or as an operand.

Type: constantType

Contains: no child elements

Contained by: retrieve-data, delete, insert, update, operation, then, else, over, left-operand, right-operand, query

Attribute	Type	Required	Default	Description
id	identifierType	no		Name of the table column. Ignored when query or variable is also defined.
table	identifierType	no		Table, or table alias, the column belongs to.
function	functionTypeEnum (21 values)	no		SQL function applied to the field. A value the engine does not support makes the query fail.
cast	INTEGER, LONG, FLOAT, DOUBLE, STRING	no		Casts the value to the given type. A value the engine does not support makes the query fail.
noprint	true, false	no		Hides the column from the output. The column is removed after the other processing steps (computed fields, transforms) have run. Default false.
transform	transformType (21 values)	no		Transformation applied to the value after it is retrieved, for example to format a date or a number.
pattern	string	no		Format pattern. For the NUMBER and NUMBER_PLAIN transforms it is a Java DecimalFormat pattern; for the ARRAY and LIST transforms it is the separator used to split the value.
translate	string	no		Id of an enumerated group. When the value matches one of its values, the localized label is output instead.
format-from	string	no		Source date format of the value. Used by the GENERIC_DATE transform.
format-to	string	no		Target date format of the value. Used by the GENERIC_DATE transform.
alias	identifierType	no		Name of the output column. Computed fields, totalize and screen components refer to the column by it. When omitted, the id is used.
audit	true, false	no		Only used in maintain operations. When true the column is written only to the audit table and not to the target table; when false it is written only to the target table and not audited; when omitted it is written to both. Ignored in queries.
key	true, false	no		Only used in maintain operations. Marks a key column. In a multiple maintain the key columns build the filter of each update and delete. In an insert, a key column with no sequence and no value is left out so the database generates it.
value	string	no		Static value of the constant.
type	variableRestriction (26 values)	no		Variable type used to convert the value. STRING when omitted.

operation

Operation between its nested operands, such as a concatenation, an arithmetic operation or a date calculation.

Type: operationType

Contains: field (0..n), constant (0..n), operation (0..n), case (0..n), over (0..n)

Contained by: retrieve-data, delete, insert, update, operation, then, else, over, left-operand, right-operand, query

Attribute	Type	Required	Default	Description
id	identifierType	no		Name of the table column. Ignored when query or variable is also defined.
table	identifierType	no		Table, or table alias, the column belongs to.
function	functionTypeEnum (21 values)	no		SQL function applied to the field. A value the engine does not support makes the query fail.
cast	INTEGER, LONG, FLOAT, DOUBLE, STRING	no		Casts the value to the given type. A value the engine does not support makes the query fail.
noprint	true, false	no		Hides the column from the output. The column is removed after the other processing steps (computed fields, transforms) have run. Default false.
transform	transformType (21 values)	no		Transformation applied to the value after it is retrieved, for example to format a date or a number.
pattern	string	no		Format pattern. For the NUMBER and NUMBER_PLAIN transforms it is a Java DecimalFormat pattern; for the ARRAY and LIST transforms it is the separator used to split the value.
translate	string	no		Id of an enumerated group. When the value matches one of its values, the localized label is output instead.
format-from	string	no		Source date format of the value. Used by the GENERIC_DATE transform.
format-to	string	no		Target date format of the value. Used by the GENERIC_DATE transform.
alias	identifierType	no		Name of the output column. Computed fields, totalize and screen components refer to the column by it. When omitted, the id is used.
audit	true, false	no		Only used in maintain operations. When true the column is written only to the audit table and not to the target table; when false it is written only to the target table and not audited; when omitted it is written to both. Ignored in queries.
key	true, false	no		Only used in maintain operations. Marks a key column. In a multiple maintain the key columns build the filter of each update and delete. In an insert, a key column with no sequence and no value is left out so the database generates it.
operator	operationRestriction (33 values)	yes		Operator applied to the operands, which are the nested fields, constants, operations or cases.

case

SQL CASE expression made of when clauses and an optional else.

Type: caseType

Contains: when (0..n), else (0..1)

Contained by: retrieve-data, operation, then, else, over, left-operand, right-operand, query, group-by, partition-by

Attribute	Type	Required	Default	Description
id	identifierType	no		Name of the table column. Ignored when query or variable is also defined.
table	identifierType	no		Table, or table alias, the column belongs to.
function	functionTypeEnum (21 values)	no		SQL function applied to the field. A value the engine does not support makes the query fail.
cast	INTEGER, LONG, FLOAT, DOUBLE, STRING	no		Casts the value to the given type. A value the engine does not support makes the query fail.
noprint	true, false	no		Hides the column from the output. The column is removed after the other processing steps (computed fields, transforms) have run. Default false.
transform	transformType (21 values)	no		Transformation applied to the value after it is retrieved, for example to format a date or a number.
pattern	string	no		Format pattern. For the NUMBER and NUMBER_PLAIN transforms it is a Java DecimalFormat pattern; for the ARRAY and LIST transforms it is the separator used to split the value.
translate	string	no		Id of an enumerated group. When the value matches one of its values, the localized label is output instead.
format-from	string	no		Source date format of the value. Used by the GENERIC_DATE transform.
format-to	string	no		Target date format of the value. Used by the GENERIC_DATE transform.
alias	identifierType	no		Name of the output column. Computed fields, totalize and screen components refer to the column by it. When omitted, the id is used.
audit	true, false	no		Only used in maintain operations. When true the column is written only to the audit table and not to the target table; when false it is written only to the target table and not audited; when omitted it is written to both. Ignored in queries.
key	true, false	no		Only used in maintain operations. Marks a key column. In a multiple maintain the key columns build the filter of each update and delete. In an insert, a key column with no sequence and no value is left out so the database generates it.

when

Condition of a case expression with the value returned when it holds. It has the filter attributes and may contain nested filters.

Type: caseWhenType

Contains: left-operand (0..1), right-operand (0..1), and (0..1), then (1)

Contained by: case

Attribute	Type	Required	Default	Description
left-field	identifierType	no		Column on the left side of the comparison.
left-table	identifierType	no		Table, or table alias, of the left field.
left-variable	identifierType	no		Id of the variable used as the left side of the comparison.
left-function	functionTypeEnum (21 values)	no		SQL function applied to the left side. See the function attribute of field.
right-field	identifierType	no		Column on the right side of the comparison.
right-table	identifierType	no		Table, or table alias, of the right field.
right-variable	identifierType	no		Id of the variable used as the right side of the comparison. A list variable produces an IN comparison.
right-function	functionTypeEnum (21 values)	no		SQL function applied to the right side. See the function attribute of field.
ignorecase	true, false	no		Compares without distinguishing upper and lower case. Applies to eq, ne, like and not like. Default false.
trim	true, false	no		Trims the field operands before comparing them. Default false.
optional	true, false	no		When true and the compared variable is null or empty, the filter is removed from the statement instead of comparing with null. Default false.
query	identifierType	no		Id of a query used as the right side of the comparison. Use it with in, not in, exists and not exists.
type	NUMBER, DECIMAL_NUMBER, DATE, TIME, STRING	no		Declared type of the compared values. The engine does not use it when it generates the SQL.
condition	conditionEnum (14 values)	no		Comparison applied between the left and the right side of the when clause. May be omitted when the clause uses nested filters.

then

Value returned by a when clause.

Type: operandType

Contains: field (0..1), constant (0..1), operation (0..1), case (0..1), over (0..1)

Contained by: when

Attributes: none

else

Value returned when no when clause holds.

Type: operandType

Contains: field (0..1), constant (0..1), operation (0..1), case (0..1), over (0..1)

Contained by: case

Attributes: none

over

Window function. It contains the field to compute and its partition-by and order-by clauses.

Type: overType

Contains: field (0..n), constant (0..n), operation (0..n), case (0..n), over (0..n), partition-by (0..n), order-by (0..n)

Contained by: retrieve-data, operation, then, else, over, left-operand, right-operand, query

Attribute	Type	Required	Default	Description
id	identifierType	no		Name of the table column. Ignored when query or variable is also defined.
table	identifierType	no		Table, or table alias, the column belongs to.
function	functionTypeEnum (21 values)	no		SQL function applied to the field. A value the engine does not support makes the query fail.
cast	INTEGER, LONG, FLOAT, DOUBLE, STRING	no		Casts the value to the given type. A value the engine does not support makes the query fail.
noprint	true, false	no		Hides the column from the output. The column is removed after the other processing steps (computed fields, transforms) have run. Default false.
transform	transformType (21 values)	no		Transformation applied to the value after it is retrieved, for example to format a date or a number.
pattern	string	no		Format pattern. For the NUMBER and NUMBER_PLAIN transforms it is a Java DecimalFormat pattern; for the ARRAY and LIST transforms it is the separator used to split the value.
translate	string	no		Id of an enumerated group. When the value matches one of its values, the localized label is output instead.
format-from	string	no		Source date format of the value. Used by the GENERIC_DATE transform.
format-to	string	no		Target date format of the value. Used by the GENERIC_DATE transform.
alias	identifierType	no		Name of the output column. Computed fields, totalize and screen components refer to the column by it. When omitted, the id is used.
audit	true, false	no		Only used in maintain operations. When true the column is written only to the audit table and not to the target table; when false it is written only to the target table and not audited; when omitted it is written to both. Ignored in queries.
key	true, false	no		Only used in maintain operations. Marks a key column. In a multiple maintain the key columns build the filter of each update and delete. In an insert, a key column with no sequence and no value is left out so the database generates it.

filter

Comparison between a left and a right operand, given as attributes or as left-operand and right-operand children.

Type: filterType

Contains: left-operand (0..1), right-operand (0..1)

Contained by: and, or, where, having

Attribute	Type	Required	Default	Description
left-field	identifierType	no		Column on the left side of the comparison.
left-table	identifierType	no		Table, or table alias, of the left field.
left-variable	identifierType	no		Id of the variable used as the left side of the comparison.
left-function	functionTypeEnum (21 values)	no		SQL function applied to the left side. See the function attribute of field.
right-field	identifierType	no		Column on the right side of the comparison.
right-table	identifierType	no		Table, or table alias, of the right field.
right-variable	identifierType	no		Id of the variable used as the right side of the comparison. A list variable produces an IN comparison.
right-function	functionTypeEnum (21 values)	no		SQL function applied to the right side. See the function attribute of field.
ignorecase	true, false	no		Compares without distinguishing upper and lower case. Applies to eq, ne, like and not like. Default false.
trim	true, false	no		Trims the field operands before comparing them. Default false.
optional	true, false	no		When true and the compared variable is null or empty, the filter is removed from the statement instead of comparing with null. Default false.
query	identifierType	no		Id of a query used as the right side of the comparison. Use it with in, not in, exists and not exists.
type	NUMBER, DECIMAL_NUMBER, DATE, TIME, STRING	no		Declared type of the compared values. The engine does not use it when it generates the SQL.
condition	conditionEnum (14 values)	yes		Comparison applied between the left and the right side.

and

Group of filters that must all hold.

Type: filterGroupType

Contains: filter (0..n), or (0..n), and (0..n)

Contained by: when, and, or, where, having, join

Attributes: none

or

Group of filters of which at least one must hold.

Type: filterGroupType

Contains: filter (0..n), or (0..n), and (0..n)

Contained by: and, or, where, having, join

Attributes: none

left-operand

Left side of a filter. It contains a field, constant, operation, case or over.

Type: operandType

Contains: field (0..1), constant (0..1), operation (0..1), case (0..1), over (0..1)

Contained by: when, filter

Attributes: none

right-operand

Right side of a filter. It contains a field, constant, operation, case or over.

Type: operandType

Contains: field (0..1), constant (0..1), operation (0..1), case (0..1), over (0..1)

Contained by: when, filter

Attributes: none

query

Query definition. It is resolved with SQL, a service, an enumerated group or a queue, depending on its attributes.

Type: queryType

Contains: table (0..n), computed (0..n), compound (0..n), field (0..n), constant (0..n), operation (0..n), case (0..n), over (0..n), join (0..n), union (0..n), variable (0..n), where (0..n), having (0..n), order-by (0..n), group-by (0..n), totalize (0..n)

Contained by: queries

Attribute	Type	Required	Default	Description
distinct	true, false	no		Returns only distinct rows (SELECT DISTINCT). Default false.
enumerated	identifierType	no		Id of an enumerated group whose values fill the query instead of a database table. Only used by enumerated queries.
service	identifierType	no		Id of the service, defined in the services file, that returns the data instead of a database. Only used by service queries.
queue	identifierType	no		Id of the JMS queue, defined in the queues file, used to obtain the data. Only used by queue queries.
cacheable	true, false	no		Caches the result using the query id plus the resolved request parameters as key, so the same call is not executed again during the cache lifetime. Default false.
managed-pagination	true, false	no		Controls how a SQL query is paginated. When true or omitted, the database returns only the requested page (limit and offset plus a count query), unless the query has a totalize element or the page size is 0. When false, all rows are fetched and the page is cut in memory. Not applied to totalized queries.
post-process	true, false	no		Only for service queries. When true (default) AWE processes the returned data list (transforms, translations, computed fields). Set it to false to keep the service result untouched.
public	true, false	no		Allows the query to be launched without an authenticated session. Default false.
id	identifierType	yes		Unique identifier of the query. Screens, services and other queries refer to the query by this id.

computed

Output column built after the SQL statement runs, by replacing placeholders in a format with values of the same row.

Type: computedType

Contains: no child elements

Contained by: retrieve-data, query, compound

Attribute	Type	Required	Default	Description
alias	string	yes		Name of the output column.
format	string	yes		Expression that builds the value. Each [name] placeholder is replaced by the value of the query variable or output column with that name.
eval	true, false	no		Evaluates the resulting text as a JavaScript expression. Default false.
nullValue	string	no		Value used for a placeholder whose column is null or empty. Blank by default.
transform	transformType (21 values)	no		Transformation applied to the computed value.
pattern	string	no		Format pattern for the computed value. See the pattern attribute of field.
translate	identifierType	no		Id of an enumerated group. When the computed value matches one of its values, the localized label is output instead.
label	string	no		Accepted by the schema and the model but not used when the computed value is generated.

compound

Output column that groups several computed elements into one structure.

Type: compoundType

Contains: computed (1..n)

Contained by: retrieve-data, query

Attribute	Type	Required	Default	Description
alias	string	yes		Name of the output column that holds the structure built from the nested computed elements. Must be unique in the query.

where

WHERE clause. It contains filter groups.

Type: whereType

Contains: filter (0..n), or (0..n), and (0..n)

Contained by: retrieve-data, delete, update, query

Attributes: none

having

HAVING clause. It contains filter groups evaluated after grouping.

Type: whereType

Contains: filter (0..n), or (0..n), and (0..n)

Contained by: retrieve-data, query

Attributes: none

join

SQL join with another table or subquery. It contains the table and the join condition.

Type: joinType

Contains: table (1), or (0..1), and (0..1)

Contained by: retrieve-data, query

Attribute	Type	Required	Default	Description
type	FULL, INNER, LEFT, RIGHT	no		Kind of SQL join. INNER when omitted.

union

Combines the result of this query with another query.

Type: unionType

Contains: no child elements

Contained by: retrieve-data, query

Attribute	Type	Required	Default	Description
query	identifierType	yes		Id of the query whose result is combined with this one.
type	ALL	no		Set to ALL to keep duplicate rows (UNION ALL). When omitted, duplicates are removed (UNION).

totalize

Adds total or subtotal rows to the result after the SQL statement runs.

Type: totalizeType

Contains: totalize-by (0..n), totalize-field (0..n)

Contained by: retrieve-data, query

Attribute	Type	Required	Default	Description
function	AVG, CNT, CNT_DISTINCT, MAX, MIN, SUM, FIRST_VALUE, LAST_VALUE	yes		Aggregation applied to every totalized field unless a totalize-field overrides it.
label	string	yes		Localization key of the text written in the total row.
field	identifierType	yes		Output column that receives the label in the total row.
style	TOTAL, SUBTOTAL	yes		Row style given to the total row, TOTAL or SUBTOTAL.

totalize-by

Column whose change of value starts a new totalized group.

Type: totalizeElementType

Contains: no child elements

Contained by: totalize

Attribute	Type	Required	Default	Description
field	identifierType	yes		Output column whose value change starts a new group; a total row is added after each group.

totalize-field

Column to totalize, optionally with its own function.

Type: totalizeFieldType

Contains: no child elements

Contained by: totalize

Attribute	Type	Required	Default	Description
field	identifierType	yes		Output column to totalize.
function	AVG, CNT, CNT_DISTINCT, MAX, MIN, SUM, FIRST_VALUE, LAST_VALUE	no		Aggregation applied to this column. When omitted, the function of the totalize element is used.

group-by

GROUP BY expression. It is a column or a nested case.

Type: groupByType

Contains: case (0..1)

Contained by: retrieve-data, query

Attribute	Type	Required	Default	Description
field	identifierType	no		Column to group by. May be omitted when a nested case provides the expression; the case takes precedence.
table	identifierType	no		Table, or table alias, of the column.
function	functionTypeEnum (21 values)	no		SQL function applied to the column before grouping.

partition-by

Partition of a window function. It is a column or a nested case.

Type: groupByType

Contains: case (0..1)

Contained by: over

Attribute	Type	Required	Default	Description
field	identifierType	no		Column to group by. May be omitted when a nested case provides the expression; the case takes precedence.
table	identifierType	no		Table, or table alias, of the column.
function	functionTypeEnum (21 values)	no		SQL function applied to the column before grouping.

order-by

ORDER BY expression.

Type: orderByType

Contains: no child elements

Contained by: retrieve-data, over, query

Attribute	Type	Required	Default	Description
field	identifierType	yes		Column to sort by.
table	identifierType	no		Table, or table alias, of the column.
function	functionTypeEnum (21 values)	no		SQL function applied to the column before sorting.
type	ASC, DESC	no		Sort direction. ASC when omitted.
nulls	FIRST, LAST	no		Position of null values, FIRST or LAST. When omitted the database default applies.

variable

Parameter of the query. Its value comes from the request, the session, a property or a static value.

Type: variableType

Contains: no child elements

Contained by: retrieve-data, multiple, serve, queue, delete, insert, update, include-target, query

Attribute	Type	Required	Default	Description
id	string	yes		Not documented yet
name	string	no		Not documented yet
value	string	no		Not documented yet
session	string	no		Not documented yet
property	string	no		Not documented yet
optional	string	no		Not documented yet
type	variableRestriction (26 values)	yes		Not documented yet

Simple types

identifierType

Identifier made of letters, digits, underscores and hyphens.

Base type: string

Pattern: ([0-9a-zA-Z_\-]+)

totalizeStyleType

Style of a total row. TOTAL is the final total; SUBTOTAL is the total of a group.

Base type: string

Allowed values: TOTAL, SUBTOTAL

joinElementType

Kind of join. INNER keeps rows with a match on both sides; LEFT keeps all rows of the left side; RIGHT keeps all rows of the right side; FULL keeps all rows of both sides.

Base type: string

Allowed values: FULL, INNER, LEFT, RIGHT

unionElementType

ALL keeps duplicate rows (UNION ALL).

Base type: string

Allowed values: ALL

transformType

Transformation applied to a value after it is retrieved. DATE, TIME and TIMESTAMP give web formats (dd/MM/yyyy, HH:mm:ss and dd/MM/yyyy HH:mm:ss); TIMESTAMP_MS adds milliseconds; DATE_MS is a date in milliseconds. JS_DATE and JS_TIMESTAMP give JavaScript date formats. ELAPSED_TIME turns milliseconds into a localized duration and DATE_SINCE gives the time elapsed since a date. NUMBER and NUMBER_PLAIN format a number with the pattern attribute, with and without thousands separator. BOOLEAN gives true or false. TEXT_HTML, TEXT_PLAIN and TEXT_UNILINE prepare text for HTML, plain text and a single line; MARKDOWN_HTML converts Markdown to HTML. DECRYPT decrypts an encrypted column. GENERIC_DATE converts a date between the formats of format-from and format-to, and DATE_RDB converts a dd-MMM-yyyy date to dd/MM/yyyy. ARRAY and LIST split the value with the separator in pattern, into an array and a list.

Base type: string

Allowed values: DATE, DATE_MS, TIME, TIMESTAMP, TIMESTAMP_MS, JS_DATE, JS_TIMESTAMP, ELAPSED_TIME, DATE_SINCE, NUMBER, NUMBER_PLAIN, BOOLEAN, TEXT_HTML, TEXT_UNILINE, TEXT_PLAIN, MARKDOWN_HTML, DECRYPT, GENERIC_DATE, DATE_RDB, ARRAY, LIST

conditionEnum

Comparison between two operands. eq, ne, ge, le, gt and lt mean equal, not equal, greater or equal, lower or equal, greater than and lower than. in and not in test membership in a list variable or a subquery; with a single value they behave as eq and ne. is null and is not null test the left operand only. like and not like use SQL patterns, and the STRINGL, STRINGR and STRINGB variable types add the wildcards. exists and not exists test whether a subquery returns rows.

Base type: string

Allowed values: eq, ne, ge, le, gt, lt, in, not in, is null, is not null, like, not like, exists, not exists

valueTypeEnum

Declared type of the values of a filter. The engine does not use it when it generates the SQL.

Base type: string

Allowed values: NUMBER, DECIMAL_NUMBER, DATE, TIME, STRING

functionTypeEnum

SQL function applied to a field. It accepts the aggregation functions and the field functions.

Allowed values: AVG, CNT, CNT_DISTINCT, MAX, MIN, SUM, FIRST_VALUE, LAST_VALUE, ABS, TRUNCDATE, ROW_NUMBER, RANK, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TRIM, LAG, LENGTH

castType

Type a value is cast to.

Base type: string

Allowed values: INTEGER, LONG, FLOAT, DOUBLE, STRING

fieldFunctions

Functions applied to a single value. ABS is the absolute value, TRUNCDATE drops the time of a date, TRIM removes the spaces at both ends, and YEAR, MONTH, DAY, HOUR, MINUTE and SECOND extract that part of a date. LENGTH gives the length of a string. ROW_NUMBER and RANK are window functions and LAG returns the previous row value.

Base type: string

Allowed values: ABS, TRUNCDATE, ROW_NUMBER, RANK, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TRIM, LAG, LENGTH

aggregationFunctions

Aggregation functions. AVG is the average, CNT counts values, CNT_DISTINCT counts distinct values, MAX and MIN give the greatest and lowest value, SUM adds values, FIRST_VALUE and LAST_VALUE give the first and last value of a window. They are the functions the totalize element accepts.

Base type: string

Allowed values: AVG, CNT, CNT_DISTINCT, MAX, MIN, SUM, FIRST_VALUE, LAST_VALUE

operationRestriction

Operator of an operation. CONCAT joins strings; REPLACE replaces the second operand with the third in the first; SUBSTRING extracts part of a string with 0-based indexes, begin inclusive and end exclusive; NULLIF returns null when the operands are equal; COALESCE returns the first operand that is not null. ADD, SUB, MULT, DIV and MOD are the arithmetic operators; POWER raises the first operand to the second; ROUND rounds with an optional number of decimals. ADD_, SUB_ and DIFF_ followed by SECONDS, MINUTES, HOURS, DAYS, WEEKS, MONTHS or YEARS add to a date, subtract from a date and give the difference between two dates in that unit.

Base type: string

Allowed values: CONCAT, REPLACE, SUBSTRING, NULLIF, COALESCE, ADD, SUB, MULT, DIV, MOD, POWER, ROUND, ADD_SECONDS, ADD_MINUTES, ADD_HOURS, ADD_DAYS, ADD_WEEKS, ADD_MONTHS, ADD_YEARS, SUB_SECONDS, SUB_MINUTES, SUB_HOURS, SUB_DAYS, SUB_WEEKS, SUB_MONTHS, SUB_YEARS, DIFF_SECONDS, DIFF_MINUTES, DIFF_HOURS, DIFF_DAYS, DIFF_WEEKS, DIFF_MONTHS, DIFF_YEARS

orderType

Sort direction. ASC is ascending and DESC is descending.

Base type: string

Allowed values: ASC, DESC

nullsType

Position of null values in a sort. FIRST puts them before other values and LAST after them.

Base type: string

Allowed values: FIRST, LAST