Universal Concatenation

In this section:

How to:

With universal concatenation, you can retrieve data from unlike data sources in a single request; all data, regardless of source, appears to come from a single file. The MORE phrase can concatenate all types of data sources (such as, FOCUS, DB2, IMS, and VSAM), provided they share corresponding fields with the same format. You can use WHERE and IF selection tests in conjunction with MORE. For related information, see Selecting Records for Your Report.

To use MORE, you must divide your request into:

During retrieval, data is gathered from each data source in turn, then all data is sorted and the output formatted as specified in the main request.


Top of page

x
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 file1 
   main 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:

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
  1. The request must re-map the field names and formats in the EXPERSON data source to match those used in the main request.
  2. The main request names the first data source in the concatenation, EMPLOYEE. It also defines the print and sort fields for both data sources.
  3. 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.

Top of page

x
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 PAYHIST data sources. Master Files and Diagrams, contains the Master Files referenced in the request.

Tip: PAYHIST is a fixed-format file. You need to issue a FILEDEF or ALLOCATE command in order to use it. See the Overview and Operations manual for more information.

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

In the resulting report, the EMPLOYEE ID values that start with 000 are from EMPDATA, and the values that start with 100 are from PAYHIST:

EMPLOYEE SALARIES
   EMPLOYEE ID             SALARY
   -----------             ------
   000-00-0030         $70,000.00
   000-00-0070         $83,000.00
   000-00-0200        $115,000.00
   000-00-0230         $80,500.00
   000-00-0300         $79,000.00
   100-10-1689            $842.90
                          $982.90
   100-11-9950            $508.75
   100-14-2166            $876.45
   100-15-5843            $508.75
   100-16-2791            $567.89
   100-16-4984          $1,236.78
   100-17-5025            $734.56
   100-18-9299            $567.89

Information Builders