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.
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 finalSumThe output of the Results statement contains only two attributes, min_x and finalSum. The
LET
-bound attribute intermediateSum does not appear in the output.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
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
LET
is in scope:
LET
definitions in the same statementSELECT
definitions in the same statementGROUP 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
.
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.
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.