Using the WITH clause - Cloud

Talend Data Shaping Language Reference Guide

Version
Cloud
Language
English (United States)
Product
Talend Cloud
Module
Data Shaping Language
Content
Design and Development
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.

The WITH clause must be used at the beginning of a query or sub-query, before a FROM-WHERE-SELECT clause. 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 LET clause.