LET clause

The LET clause defines attributes that may be used elsewhere in the statement but do not necessarily appear in the statement's result.

The primary intent of LET is to make it easier to group by the value of a computed attribute (and especially to group by the MEMBERS of a computed attribute). However, LET can be used in any statement, grouping or not, to define temporary values of use elsewhere in the statement.

The syntax is:

LET <expression> [AS <attribute>][, <expression> [AS <attribute>]]*

LET may appear in any statement, immediately before the SELECT clause, as in this example:

RETURN Results AS
LET
   x + y AS intermediateSum
SELECT
   MIN(x) AS min_x,
   intermediateSum + z AS finalSum
FROM WineState
GROUP BY finalSum

The output of the Results statement contains only two attributes, min_x and finalSum. The LET-bound attribute intermediateSum does not appear in the output.

If present, LET must appear immediately before the SELECT clauses (as in the example above) and it must be followed by one or more attribute definitions, separated by commas. These attribute definitions look and act exactly like those that appear after SELECT. In particular, if the expression on the left-hand side of the definition is a bare attribute reference (optionally with data-source qualifier), then the AS clause of the definition is optional. That is, you may write:

LET x,
  State.y AS y,
  3 as z

which is equivalent to:

LET x AS x,
  State.y AS y,
  3 as z

LET attributes are computed immediately after the statement WHERE clause (if used) and before any of the SELECT attributes are computed.

Because LET attributes are computed before grouping, aggregators like AVG and SUM are illegal in LET clauses, and EQL signals an error if any appear in that context.

LET scoping

An attribute defined with LET is in scope:

for all following LET definitions in the same statement
for all SELECT definitions in the same statement
for the GROUP BY clause, including MEMBERS, in the same statement.

In addition, if a LET attribute is used as a grouping key, then it also appears in the statement's results and is available for use in ORDER BY and HAVING clauses. If the attribute is not a grouping key, then it is not in scope for ORDER BY or HAVING.

This example illustrates the LET scoping rules:

RETURN results AS
LET
  (FOREACH d IN orderDates RETURN (EXTRACT(d, YEAR))) AS orderYears
SELECT
  MAX(totalCost) AS maxCost
FROM OrderHistory
GROUP BY orderYears
HAVING 2014 IN orderYears

The example assumes that the data-source OrderHistory defines an attribute orderDates of type mdex:dateTime-set. The definition of the orderYears attribute extracts the year from each date in orderDates and then re-assembles these years into a set. The statement groups its results by the set of order years, computing the maximum cost for each, and returning rows for those orders that have at least one date in 2014 in orderDates. Because orderYears is a group key, the output table has two attributes (maxCost and orderYears), and orderYears is available for use in the HAVING clause.

As an alternative, consider this example:

RETURN results AS
LET
  (FOREACH d IN orderDates RETURN (EXTRACT(d, YEAR))) AS orderYears
SELECT
  MAX(totalCost) AS maxCost
FROM OrderHistory
GROUP BY MEMBERS(orderYears) AS yr

This statement is the same as the previous example, except that this statement groups not by orderYears but rather by its members. Therefore, orderYears is not a group key, but is merely used to compute the group key yr. Therefore, orderYears does not appear in the statement's output, and it cannot appear in either HAVING or ORDER BY clauses. (The statement's output contains two attributes, yr and maxCost.)

To summarize the rules given above: in a non-grouping statement, LET attributes never appear in the output, and they are never visible in HAVING and ORDER BY clauses.