|
|
|
This statement will retrieve one record from the first table, then retrieve as many records found in the second table that match the key of the first one. The records would be written to the output file automatically, unless no records in the second table(s) match the first. Then the record from the first table may be optionally written out. This loop would occur until all key values specified are unloaded. The record from the first table will not be duplicated in the output file for subsequent tables after the first successful EQUI-JOIN. If the primary table has duplicate key values, they will be written to the output file. This supports many-to-one, one-to-one, and one-to-many relationships.
EQUI-JOIN table-name,key-occurrence-name
TO table-name1,key-occurrence-name,
table-name2,key-occurrence-name,
...,...,table-name20,key-occurrence-name
{USING KEY key-value|
USING KEY FROM {field-name | work-area | PRIMARY}|
USING {ddname | dtfname} AS INPUT}
[PERFORM paragraph-name FOR table-name
NOT-FOUND GO TO {paragraph-name | FINISH}
AT-END GO TO {paragraph-name | FINISH}
KEYS-AT-END GO TO {paragraph-name | FINISH}
NO-PRIMARY
WHEN conditional-clause USE table-name].
The table immediately following the statement EQUI-JOIN is considered the primary table. Following the comma is the key to be used to retrieve records in the primary table. Following the keyword TO is a list of secondary tables and keys that CA-Datamacs/II will attempt to include in the output file. The secondary list may contain up to 20 entries. All the table-names and key-occurrence-names have to be coded in pairs.
The EQUI-JOIN statement can only be used during an UNLOAD function.
Identifies a key literal to be used when a single record is to be retrieved. The maximum length of an individual key literal is that which can be coded within area B of the COBOL statement. See function logic MOVE statement for more syntax rules concerning literals. Numeric literals should be entered without any periods or commas. A trailing minus sign can be used to indicate a negative number. Alphanumeric literals should be enclosed in quotes. Any trailing blanks should be included in quotes. A hexadecimal value can be entered by enclosing the value with X'value'. Only valid hexadecimal digits are allowed; 0 to 9 and A through F. Packed numeric values should be entered as hexadecimal values insuring the same sign is used as that specified in Datadictionary.
This is the name of a COBOL field containing the key value. The PRIMARY option allows all records in the primary table to be unloaded along with any matching records from the secondary table(s). Duplicate keys in the primary table will not produce duplicate output records for the secondary tables.
The keys of the desired records are placed in a sequential fixed length file, one key per record. From 1 to 17 files may be specified per run, but only one per EQUI-JOIN. The predefined DD/DTF names are DMIKEYS and DMIKY01 through DMIKY16.
Each time the EQUI-JOIN command is executed, one key is read from the DD/DTF file name specified and used to find a record in the primary table and all subsequent tables specified. An internal loop is created until all input key records have been read, at which time the KEYS-AT-END exit is activated.
USING KEY, USING KEY FROM, and USING ûddname|dtfnameº AS INPUT are mutually exclusive.
Can be used to further qualify the record selected. The field-name to be tested must be in the primary table. A WHEN condition, if found to be not true, loops back and tests the next record in the set, until the end of the set.
This feature will allow branching to a paragraph outside the EQUI-JOIN. The intended purpose is to allow retrieval from another table whose key is imbedded in the current record, but is not the master key used by EQUI-JOIN. The paragraph should contain a WRITE statement to output any records retrieved. To return to EQUI-JOIN, a GOBACK must be coded in the paragraph.
Enter the paragraph name to branch to when the key in the first table is not found. If this entry is not coded and the key option is USING KEY INPUT, the next input record will be read. If the option is USING KEY or USING KEY FROM, the next statement after the EQUI-JOIN will be executed.
Enter the paragraph name to branch to after the last record in the primary table is read. This option can only be used when no input keys file is used. If not coded, CA-Datamacs/II will automatically go to end-of-job.
Enter the paragraph name to branch to when the input keys file is at end of file. If not coded, CA-Datamacs/II will automatically go to end-of-job.
Specify this keyword when you do not want the primary table record written to the output file if no keys are found in any other table. Just one key match to another table will qualify the primary record for output.
| Copyright © 2009 CA. All rights reserved. | Tell Technical Publications how we can improve this information |