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