Concatenating Data: MORE
|
Topics: |
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, and Oracle), provided they share corresponding field names with the same format. You can use DEFINE fields, if necessary, to make the fields match.
To use MORE, you must divide your request into:
- One main request that retrieves the first data source, sorting criteria, and output format for all data.
- Subrequests that define the data sources to be concatenated to the data of the main request. The fields printed and sorted by the main request must exist in each concatenated data source, but are not mentioned.
- You can add WHERE and IF selection tests in any request.
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.
Concatenating 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.
The format of the MATCH command is:
{TABLE|MATCH} FILE file1
main request
MORE
FILE file2
subrequest
MORE
FILE file3
subrequest
MORE
.
.
.
ENDField 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, and WRITE 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. |
Note: Text (TX) fields and CLOB fields (if supported) cannot be concatenated.
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.
Run the request. The output is:
| WebFOCUS | |
|
Feedback |