Syntax for filters

You can use a clause similar to the WHERE clause in SQL to filter for individual users when you create a replication subscription in the Mobile Devices Administration Center. Enter the clause in the Subset of rows for individual users field of the Advanced Replication Subscription notebook for DataPropagator subscriptions, or Advanced Replication Subscription notebook for JDBC subscriptions.

There are limitations when you use the syntax diagram listed in this topic. These limitations include:

There can be several look-up tables (such as the Ziptab table in the example above). However, each look-up table cannot be mentioned more than once in the clause.
Parameters can only appear where a constant is expected. If the parameter is a character data type, such as VARCHAR or CHAR, it must be enclosed in single quotes. For example, JOBCODE=':JOB'
Restriction for creating filters on Lotus Domino table: In order to define a filter (Horizontal and Vertical) for a JDBC subscription using a Lotus Domino data source, the source table in the Domino database needs to be created with uppercase column names. This restriction applies to primary key columns and the columns used in the filter (columns used in the WHERE clause for a horizontal filter and columns unsubscribed for a vertical filter)

Syntax

Filter
 
   .-logic operator AND or OR-.
   V                          |
>>---| dimension |------------+--------------------------------><
 
dimension:
 
|--+-column-name--IN--| subselect |-----------+-----------------|
   |    .-,-----------.                       |
   |    V             |                       |
   +-(----column-name-+--)--IN--| subselect |-+
   '-| simpleUnaryFilter |--------------------'
 
subselect:
 
|--(--| selectClause |--| fromClause |--+-----------------+----->
                                        '-| whereClause |-'
 
>--)------------------------------------------------------------|
 
selectClause:
 
           .-,-----------.
           V             |
|--SELECT----column-name-+--------------------------------------|
 
fromClause:
 
|--FROM--| singleTableReference |-------------------------------|
 
whereClause:
 
|--WHERE--| simpleFilter |--------------------------------------|
 
singleTableReference:
 
|--+----------------+--table-name-------------------------------|
   '-schema-name--.-'
 
simpleFilter:
 
   .-logic operator AND or OR-.
   V                          |
|----| simpleUnaryFilter |----+---------------------------------|
 
simpleUnaryFilter:
 
|--+-NOT--| simpleUnaryFilter |-+-------------------------------|
   +-(--| simpleFilter |--)-----+
   '-| simplePredicate |--------'
 
simplePredicate:
 
|--| expression |--| simplePredicateEnd |-----------------------|
 
expression:
 
|--+-| function |-+---------------------------------------------|
   +-column-name--+
   '-| constant |-'
 
function:
 
|--+----------------+--function-name--(------------------------->
   '-schema-name--.-'
 
>--+--------------------+--)------------------------------------|
   | .-,--------------. |
   | V                | |
   '---| expression |-+-'
 
simplePredicateEnd:
 
|--+-+-| operator |-+--| expression |----------------------+----|
   | '-IN-----------'                                      |
   +-IS--+-----+--NULL-------------------------------------+
   |     '-NOT-'                                           |
   '-+-----+--BETWEEN--| expression |--AND--| expression |-'
     '-NOT-'
 
operator:
 
|--+-=----+-----------------------------------------------------|
   +-<>---+
   +->----+
   +-<----+
   +->=---+
   +-<=---+
   +-!=---+
   +-!<---+
   +-!>---+
   '-LIKE-'

Description

Operators

You can use any of the following operators:

=: Equal to.
<>: Not equal to.
<: Less than.
>: Greater than.
<=: Less than or equal to.
!>: Less than or equal to.
>=: Greater than or equal to.
!<: Greater than or equal to.
LIKE: Matches one character string. Use a single-byte character-set (SBCS) underscore to refer to one SBCS character. Use a double-byte character-set (DBCS) underscore to refer to one DBCS character. For example, a condition WHERE PART_NUMBER LIKE '_0' (here the underscore is in SBCS) returns all 2-digit part numbers ending in 0 (20, 30, and 40, for example). Use a percent (either SBCS or DBCS) to refer to a string of zero or more SBCS or DBCS characters. For example, a condition WHERE DEPT_NUMBER LIKE '2%' returns all department numbers beginning with the number 2 (20, 27, or 234, for example).

Related tasks