FOREACH function

The syntax of the FOREACH function is:

FOREACH id_1 IN set_1[, id_n IN set_n] RETURN(expression)

where:

• id is an arbitrary identifier for the item to be computed. The identifier must use the NCName format.
• set is a set of any set data type.
• expression is an EQL expression. The expression must be enclosed within parentheses and the RETURN keyword is required. The function must have only one RETURN regardless of the number of id parameters are used.

As with the EVERY and SOME quantifiers, EQL does not allow references to these bound variables to be qualified with data-source aliases. For example, in this statement:

RETURN results AS
  SELECT
    Source.x AS x,
    FOREACH x IN {1, 2}, y in {3, 4} RETURN (results.x + Source.y) AS vals
  FROM Source

EQL interprets the reference to results.x (in the FOREACH RETURN expression) as a reference to the x defined by the SELECT clause (that is, as an alias for Source.x and not as a reference to the x bound by FOREACH). Similarly, EQL interprets the reference Source.y as a reference to the attribute y in Source.

However, if you drop the statement qualifiers, as in the following:

RETURN results AS
  SELECT
    Source.x AS x,
    FOREACH x IN {1, 2}, y in {3, 4} RETURN (x + y) AS vals
  FROM Source

then EQL interprets x and y (in the FOREACH RETURN expression) as references to the FOREACH-bound identifiers, even though x and y are already in scope from the earlier SELECT and from the data source. Therefore, vals always has the value {4, 5, 6}.

In the FOREACH syntax, set_1 through set_n must be a set data type (such as mdex:string-set); EQL signals an error otherwise. The corresponding x_1 through x_n identifiers must have the type of the elements of these sets. To illustrate, consider this example:

FOREACH x IN {1, 2}, y IN {'abc', 'def'} RETURN (x + y)

Here, the first universe is a set of integers, and thus x has type integer within the RETURN expression. Similarly, the second universe is a set of strings, so y has type string. As a result, the RETURN expression x + y is thus ill-typed (and EQL signals an error accordingly).

FOREACH expressions may appear in both pre-grouping and post-grouping computation (including WHERE, HAVING, and ORDER BY clauses). They interact with aggregations in much the same way that quantifier expressions (EVERY and SOME) do:

• FOREACH expressions can appear inside aggregator arguments:

  RETURN results AS
    SELECT
      SET_UNIONS(FOREACH x IN e RETURN(b)) AS unions
    FROM Source
    GROUP
  

• An aggregator must not appear between a FOREACH and its bound variable. That is, the following is invalid, and EQL signals an error accordingly:

  FOREACH x IN e RETURN(SUM(x))
  

To explain how FOREACH works, we start with a simple example:

FOREACH x IN {1, 2, 3} RETURN(x * 2 + 1)

You can also use FOREACH to iterate over multiple sets:

FOREACH x IN {1, 2, 3}, y IN {40, 50, 60} RETURN(x + y)

This expression evaluates to the set {41, 42, 43, 51, 52, 53, 61, 62, 63}. Here, EQL evaluates the RETURN expression x + y for all possible combinations of values x and y from the two sets:

x = 1, y = 40: x + y = 41
x = 2, y = 40: x + y = 42
x = 3, y = 40: x + y = 43
x = 1, y = 50: x + y = 51
...
x = 3, y = 60: x + y = 63

Because sets cannot contain duplicate values or NULLs, the result of a FOREACH expression may be smaller than the universe (or the cross product of the universes), as illustrated by these two examples:

FOREACH x IN {-1, 1, 2}      RETURN (ABS(x))        // returns {1, 2}
FOREACH x IN {'3', '4', 'y'} RETURN (TO_STRING(x)) // returns {3, 4}

In the first example, ABS(-1) = ABS(1) = 1, so the final set contains only two elements; the value 1 may not appear twice. In the second example, TO_INTEGER('x') is NULL, so this value does not appear in the final set.

If the RETURN expression itself produces a set (rather than a single value), then FOREACH evaluates as described above, but computes the union of all of the RETURN sets as the final result. For example:

FOREACH x in {3, 4, 5}, y IN {-1, 0, 2} RETURN ({x + y, x - y})

evaluates to the set {1, 2, 3, 4, 5, 6, 7}, as follows:

// Note: "body" is the RETURN expression
| x | y  | body  |
------------------
| 3 | -1 | {2,4} |
| 3 |  0 | {3}   |
| 3 |  2 | {1,5} |
| 4 | -1 | {3,5} |
| 4 |  0 | {4}   |
| 4 |  2 | {2,6} |
| 5 | -1 | {4,6} |
| 5 |  0 | {5}   |
| 5 |  2 | {3,7} |
------------------

An important corollary of the above description is that if any of the universe sets are empty, then the result set is itself empty. For example, given two multi-assign attributes whose assigned values are:

| xs    | ys    |
-----------------
| {1,2} | {3}   |
| {}    | {4,5} |
| {6}   | {}    |
-----------------

then the statement:

RETURN results AS
  SELECT
    xs,
    ys,
    FOREACH x IN xs, y IN ys RETURN (x + y) AS zs
  FROM InputState

produces the following results:

| xs    | ys    | zs    |
-------------------------
| {1,2} | {3}   | {4,5} |
| {}    | {4,5} | {}    |
| {6}   | {}    | {}    |
-------------------------

Example 1: This is one of the simplest examples of a FOREACH expression, as only one multi-assign integer attribute (Score) is used and the RETURN expression just returns the values of each Score set:

RETURN Results AS
SELECT
   WineID AS id,
   FOREACH x IN Score RETURN(x) AS ratings
FROM WineState
ORDER BY id

Example 2: This example uses two multi-assign string attributes (Body and Flavors) and concatenates the members of the sets:

RETURN Results AS
SELECT
   WineID AS id,
   FOREACH x IN Body, y IN Flavors RETURN (CONCAT(x, ' ', y)) AS bodyflavor
FROM WineState
WHERE IS_NOT_EMPTY(Body) AND IS_NOT_EMPTY(Flavors)
ORDER BY id

Example 3: This example uses both LET and FOREACH, and also uses the EXTRACT function in the RETURN expression:

RETURN Results AS
LET 
   (FOREACH d IN ShipDate RETURN (EXTRACT(d, YEAR))) AS yearSet
SELECT
   SET(Price) AS prices
FROM WineState
GROUP BY MEMBERS(yearSet) AS shipyear