Chapter 7. SQL Remote Design for AdaptiveSer verAnywhere
♦Thosecontacts belonging to the relevant customers, from the Contact
table.
Partitioning the Customer table in the Contact example
TheCustomer table can be partitioned using the rep_key value as a
subscriptionexpression. Apublication that includes the SalesRep and
Customertables would be as follows:
CREATE PUBLICATION SalesRepData (
TABLE SalesRep
TABLE Customer SUBSCRIBE BY rep_key
)
Partitioning the Contact table in the Contact example
TheContact table must also be partitioned among the sales representatives,
butcontains no reference to the sales representative rep_key value. How can
theMessage Agent match a subscription value against rows of this table,
whenrep_key is not present in the table?
Tosolve this problem, you can use a subquery in the Contact article that
evaluatesto the rep_key column of the Customer table. The publication
thenlooks like this:
CREATE PUBLICATION SalesRepData (
TABLE SalesRep
TABLE Customer
SUBSCRIBE BY rep_key
TABLE Contact
SUBSCRIBE BY (SELECT rep_key
FROM Customer
WHERE Contact.cust_key = Customer.cust_key )
)
TheWHERE clause in the subscription expression ensures that the subquery
returnsonly a single value, as only one row in the Customer table has the
cust_keyvalue in the current row of the Contact table.
☞Foran Adaptive Server Enterprise consolidated database, the solution is
different. For more information, see “Partitioning tables that do not contain
thesubscription column” on page 149.
Territory realignment in the Contact example
Interritory realignment, rows are reassigned among subscribers. Inthe
presentcase, territory realignment is the reassignment of rows in the
Customertable, and by implication also the Contact table, among the Sales
107