Using the SET clause
The SET clause is used to define a variable that can be reused and
modified later in the query.
The SET clause should be structured as
follows:
SET $variable_name = expressionOnce a variable has been defined, you can use the same syntax to change its value.
The variable must start with the $ character and cannot be a hierarchical identifier, for example, $item.index is not a valid variable. The variable must be unique throughout the query. The expression used to specify the value of the variable can be a simple expression or a conditional expression. In Talend Data Mapper, it can also be an array.
The SET clause must be used after a FROM,
UNNEST, JOIN or GROUP BY clause, or in
a query block introduced by a SELECT clause. In Talend Data Mapper, it can also be used after a UNION ALL clause. If the query block
contains a WHERE or HAVING clause, the
SET should be placed before it. For example:
FROM customer
SET $address = concatWith(" ", address.street, address.city)
WHERE hasValue(rating)
SELECT {
name,
rating,
address = $address,
SET $level = if (rating > 650) "Premium" else "Standard",
level = $level
}Information noteNote: If a SET clause is used in a SELECT clause:
- The variable is only available in that block and cannot be used elsewhere in the query.
- Aggregation functions are not supported.