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, a conditional expression, or an array.

The WITH clause must be used at the beginning of a query or sub-query, before a FROM-WHERE-SELECT clause, or after a UNION ALL clause. In Talend Data Mapper with the 8.0.1-R2024-11 monthly update or a later one, you can also use a WITH clause at the beginning of a query block. For example:
WITH $limit_date = toDate('2020-05-21')
FROM order AS o
WHERE toDate(o.order_date) >= $limit_date
SELECT {
   items = (
      WITH $limit_price = 1000
      FROM o.items AS i
      LET $total_price = i.qty * i.price
      WHERE $total_price >= $limit_price
      SELECT {
        o.order_id,
        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 – please let us know!