Use the where Clause

You can include one where clause in a subscription to create selective subscriptions.

The where clause syntax is a subset of the Transact-SQL where clause. The where clause 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.

Note: 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]...

See Replication Server Reference Manual > Topics > Datatypes for entry formats for values for different datatypes.

Note: 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.

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, to specify that you want to subscribe to data where state = CA 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'

Note: 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'

Note: 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.