Creating a function using a WITH clause
The WITH clause can be used to create a custom function with parameters.
This feature is available in Talend Data Mapper when you have installed Talend Studio R2025-03 or a later version.
The parameterized WITH clause should be structured as
follows:
WITH functionName(argument_1, argument_2, ...) = expression/block/sub-queryFor example, you can create a function containing an expression to check if a number is
between two values, and use that function in a WHERE
clause:
WITH isBetween(val, min, max, exclusive) =
IF (exclusive)
hasValue(val) && val > min && val < max
ELSE
hasValue(val) && val >= min && val <= max
FROM customer
WHERE isBetween(rating, 500, 700, false)
SELECT {
name,
rating
}The following example creates a function containing a block that extracts a first name and
last name from a string:
WITH extractNames(fullname) = {
LET names = split(fullname, ' '),
firstname = names[0],
lastname = names[1]
}
FROM customer AS c
SELECT extractNames(c.fullname)The following example uses a sub-query to iterate on a list of
items:
WITH collectItemsIds(items) = (
FROM items AS i
SELECT i.itemno
)
FROM orders AS o
SELECT {
id = o.orderno,
itemIds = collectItemsIds(o.items)
}The parameterized WITH clause supports recursion. You can call
a function created with a WITH clause in the function itself. For
example:
WITH factorial(n) = IF (n > 1) n * factorial(n-1) ELSE 1
SELECT factorial(10) Information noteImportant: The name of the custom function is case insensitive. For example, the
following script is not accepted:
WITH maxi(a,b) = max(a,b)
WITH Maxi(a,b,c) = max(a,b,c)
FROM customer
SELECT { max1 = maxi(x,y), max2 = Maxi(x,y,z) }Information noteRestriction:
- A parameterized WITH clause can only be defined at the beginning of the main script.
- A parameterized WITH clause cannot use an aggregation function.
- The INDEX clause cannot be used in a recursive parameterized WITH clause.