WITH Clause
Description
The WITH clause allows you to define a temporary list of REGISTER and EXTEND statements that can be referenced in the current query scope.
The WITH clause can be used to define reusable aliases for tables, expressions, and columns, which should only be valid inside the scope in which the WITH clause is put. In other words, the WITH clause contains a comma-separated list of REGISTER or EXTEND statements, which are valid in the immediate next expression and its sub-expressions that follow this list. Note that it might not be clear what constitutes the immediate next expression so we recommend making this explicit via parenthesis as illustrated in the examples below.
Syntax
WITH [ with_clause_expression1, ..., with_clause_expressionN ]
with_clause_expression: Can be either a REGISTER or EXTEND expression.
REGISTER
Table alias
REGISTER "name" AS table
This syntax registers the specified table name as an alias for the provided table. The table alias can be used as if it was a normal table, at all places in the current query scope where a table name is expected. The aliased table can be a table from the Data Model, or a table created by other PQL functions, like a DOMAIN_TABLE, CASE_TABLE, COMMON_TABLE, or ACTIVITY_TABLE.
It is not possible to register an alias that is already a valid table name, for example if a table with that name already exists in the Data Model.
Column and condition alias (PQL variables)
REGISTER @name AS table.column
REGISTER @name AS condition
This syntax registers the specified expression under the given name. We name this feature "PQL variables". The aliased expression can be a column, something that returns a column, or a condition. The variable name needs to start with an @ character. The aliased expression can be called using that name at all places in the current query scope where the aliased expression is allowed.
Tables cannot be aliased with this syntax - for that, please use the table alias syntax described above.
It is possible to overwrite an already registered variable name with another REGISTER statement.
EXTEND
With EXTEND, new columns can be temporarily (i.e., for the current query scope) added to existing tables. EXTEND expressions help to reduce the duplication in PQL queries.
EXTEND "target_table" WITH "new_name" AS definition expression
After a column has been defined, referencing "target_table"."new_name" in any subsequent expression is treated as if the definition expression was directly used instead.
Aggregations
Aggregations are treated specially inside the EXTEND expression. Since EXTEND adds new columns to existing tables, standard aggregations are impossible by definition (because the grouper columns are not known yet). PQL solves this by providing PU-functions, which make it possible to define nested aggregations. However, in the context of EXTEND, this creates some redundancy. Consider the following example:
EXTEND "CaseTable" WITH "ActivityCount" AS PU_COUNT("CaseTable", "Activities"."Activity") Notice how "CaseTable" appears twice in this statement. To avoid this redundancy, EXTEND allows using standard aggregations inside the definition expression. These aggregations are executed as if a PU-function with the same target_table was used instead. Therefore, given the example from above, the equivalent EXTEND statement looks like this:
EXTEND "CaseTable" WITH "ActivityCount" AS COUNT("Activities"."Activity") Only a single standard aggregation is allowed inside EXTEND. More than one aggregation can be achieved with a second EXTEND definition or with a PU-function inside the definition.
Examples
[1] In this example, a | |||||||||||||||||||
| |||||||||||||||||||
|
[2] In this example, a PQL variable is defined in both dimensions using a | |||||||||||||||||||
| |||||||||||||||||||
|
[3] The | ||||||||||||
| ||||||||||||
|
[4] In this example, a | ||||||||||||||
| ||||||||||||||
|