User's Manual

Chapter 7. SQL Remote Design for Adaptive Server Anywhere
♦ Those contacts belonging to the relevant customers, from the Contact
table.
Partitioning the Customer table in the Contact example
The Customer table can be partitioned using the rep_key value as a
subscription expression. A publication that includes the SalesRep and
Customer tables would be as follows:
CREATE PUBLICATION SalesRepData (
TABLE SalesRep
TABLE Customer SUBSCRIBE BY rep_key
)
Partitioning the Contact table in the Contact example
The Contact table must also be partitioned among the sales representatives,
but contains no reference to the sales representative rep_key value. How can
the Message Agent match a subscription value against rows of this table,
when rep_key is not present in the table?
To solve this problem, you can use a subquery in the Contact article that
evaluates to the rep_key column of the Customer table. The publication
then looks 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 )
)
The WHERE clause in the subscription expression ensures that the subquery
returns only a single value, as only one row in the Customer table has the
cust_key value in the current row of the Contact table.
☞ For an Adaptive Server Enterprise consolidated database, the solution is
different. For more information, see “Partitioning tables that do not contain
the subscription column” on page 149.
Territory realignment in the Contact example
In territory realignment, rows are reassigned among subscribers. In the
present case, territory realignment is the reassignment of rows in the
Customer table, and by implication also the Contact table, among the Sales
107