Re: Box query


Bob Badour wrote:
Mikito Harakiri wrote:

Bob Badour wrote:

If you take a look at the second-to-last step, your query failed before
it got to the having clause:

Let tmp1 = { (box1,box2,exp(sum(ln(...)))) |
(1,1,6), (1,2,3e-8),
(2,1,3e-8), (2,2,6)
}

All right, let's make the case condition symmetric:

having 1 = exp(sum(ln(
case when b2.low between b1.low and b1.high
*or* b1.low between b2.low and b2.high
then 1 else 0.00000001 end)))

Changing the having clause won't fix a problem that already happened
before the having clause is applied.

I'm changing the aggregation expression. Therefore, the result of
aggregation is different now:

Let tmp1 = { (box1,box2,exp(sum(ln(...*or*...)))) |
(1,1,1), (1,2,1),
(2,1,1), (2,2,1)
}


Anyway, the condition if the two intervals [b1.low,b1.high] and
[b2.low,b2.high] intersect (for each dimension) is a simple expression
that depends on 4 numbers. Kind of obvious, isn't it? All what is
needed is aggregating it with the product aggregate -- which
corresponds to informal idea of the intersection volume. Alternatively,
we operate with 1s and 0s only, and could use boolean conjunction,
which is arithmetic min aggregate.

.

Relevant Pages

  • Re: Box query
    ... before the having clause is applied. ... I'm changing the aggregation expression. ... intersect is a simple expression ...
    (comp.databases.theory)
  • Re: What does "expression" refer to in the Totals list in a Query?
    ... Expressions are fields in the SELECT clause ... Sum means add up all values for the grouped by fields. ... Since the expression is tacked on after aggregation, ... You can therefore refer to that in your Expression. ...
    (microsoft.public.access.queries)
  • Re: DateDiff
    ... The purpose of the GROUP BY clause is to perform some sort of aggregation on some field. ... When I take out the group by Employee id, the query runs fine. ...
    (microsoft.public.access.queries)
  • Re: Querying mean values without using "Totals"
    ... You cannot use an aggregate in a WHERE clause. ... The WHERE clause is executed BEFORE any aggregation, so, the result ... Using the expression builder with the function "Avg" in the cell ... possible to have a aggregated function in a WHERE instance". ...
    (microsoft.public.access.queries)
  • Re: Where Between Date Range
    ... AS 2005 makes possible using sets in the WHERE clause, ... 2005 engine makes aggregation on the fly. ... Vladimir Chtepa. ... > Deepak Puri ...
    (microsoft.public.sqlserver.olap)