Using the UNION ALL clause
The UNION ALL clause allows you to build a single array based on the results of multiple query blocks or sub-queries.
In Talend Data Mapper with the 8.0.1-R2024-05 monthly update or a later one, you can also use UNION ALL ENTRY to create an array of record with key/value pairs from multiple sub-queries.
A query with a UNION ALL clause to combine the results of sub-queries in
an array should be formatted as follows:This syntax is used in the following example: Merging two arrays based on conditions.
SELECT [
<sub-query>
UNION ALL
<sub-query>
]A query with a UNION ALL ENTRY clause to combine the results of
sub-queries in an array of records should be formatted as follows:
SELECT [
{ key = '<name_of_key>', value = <value_expression> }
UNION ALL ENTRY
{ key = '<name_of_key>', value = <value_expression> }
]For example:
FROM customer
SELECT [
{ key = 'id', value = custid }
UNION ALL ENTRY
{ key = 'name', value = name }
UNION ALL ENTRY
{ key = 'address', value = concatWith(', ', address.street, address.city) }
]A query with a UNION ALL clause to combine the results of query blocks in
an array should be formatted as follows:
<query_block>
UNION ALL
<query_block>You can use conditions in a UNION ALL clause. For
example:
FROM order
LET $items_size = size(items)
WHERE $items_size > 2
SELECT [
IF ($items_size > 2) ( FROM items INDEX $i SELECT ENTRY { key = concat('itemno_', $i + 1), value = itemno } )
UNION ALL ENTRY
IF ($items_size > 2) ( FROM items INDEX $j SELECT ENTRY { key = concat('qty_', $j + 1), value = qty } )
UNION ALL ENTRY
IF ($items_size > 2) ( FROM items INDEX $k SELECT ENTRY { key = concat('price_', $k + 1), value = price } )
]