Skip to main content

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 = expression

Once 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.

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 – please let us know!