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.