Up to now all the transformations did was reuse the FROM statement and the main query columns for the SELECT and GROUP BY statements. This does make the code shorter, but the next step in power is to enable the programmer change the data in the default data source, without repeating code. This we can do by reusing existing filter code in the WHERE statement.
To illustrate this we use the example query:
"show the sales in January of departments selling apples"
However this natural language version of the query is ambiguous. If we forget about the January filter in the second part of the query we get:
"show the sales in January of departments that have sold apples anywhere in the past"
When the January filter should be applied to the sub query as well the interpretation becomes:
"show the sales in January of departments selling apples in January"
Depending on the context both are valid interpretations that generate different answers. The reason for their difference is that natural languages often automatically assume that a previously stated filter is retained as some kind of a "current state of the filter". This current state does not affect later row filters, but it does make a difference when a later filter is a group selection or subquery.
Else Logic offers two different versions of the AND operator that reflect the difference between the previous interpretations. (And have nothing to do with multi valued logic at all.)
The current AND operator works as it always has done. The new AND THEN operator reuses the expression on its left side so that it not only filters the query result but also changes the "current state of the default data source" for the expression on the right side of the operator.
In the example below the query below before the AND THEN operator we have the default data source:
But after the operator the default data source becomes:
FROM SalesOverview WHERE Period BETWEEN '2009-01-01' AND '2009-01-31'
Check the difference by first running the code with AND THEN and a second time with THEN removed. This will result in both versions of the natural language query. If the difference is hard to see, set "Show changes" on and/or try to see it by opening two windows.