Universal Concatenation

Syntax: How to Concatenate Data Sources

The MORE phrase, which is accessible within the TABLE and MATCH commands, specifies how to concatenate data from sources with dissimilar Master Files.

{TABLE|MATCH}  FILE file1main request 
MORE
FILE file2 
  subrequest 
MORE
FILE file3 
  subrequest 
MORE
   .
   .
   .
{END|RUN}

where:

TABLE|MATCH

Begins the request that concatenates the data sources.

file1

Is the name of the first data source.

main request

Is a request, without END or RUN, that retrieves the first data source and defines the data fields, sorting criteria, and output format for all data. WHERE and IF criteria in the main request apply only to file1.

When concatenating files within the TABLE command, you can also define calculated values for the first data source.

MORE

Begins a subrequest. There is no limit to the number of subrequests, other than available memory.

FILE file2

Defines file2 as the second data source for concatenation.

subrequest

Is a subrequest. Subrequests can only include WHERE and IF phrases.

END|RUN

Ends the request.

Example: Concatenating Data Sources

Both the EMPLOYEE and the EXPERSON data sources contain employee information. You can concatenate their common data into a single file:

EMPLOYEE contains the field values EMP_ID=123456789 and CURR_SAL=50.00.
EXPERSON contains the field values SSN=987654321 and WAGE=100.00.

The following annotated request concatenates the two data sources:

   DEFINE FILE EXPERSON 
1. EMP_ID/A9 = SSN;
   CURR_SAL/D12.2 = WAGE;
   END 
2. TABLE FILE EMPLOYEE
   PRINT CURR_SAL
   BY EMP_ID 
3. MORE
   FILE EXPERSON
   END

The request must re-map the field names and formats in the EXPERSON data source to match those used in the main request.
The main request names the first data source in the concatenation, EMPLOYEE. It also defines the print and sort fields for both data sources.
The MORE phrase starts the subrequest that concatenates the next data source, EXPERSON. No display commands are allowed in the subrequest. IF and WHERE criteria are the only report components permitted in a subrequest.

Field Name and Format Matching

All fields referenced in the main request must either exist with the same names and formats in all the concatenated files, or be remapped to those names and formats using virtual fields. Referenced fields include those used in COMPUTE commands, headings, aggregation phrases, sort phrases, and the PRINT, LIST, SUM, COUNT, WRITE, or ADD commands.

A successful format match means that:

Usage Format Type	Correspondence
A	Format type and length must be equal.
I, F, D	Format type must be the same.
P	Format type and scale must be equal.
DATE (new)	Format information (type, length, components, and order) must always correspond.
DATE (old)	Edit options must be the same.
DATE -TIME	Format information (type, length, components, and order) must always correspond.

Text (TX) fields and CLOB fields (if supported) cannot be concatenated.

Example: Matching Field Names and Formats

The following annotated example concatenates data from the EMPDATA and SALHIST data sources.

   DEFINE FILE EMPDATA 
1. NEWID/A11=EDIT (ID,'999-99-9999');
   END
 
   DEFINE FILE SALHIST 
2. NEWID/A11=EDIT (ID,'999-99-9999');
   CSAL/D12.2M=OLDSALARY;
   END
  
3. TABLE FILE EMPDATA
   HEADING
   "EMPLOYEE SALARIES"
   " "
   PRINT CSAL
   BY NEWID 
4. WHERE CSAL GT 65000 
5. MORE
   FILE SALHIST 
6. WHERE OLDSALARY GT 65000
   END

Defines NEWID in the EMPDATA data source with the same name and format as the sort field referenced in the main request.
Defines NEWID in the SALHIST data source with the same name and format as the sort field referenced in the main request.
The main request. This contains all the formatting for the resulting report and names the first file to be concatenated. It also contains all printing and sorting information. The fields printed and the sort fields must exist as real or DEFINE fields in each file.
The WHERE criterion in the main request applies only to the EMPDATA data source.
The MORE phrase concatenates the SALHIST data source to the EMPDATA data source.
This WHERE criterion applies only to the SALHIST data source. Notice that it references a field that is not defined in the EMPDATA data source.

The output is:

EMPLOYEE SALARIES

NEWID	SALARY
000-00-0030	$70,000.00
	$70,000.00
000-00-0070	$83,000.00
	$83,000.00
	$79,100.00
000-00-0200	$115,000.00
	$115,000.00
	$102,500.00
	$89,500.00
000-00-0230	$80,500.00
	$80,500.00
	$75,000.00
	$70,800.00
000-00-0300	$79,000.00
	$79,000.00
	$75,000.00
	$70,000.00

When you concatenate data, record sets are simply appended, not grouped or aggregated across files. Therefore, if duplicate sort fields exist, they show up twice in the report output.