Translating Data from Another File. The TFILE code makes it possible to access data in another file. This powerful code eliminates the need for duplicating data in related files by retrieving data from one file for use in another file.
The TFILE code in the source file references data in another file, the destination file. The source file must include keys to the destination file—that is, data which is identical to the item-IDs of the destination file. These keys can be contained in one of the attributes of the source file, or they can be built from data stored in two or more attributes. These keys allow the source file to access any of the data in the destination file.
The TFILE code is placed in its own Attribute Definition item in the source file. This Attribute Definition item is created as a synonym for the Attribute Definition item (also in the source file) that defines the attribute mentioned in the preceding paragraph—the one that contains the item-IDs of the destination file. This is done by assigning an A or an S (synonym) code in Attribute 1 and by assigning an attribute number which is the same as the number of the attribute containing the destination file’s item-IDs.
The TFILE code itself specifies:
The name of the destination file
The number of the attribute in the destination file from which data is to be translated
What action is to be taken if there is no data in that attribute
As an example of how TFILE correlatives can be used, a book store could store book orders in an ORDERS file and store its book inventory in a BOOK-CATALOG file. Each order in ORDERS includes book titles and prices for each book order, but titles and prices are not stored in ORDERS. Instead, using TFILE correlatives, the orders file looks up the titles and prices in BOOK-CATALOG and uses them in ORDERS file reports. In this arrangement, ORDERS is the source file and BOOK-CATALOG is the destination file.
In the example below, attribute 1 in the source file contains the keys to the items in the destination file.
The next example displays a report showing some sample data in the ORDERS file:
|
>SORT ORDERS QTY BOOKCODE |
||
|
|
|
|
|
PAGE 1 |
11:15:13 DD MMM YYYY |
|
|
|
|
|
|
ORDERS |
Qty |
Book Code |
|
|
|
|
|
10101 |
2 |
N01 |
|
10102 |
3 |
N02 |
|
|
1 |
N01 |
|
10103 |
2 |
N01 |
|
10104 |
3 |
N01 |
|
|
5 |
N02 |
|
|
1 |
QR01 |
|
|
34 |
QR02 |
|
10105 |
1 |
N01 |
|
|
4 |
QR01 |
|
|
7 |
QR02 |
|
10106 |
3 |
N02 |
|
10107 |
9 |
N01 |
|
|
34 |
N02 |
|
10108 |
10 |
N02 |
|
|
2 |
QR01 |
|
10109 |
7 |
QR02 |
|
10110 |
3 |
N02 |
|
|
45 |
QR01 |
|
|
1 |
QR02 |
Data in the attribute BOOKCODE references the item-IDs of the BOOK-CATALOG file.
The item-IDs in BOOK-CATALOG are unique codes (such as standard ISBN numbers) that identify each book. Attribute 1, BOOKCODE, of the ORDERS file contains the BOOK-CATALOG item-IDs, which identify by code number each book ordered. This attribute contains the keys that are used to access data in the BOOK-CATALOG file.
The example below displays some sample output contents from the BOOK-CATALOG file.
|
>SORT BOOK-CATALOG AUTHOR TITLE PRICE |
|||
|
|
|
|
|
|
PAGE 1 |
|
11:15:28 DD MMM YYYY |
|
|
|
|
|
|
|
BOOK-CATALOG |
Author... |
Title........................ |
Price... |
|
|
|
|
|
|
N01 |
STRANG |
DATABASE MANAGEMENT SYSTEMS |
$9.95 |
|
N02 |
JACKSON |
OPERATING SYSTEM CONCEPTS |
$18.75 |
|
QR01 |
O'REILLY |
WRITING COMMERCIAL APPLICATION |
$24.50 |
|
QR02 |
BALLARD |
WORD PROCESSING |
$22.98 |
|
|
|
|
|
|
4 ITEMS LISTED. |
|
|
|
|
|
|
|
|
|
> |
|
|
|
Using TFILE correlatives, you can link the data contained in the BOOK-CATALOG file to the data contained in the ORDERS file, thus avoiding unnecessary duplication of data. The ORDERS file requires only one set of data to access the BOOK-CATALOG file, namely the unique book code numbers which are the item-IDs of BOOK-CATALOG. These numbers are stored as Attribute 1, BOOKCODE, in the ORDERS file.
The Attribute Definition item for TITLE in the ORDERS file contains the following data:
|
TITLE 001 S 002 1 003 Title 004 005 006 007 008 TBOOK-CATALOG ; C ; ; 2 009 T 010 35 |
This Attribute Definition item is a synonym for the Attribute Definition item BOOKCODE in the source file ORDERS. It uses a definition code of S and an attribute number of 1, the same number used for the attribute BOOKCODE. The TFILE correlative accesses data stored in Attribute 2, TITLE, in the destination file BOOK-CATALOG.
The illustration below identifies each of the elements in the TFILE correlative.
Format
When a TFILE correlative is to be used by INFO/ACCESS, its syntax format is:
|
Tfilename ; code ; ; attribute [; break] |
Parameter(s)
|
filename |
Name of the destination file. |
|
code |
Specifies the action to be taken if no data is found. |
|
attribute |
Number of the attribute in the destination file that contains the data to be translated. |
|
break |
Optional number of the attribute in the destination file to be translated for BREAK-ON and TOTAL lines. |
|
; ; |
Two semicolons must separate code from attribute. These semicolons actually specify a null value for an input attribute. Input attributes are not used by INFO/ACCESS. |
Description
The TFILE code must include an action code. Action codes specify what action is to be taken if there is no data in the destination file or if the item specified by the key does not exist in the destination file.
The action code X shown in the preceding figure specifies that if no value exists in Attribute 2 in the BOOK-CATALOG file, or if the item specified by the item-ID does not exist in the destination file, a null value is displayed in the title column of the INFO/ACCESS report.
The table below shows the complete list of TFILE action codes with a description of what happens if the file translation can’t take place.
|
Code |
Description |
|
|
C |
Returns the item-ID. |
|
|
I |
Verifies input only. Functions like V for input and like C for output. |
|
|
O |
Verifies output only. Functions like C for input and like V for output. |
|
|
V |
Suppresses the entire print line for the item returned in the error message:
|
|
|
X |
Returns a null value. |
To continue the example, the ORDERS file uses another TFILE correlative to translate book prices from the BOOK-CATALOG file. The correlative that retrieves book prices is just like the one used to retrieve titles, except that instead of the attribute TITLE, Attribute 3, PRICE, in the BOOK-CATALOG file is referenced. The Attribute Definition item for PRICE in the ORDERS file dictionary contains the data:
|
PRICE 001 S 002 1 003 Price 004 005 006 007 MR2$ 008 TBOOK-CATALOG ; X; ; 3 009 R 010 8 |
This Attribute Definition item is created as another synonym for Attribute 1, BOOKCODE. This time, however, the TFILE correlative references Attribute 3, PRICE, in the BOOK-CATALOG file. Also, since this attribute contains numeric values, it is right-justified.
Yet a third TFILE correlative is used to obtain the unit price from Attribute 3, PRICE, of BOOK-CATALOG. This TFILE correlative is embedded in an F correlative that multiplies the unit price by the data in Attribute 2, QTY, of ORDERS to arrive at the total amount for each line item. The Attribute Definition item for LINE.AMT in the ORDERS file dictionary contains the data:
|
LINE.AMT 001 S 002 0 003 Item Amount 004 005 006 007 MR2$ 008 F ; 1 ; (TBOOK-CATALOG ; X ; ; 3) ; 2 ; * 009 R 010 10 |
|
>SORT ORDERS QTY TITLE PRICE LINE.AMT |
Data in TITLE, PRICE, and LINE.AMT is derived from the BOOK-CATALOG file.
|
PAGE 1 |
|
11:15:44 DD MMM YYYY |
||
|
|
|
|
|
|
|
ORDERS |
Qty |
Title........................ |
Price... |
Item Amount |
|
|
|
|
|
|
|
10101 |
2 |
DATABASE MANAGEMENT SYSTEMS |
$9.95 |
$19.90 |
|
10102 |
3 |
OPERATING SYSTEM CONCEPTS |
$18.75 |
$56.25 |
|
|
1 |
DATABASE MANAGEMENT SYSTEMS |
$9.95 |
$9.95 |
|
10103 |
2 |
DATABASE MANAGEMENT SYSTEMS |
$9.95 |
$19.90 |
|
10104 |
3 |
DATABASE MANAGEMENT SYSTEMS |
$9.95 |
$29.85 |
|
|
5 |
OPERATING SYSTEM CONCEPTS |
$18.75 |
$93.75 |
|
|
1 |
WRITING COMMERCIAL APPLICATIONS |
$24.50 |
$24.50 |
|
|
34 |
WORD PROCESSING |
$22.98 |
$781.32 |
|
10105 |
1 |
DATABASE MANAGEMENT SYSTEMS |
$9.95 |
$9.95 |
|
|
4 |
WRITING COMMERCIAL APPLICATIONS |
$24.50 |
$98.00 |
|
|
7 |
WORD PROCESSING |
$22.98 |
$160.86 |
|
10106 |
3 |
OPERATING SYSTEM CONCEPTS |
$18.75 |
$56.25 |
|
10107 |
9 |
DATABASE MANAGEMENT SYSTEMS |
$9.95 |
$89.55 |
|
|
34 |
OPERATING SYSTEM CONCEPTS |
$18.75 |
$637.50 |
|
10108 |
10 |
OPERATING SYSTEM CONCEPTS |
$18.75 |
$187.50 |
|
|
2 |
WRITING COMMERCIAL APPLICATIONS |
$24.50 |
$49.00 |
|
10109 |
7 |
WORD PROCESSING |
$22.98 |
$160.86 |
|
10110 |
3 |
OPERATING SYSTEM CONCEPTS |
$18.75 |
$56.25 |
|
|
45 |
WRITING COMMERCIAL APPLICATIONS |
$24.50 |
$1102.50 |
|
|
1 |
WORD PROCESSING |
$22.98 |
$22.98 |
With these three TFILE correlatives in place, it is now possible to replace the data in the attribute BOOKCODE with the titles and prices that are stored in the BOOK-CATALOG file.
Multiple TFILE correlatives included in an Attribute Definition item allow data retrieval from a destination file that is more than one reference away from the source file. See Combining Correlatives and Conversions for more information about multiple TFILE correlatives.
To access the remote file, the dictionary entry that contains the TFILE correlative must be created with the AMC specifying the attribute of the source item which provides the item-IDs for items in the destination file.
Titles and prices are contained in the BOOK-CATALOG file in Attributes 2 and 3 respectively. The IDs in BOOK-CATALOG are unique character strings (PRODUCT) that identify each book.
The ORDERS file also uses the PRODUCT attribute, but as Attribute 1. Since PRODUCT can be used to link the data in the ORDERS and BOOK-CATALOG files, this is the only piece of data required in the ORDERS file to uniquely identify a book.
The table below shows the Attribute Definition items for TITLE and PRICE in the ORDERS file:
|
Item-ID |
TITLE |
PRICE |
|
D/CODE |
S |
S |
|
A/AMC |
1 |
1 |
|
S/NAME |
|
|
|
V/CONV |
|
|
|
V/CORR |
TBOOK-CATALOG;X;;2 |
TBOOK-CATALOG;X;;3 |
|
V/TYP |
T |
R |
|
V/MAX |
40 |
8 |
The TFILE correlative for TITLE specifies that the data should be retrieved from Attribute 2 of the BOOK-CATALOG file. The TFILE correlative for PRICE specifies Attribute 3 of the BOOK-CATALOG file. The X subcode shown for both TITLE and PRICE causes a null value to be returned if for any reason the data cannot be retrieved.
With the preceding TFILE correlatives in place, lengthy titles need not be duplicated in each order item. In addition, the price of any book in the BOOK-CATALOG file could be changed, and reports generated from the ORDERS file would automatically reflect the new data.
See Also
Using INFO/ACCESS Correlatives and Conversions
Introduction to Correlatives and Conversions