Skip to main content

Using the WITH clause

The WITH clause is used to define a variable that can be reused later in the query.
The WITH clause should be structured as follows:
WITH $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 WITH clause must be used at the beginning of a query or sub-query, before a FROM-WHERE-SELECT clause. In Talend Data Mapper, it can also be used after a UNION ALL clause. For example:
WITH $limit_date = toDate('2020-05-21')
FROM order AS o
WHERE toDate(o.order_date) >= $limit_date
   items = (
      WITH $limit_price = 1000
      FROM o.items AS i
      LET $total_price = i.qty * i.price
      WHERE $total_price >= $limit_price
      SELECT {
        total_price = $total_price

The WITH clause is evaluated only once per query, unlike the Using the LET clause 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!