The CustDB sample application uses this technique for the ULOrder table. The ULEmpCust table holds the many-to-many relationship information between ULCustomer and ULEmployee.

Each remote database receives only those rows from the ULOrder table for which the value of the emp_id column matches the MobiLink user name.

The SQL Anywhere version of the download_cursor script for ULOrder in the CustDB application is as follows:

SELECT o.order_id, o.cust_id, o.prod_id,
   o.emp_id, o.disc, o.quant, o.notes, o.status
FROM ULOrder o , ULEmpCust ec
WHERE o.cust_id = ec.cust_id
 AND ec.emp_id = {ml s.username}
 AND ( o.last_modified >= {ml s.last_table_download}
  OR ec.last_modified >= {ml s.last_table_download})
 AND  ( o.status IS NULL
  OR o.status != 'Approved' )
 AND ( ec.action IS NULL )

This script is fairly complex. It illustrates that the query defining a table in the remote database can include more than one table in the consolidated database. The script downloads all rows in ULOrder for which the following are all true:

The action column on ULEmpCust is used to mark columns for delete. When NULL, the row is deemed to be fully active (not deleted).

The download_delete_cursor script is as follows.

SELECT o.order_id, o.cust_id, o.prod_id, o.emp_id, o.disc, o.quant, o.notes, o.status
   FROM ULOrder o, dba.ULEmpCust ec
   WHERE o.cust_id = ec.cust_id
     AND ( ( o.status = ''Approved'' AND o.last_modified >= {ml s.last_table_download} )
        OR ( ec.action = ''D''  )  )
     AND ec.emp_id = {ml s.username}

This script deletes all approved rows from the remote database.