The Perfion Query - HAVING
The Having clause works similarly as the Where clause, but there are some crucial distinctions. Furthermore, it has a specific use case for remote features.
Post filtering
The Perfion Where clauses are transformed into real SQL clauses against the underlying SQL database. The biggest difference from the Where clauses is that the Having clauses are not transformed into SQL statements but instead executed afterwards (in memory) on the result-set retrieved from the underlying SQL database.
This means that HAVING does not allow complex Features on Features or the Hierarchy-notation concept since both rely on data that is not found in the result-set.
Remote features
As opposed to the Where clauses, the Having clauses allows criteria on remote feature values (since they are working directly on the result-set).
Here is an example of the Having clause on a Remote feature (RRPBC), which shows Bodum products with a Retail price (RRPBC) higher than $50. As you can see, only the remote feature (the Retail Price from BC ERP) is placed within the Having clause. Other conditions stay within a separate Where clause, placed before the Having clause.
<Query>
<Select languages='EN'>
<Feature id='**' view='Overview' />
</Select>
<From id='100' />
<Where>
<Clause id='BrandName' operator='=' value='Bodum' />
<Clause id='Brand' operator='=' value='0' />
</Where>
<Having>
<Clause id='RRPBC' operator='>' value='50' />
</Having>
</Query>Performance
Since the Having clauses work on the retrieved result set, it is important to get as many criteria into the Where clauses as possible in order to optimize performance.
Syntax
The syntax of the Having clauses are identical to the Where clauses. However, not all of the same operations are typically needed at this stage. The following operators are currently supported for the Having clauses.
= |
!= |
> |
>= |
< |
<= |
IN, NOT IN |
HAS, NOT HAS |