Group selection made easy, Outer joins made powerful
Site owned & produced by MagnaFacta

OR ELSE

The OR ELSE operator does the same for OR's as AND THEN does for AND: it changes the data in the default data source by limiting the amount of data in it. For filtering the data in the result set the OR ELSE operator works just like the normal OR operator. However for the filtering of the default data source the expression to the left of the OR ELSE operator is combined with any other filters on it using the AND operator.

All expressions that filter the default data source are combined using the AND operator. While there is no mathematical reason for this, the human reason is that otherwise it becomes hard to keep track of what is going on. Using the reused code only to filter down seems to me more like the natural language use of the words "and" and "or" in queries.

The query example

Using the OR ELSE operator allows us to simplify the writing of queries such as:

"show the apple sales with the pear sales for departments not selling apples"

Designing the query

This query consists of a filter statement for the apple sales combined with an OR with a filter for the pear sales for the other departments. This pear filter consists of a part filtering for pears sales AND a second part using a subquery to filter the sales of the departments that sell apples. As the WHERE of the subquery is a repeat of the first filter we should use the first filter to filter the default data source. For this we use the Else Logic OR ELSE operator.

The department subquery statement uses the same FROM as the main query, so we can leave this out. The SELECT statement also remains the same and is left out as well. Lastly the WHERE statement is just the filter added to the default data source by then OR ELSE operator. The conclusion is that all we need to filter out the right departments is an empty NOT IN () statement.

Experiments

To see how AND THEN combines with OR ELSE, copy in the Period BETWEEN '2009-01-01' AND '2009-01-31' at the beginning of the filter and combine it with the rest of the filter using AND THEN.

Do not forget that AND has a lower operator precedence than OR. Furthermore the Else Logic operators have a lower precedence that their normal counterparts and they are not cumulative. In "expr1 AND THEN expr2 AND THEN expr3" the default data source in expr3 is filtered by expr2 and not by "expr1 AND expr2". But the default data source in expr2 is filtered by expr1 so if expr2 uses the default data source expr1 will be applied to expr2 and that version of expr2 is the version that is applied to expr3. If you want to apply both expressions to expr3 use brackets: "(expr1 AND THEN expr2) AND THEN expr3" and "expr1 AND THEN (expr2 AND THEN expr3)" will both apply expr1 to the default data source of expr3.

In practice I always use brackets for all precedence determination and do not rely on the built-in precedence.




Conditions of use, Copyrights © & Intellectual Property Rights