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
}

Note: 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 WITH clause.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here