Filtering rows

In order to include or exclude particular rows for replication, you need to build a row-filtering expression.

About this task

All row-filtering expressions that you define must return a boolean result. For example, you may have a source column such as SALARY that maintains the salary for each employee in your organization. You may only want to replicate those rows to the target table for those employees that have a salary greater than $48,000. In this scenario, you would need to define a row-filtering expression (SALARY > 48000).

You can use column manipulation functions, basic numeric operators, and SQL SELECT WHERE clauses in your row-filtering expressions.

The following are examples of valid row-filtering expressions:

  • (SALES < 10000) OR (SALES > 90000)
  • ((AIRPORT = 'JFK') OR (AIRPORT = 'LAX'))
  • %IF(COUNTRY = 'US', PRODUCTPRICE, PRODUCTPRICE *1.2) > 50
  • PRODUCTPRICE * (1 + TAX) > 20000

Procedure

  1. Click Configuration > Subscriptions.
  2. Select the subscription.
  3. Click the Table Mappings view and select the table mapping from the Source Table column.
  4. Right-click and select Open Details....
  5. Click the Filtering tab.
  6. Click Editor and build a row-filtering expression.

    The expression must return a boolean result.

  7. Click Verify to check the syntax of the expression and click OK to return to the Filtering tab.
  8. Choose one of the following options in the Row-filtering area:
    Select rows that match the expression
    Select this option if you want CDC Replication to replicate the source rows that satisfy your row-filtering expression.
    Omit rows that match the expression
    Select this option if you want CDC Replication to replicate all rows except those that satisfy your row-filtering expression.
  9. Click Save.