Join Logical File

DDS on IBM Power i can join files in a logical file.

Join Logical File

A join logical file is a LF that combines fields from two or more physical files into one record format. Not all the fields in the record format need to exist in all the physical files. However, the joined fields must exist on each file joined. On join specification, identify which files are joined (JOIN keyword; optional if only one secondary file), and identify which fields are used to join the pair (specify the JFLD keyword). At least one JFLD is required for each join specification.

The fields do not have to be specified in any particular order. If the same field names appear in different physical files, specify the name of the physical file on the JREF keyword for those fields. You may rename fields using the RENAME keyword, and concatenate fields from the same physical file (CONCAT keyword). A subset of an existing character, hexadecimal, or zoned decimal field can be defined using the substring (SST keyword) function. The substring of a character or zoned decimal field is a character field, and the substring of a hexadecimal field is also a hexadecimal field. You can redefine fields: changing their data type, length, or decimal positions.

Joining Data

The example below joins two files. In addition to record formats, this join specifies the key sequence. The primary file GPORTAFL is joined to GPORPCFL using two fields, COMPNZ = COMPNC and INVNOZ = INVNOC. In this instance each pair of join fields do share the same attributes.

A          R GPORTAJ                   JFILE(GPORTAFL GPORPCFL)
A          J                           JOIN(GPORTAFL GPORPCFL) 
A                                      JFLD(COMPNZ COMPNC)     
A                                      JFLD(INVNOZ INVNOC)     
A            COMPNZ    R                                       
A            INVNOZ         8  0       TEXT('INVOICE NUMBER')  
A            CSTNOZ         8                                  
A            COMPNC         2          TEXT('COMPANY NUMBER')  
A            INVNOC         8  0       TEXT('INVOICE NUMBER')  
A            RECNOC         2  0       TEXT('RECORD NUMBER')   
A            CSTNOC         8  0       TEXT('CUSTOMER NUMBER') 
A            FRAMEC         8  0       TEXT('FRAME NUMBER')    
A            FDESCC         3          TEXT('FRAME DESC')      
A            COVR1C         5  0       TEXT('COVER 1')         
A            COLR1C         2  0       TEXT('COLOR 1')         
A            COVR2C         5  0       TEXT('COVER 2')         
A            COLR2C         2  0       TEXT('COLOR 2')         
A            COVDSC         4          TEXT('COVER DESC')      
A            FCODEC         4  0       TEXT('FRAME CODE')   
A            FCODEC         4  0       TEXT('FRAME CODE')    
A            WHSNOC         2  0       TEXT('WHSE NO')       
A            SLPRCC         5  2       TEXT('SELL PRICE')    
A            SPCD1C         2          TEXT('SPECIAL CODE 1')
A          K COMPNZ                                          
A          K INVNOZ

When creating a join logical file, there are keywords specific to join files to consider. One of particular note is DYNSLT. Specify this file-level keyword to indicate that the selection and omission tests specified in the file (using select/omit specifications) are to be performed at processing time.

Join Keywords

Keyword	Defining a join logical file
JFILE	Specify the names of the physical files as parameter values on the JFILE keyword. The first name you specify is the primary file. The others are all secondary files. For best performance, specify the secondary files with the least records first after the primary file.
JOIN	For each secondary file, code a join specification. On each join specification, identify which pair of files are joined (using the JOIN keyword; optional if only one secondary file), and identify which fields are used to join the pair (using the JFLD keyword; at least one required in each join specification).
JREF	Use this field-level keyword in a JLF for fields whose names are specified in more than one physical file. This keyword identifies which physical file contains the field to refrence..
JDFTVAL	The JDFTVAL keyword. Do this if you want to return a record for each record in the primary file even if no matching record exists in a secondary file.
JDUPSEQ	The JDUPSEQ keyword. Do this for fields that might have duplicate values in the secondary files. JDUPSEQ specifies on which field (other than one of the join fields) to sort these duplicates, and the sequence that should be used.
*	Key fields. Key fields cannot come from a secondary file. If you omit key fields, records are returned in arrival sequence as they appear in the primary file. * Use the K-level specs to define keys.
DYNSLT	Select/omit fields. In some situations, you must also specify the dynamic selection (DYNSLT) keyword at the file level.

iSoftwerks.factory