Defining complex horizontal filters

Some situations in database and user management call for the definition of a complex SQL WHERE clause in Subset of rows for individual users field in order to limit the rows that a group or a user can see. When defining the filter, you might refer to a table in the mirror database, use parameters for the group or the user, or use a combination of these methods. For the basic steps in creating a simple horizontal filter, see Filtering data at the subscription level.

DB2 Everyplace Sync Server provides a subset of the standard SQL WHERE implementation to use with the filter. For the syntax of the subset, see Syntax for filters.

The following example demonstrates how you may refer to other tables in the mirror database and use a parameter for horizontal filtering.

Suppose you manage two tables named Ziptab and Business, both of which are in the mirror database. Ziptab contains ZIP codes for certain cities (see Tabulka 208), while Business keeps track of some companies and their ZIP codes (see Tabulka 209). You want users in San Jose, California to view only the businesses in their city. You have created a group and assigned these users to the group.

Tabulka 208. Ziptab table

Tabulka 209. Business table

You would type the following clause in the Subset of rows for individual users field of the Rows page:

(Zip) IN (SELECT Zipcode FROM Ziptab WHERE City = ':fcity')

Then complete and close the Create Subscriptions notebook. As a result, after synchronization, the users will only see the following rows in Business table on their devices:

Tabulka 210. Rows that the users will see on their devices

Související úlohy

• Filtering data at the subscription level
• Filtering data from data sources
• Filtering data horizontally
• Defining simple horizontal filters
• Filtering data at the group level
• Filtering data at the user level
• Filtering data vertically

Související odkazy

• Syntax for filters