The transparent filter flag has been introduced with SPS09. Its purpose is to avoid the inclusion of attributes in aggregations when the attributes are only needed for filtering. For example, the following query
SELECT field1, field2, SUM(m) FROM View WHERE field3 = 'X' GROUP BY field1, field2
will force “field1” and “field2” to be kept when aggregating field “m” due to the GROUP BY. In addition, the usage of “field3” in the filter will force “field3” into the aggregation as well. By setting the transparent filter flag for “field3” you can request that “field3” should not be kept when doing the aggregation unless it is requested by the query itself.
Let’s have a look at two examples
The first example will lead to different results depending on whether an additional filter that actually does not reduce any data is included in the query or not. In the second example the transparent filter flag will be used and by this the same results will be returned by the queries with and without filter (remember that for illustrative purposes in these examples the filter is defined in a way that no records are removed by it).
Example 1 – no usage of transparent filter flag
- Create an example table
create column table exampleTransparentFilter(day DATE,TNR NVARCHAR(10),TTYP NVARCHAR(10)); insert into exampleTransparentFilter values ('2015-10-10','1','SL'); insert into exampleTransparentFilter values ('2015-10-11','1','SL');
- Create a Calculation View CV1 that consumes this table as a data source and defines a field C as count distinct on TNR:
- Create another Calculation View CV2 that simply consumes CV1 and passes through all fields
- Write a query on CV2 that only requests attribute “TTYP” but has a filter on attribute “Day”
SELECT "TTYP", SUM("C") AS "C" FROM "CV2" WHERE "DAY"!='' GROUP BY "TTYP";
- In the results you will see the value “2” for the count distinct:
- Comment the WHERE clause which does not reduce the data because the filter will never evaluate to FALSE and execute the query without the filter:
SELECT "TTYP", SUM("C") AS "C" FROM "CV2" -- WHERE -- "DAY"!='' GROUP BY "TTYP";
- You will now see the distinct count value “1”
When a filter is defined the respective attribute is used in the grouping for the count distinct per default. In the example above this would be attribute “Day”. The result from view CV1 therefore looks like:
The SQL query on top of the Calculation Views sums up the distinct count field “C” because only TTYP is requested in the GROUP BY and therefore the same TTYP enters twice – for each day once.
Example 2 – Usage of transparent filter flag
Set the transparent filter flag for the field “DAY” and run the same two queries from above. Both will return the value “1”:
These two examples should have demonstrated that it is important to specify what semantics is expected for a filter. Should the attribute that is used in the filter be included when aggregating, or not. If the intended behavior is to ignore the filter attribute during aggregation then the transparent filter flag can be used.