BIND_FILTERS
Description
BIND_FILTERS pulls a filter to a specified table. Multiple filters on a table are merged together by a logical AND.
BIND_FILTERS
can be used in the following contexts:
Syntax
BIND_FILTERS( target_table , condition [, condition ] *)
Join Behavior For Multiple Filters
Filters separated by a comma do not need to have a valid join relation between each other. But they should have a valid join relation with the target_table
, otherwise they are simply ignored and have no effect on the result.
NULL handling
If a condition returns NULL, then BIND_FILTERS returns FALSE.
Examples
[1] Binds the filters on the Activity and OrderPos tables to the Case table. The actual join between those tables is not introduced, which is why the number of rows in the result equals the number of rows of the Case table: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[2] This query results in a join error since the Activity and OrderPos table do not have a valid join path. In order to make this query work, the AND can be replaced with comma separation. Then, the two filters are independently pulled to the caseTable (see the example above). | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[3] A case is matched to the string value when for this case the orderId is "001" or BIND_FILTERS returns true. BIND_FILTERS is true when it has an activity "C" and an city entry with "Seattle" for the orderId. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[4] BIND_FILTERS filters for the activities "A" and "C" as well as for the countries "DE", "FR", "IT" as well as "ESP". In order to not being filtered out by FILTER, a case must have one of the specified activities as well as one of the countries. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[5] BIND_FILTERS filters for activities "A" and "B". Since the only case, which has activities with "A" or "B", is case 1, the distinct activities will only be counted for this case. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[6] CALC_REWORK only counts the activities for the case with id 1 because BIND_FILTERS filters for cases, which have an orderId having a country entry with "DE" or "FR" and a city entry with "Frankfurt" or "Lyon", and activities "A" or "C". | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[7] In the first FILTER statement, only entries with the country being "DE" or "FR" are kept. Then, in the next FILTER statement only the caseTable entries with an order ID still being in OrderPos are kept. Afterwards, the filter is pulled to the activityTable filtering out all entries with case ID 2. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[8] BIND_FILTERS filters for the order IDs "001", "002", and "007". The result is then pulled to the activityTable. Since order ID "003" was not in the list of the IN-operator, case 3 was filtered out. Therefore, when pulling the filter to the activityTable, all entries with case 3 are ignored. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|