You can include one where clause in a subscription. The where clause syntax is a subset of the Transact-SQL where clause. It is supported by the create subscription and define subscription commands for subscriptions to replication definitions. The supported syntax is the same for both commands and allows you to create very selective subscriptions. It is designed for efficient processing by the Subscription Resolution Engine in Replication Server.
You cannot evaluate a Java column in a subscription
expression. Thus, you cannot include a column of type rawobject or rawobject
in row in a subscription where clause.
For subscriptions to table replication definitions, the where clause syntax is:
where column_name{< | > | <= | >= | = | &} value
[and column_name{< | > | <= | >= | = | &}
value]...
For subscriptions to function replication definitions, the where clause syntax is:
where @param_name
{< | > | <= | >= | = | &} value
[and @param_name
{< | > | <= | >= | = | &} value]...
Refer to “Datatypes” in Chapter 2, “Topics,” in the Replication Server Reference Manual for entry formats for values for different datatypes.
The !=, !<, !>,
and or operators are not supported. You can create
multiple subscriptions instead of using the or operator.
The & operator is supported only on rs_address columns.
For details on using the rs_address datatype,
see “Using the rs_address datatype” and “Bitmap subscriptions”.
Each column name in a where clause must be listed in the searchable columns list of the table or function replication definition. The value for each column must have the same datatype as the column to which it is compared.
For example, for table replication definition publishers_rep, you would enter:
create subscription publishers_sub1 for publishers_rep with replicate at SYDNEY_DS.pubs2 where state = 'CA'
to specify that you want to subscribe to data where state = CA.
The maximum size of a where clause
in a create subscription statement is 255 characters.
To subscribe to data in publishers, where state = CA or state = MA, you would need to create two subscriptions. In addition to the preceding command, you would enter:
create subscription publishers_sub2 for publishers_rep with replicate at SYDNEY_DS.pubs2 where state = 'MA'
When you use a where clause with
a subscription for heterogeneous datatype columns subject to class-
or column-level translations, you must make sure that you use the
correct datatype in the comparison. See “Subscriptions for columns with heterogeneous datatypes”.
