Using the LET clause
The LET clause is used to define a variable that can be reused later
in the query.
The LET clause should be structured as
follows:
LET $variable_name = expression
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 LET clause must be used after a FROM,
UNNEST, JOIN, GROUP BY or GROUP
AS clause or in a query block introduced by a SELECT clause. If
the query contains a WHERE or HAVING clause, the
LET should be placed before it. For example:
FROM customer
LET $address = concatWith(" ", address.street, address.city)
WHERE hasValue(rating)
SELECT {
name,
rating,
address = $address,
LET $level = if (rating > 650) "Premium" else "Standard",
level = $level
}Information noteNote: If a LET 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.
The LET clause is evaluated for each iteration in the query, unlike the Using the WITH clause clause.