Use the SUBTRACT command to form a new table by subtracting rows in one table from a second table based on common columns.
Options
FORMING tblname3
Specifies the name of the table that is produced when you subtract one table from another.
FROM tblname2
Specifies the name of the table from which you want to subtract another table.
TEMPORARY
Creates a temporary table that disappears when the database is disconnected.
tblname1
Specifies the name of the table you want to subtract from a second table.
USING collist
Specifies the columns and order of columns that you want to include in the new table. At least one column in the USING clause must exist in both tables. If you do not include the clause, R:BASE makes a new table that includes all columns from both tables.
About the SUBTRACT Command
When one table (tblname1) is subtracted from another table (tblname2), R:BASE looks for columns in both tables that have the same name. The values in these common columns are subtracted and placed in the new table (tblname3). Tblname3 also contains all columns from tblname2 that are not in common with tblname1, unless columns are listed in the USING clause. It is worth noting that when there is more than one column with the same name R:BASE will not allow you to use the SUBTRACT Command without using the USING clause to specify which columns to compare.
Use SUBTRACT when you want to create a table that identifies differences between two tables, such as unique rows. When computed columns are common, R:BASE subtracts the current values in the computed columns. If you include a computed column in the USING clause, R:BASE transfers the current value for the computed column to the new table. If you want a computed column in a new table, include each column used in the computed column's expression with a USING clause. If you do not, R:BASE changes the computed column to a regular column containing data.
In a new table, R:BASE does not update the value in a row for an autonumbered column. An autonumbered column becomes a regular column.
You can increase the speed of SUBTRACT by creating an index for a common column in tblname1. SUBTRACT does not use an index in tblname2. If both tables have multiple common columns, index the common column in tblname1 that is most distinctive.
Example
The following command creates a new table named nobonus by subtracting the common columns in the salesbonus table from the employee table.
SUBTRACT salesbonus FROM employee FORMING nobonus
