Use the UNION command to combine the columns and rows of two tables into a new table.
Options
FORMING tblname3
Specifies the name of the new table that is produced.
TEMPORARY
Creates a temporary table that disappears when the database is disconnected.
tblname1
Specifies the name of one of two tables you will combine.
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.
WITH tblname2
Specifies the name of the second of two tables you will combine.
About the UNION Command
When two tables are added together, R:BASE looks for columns in both tables that have the same name. The values in these common columns are added together and placed in the new table.
If the common columns have more than one match, R:BASE adds a new row for each possible combination. For example, if a table with a column named empid has two rows with an identification number of 100, and a second table has three rows with the same identification number, the new table will have six rows-one for each combination.
You can increase the speed of UNION by creating an index for the common column in both tables. If the tables have multiple common columns, index the common column that has the greatest number of unique values.
Combining Various Column Types
When computed columns are common columns, R:BASE compares the current values and changes the computed columns to regular columns in the new table. If you want a computed column in the new table, you should include each column used in the computed column's expression before the computed column.
If you use the UNION command to combine one or more columns that have NOTE data types, R:BASE truncates the columns that have the NOTE data types until the total row length is within the 32,768-character limit.
In a new table, R:BASE does not update the value in a row for an autonumbered column. An autonumber column becomes a regular column.
Creating New Columns
You can also use UNION to add new columns to a table. Define the new columns in a temporary table with the CREATE TABLE command. Then, combine the existing table with the temporary table to form a new table containing all the columns. When the union is complete, use the DROP command to delete the temporary table. The temporary table must have at least one column in common with the existing table.
Examples
The following command creates the saleshist table by combining all rows for the empid, empfname, emplname, and netamount columns. The saleshist table contains the columns listed in the USING clause in the order given.
UNION employee WITH transmaster FORMING saleshist USING +
empid, empfname, emplname, netamount
The following command creates the reporthistory table by combining all columns and rows from the employee and salesbonus tables. If a USING clause is not specified,reporthistory includes all the columns from the employee and salesbonus tables, listing the columns from the employeetable first.
UNION employee WITH salesbonus FORMING reporthistory
