Joining physical file to itself in Join Logical File in AS400

Joining physical file to itself in Join Logical File in AS400

Introduction

In this blog, we will be seeing an example that tells us how to create a Join Logical file (JLF) to join physical file(PF) to itself.

Create first physical file PF1

PF1 has 3 fields namely EMPID of 10 chars and EMPNAME of 20 chars and MANAGERID of 10 chars respectively and record format name as RPF1.Use the CRTPF command to create the object for file PF1.

A          R RPF1                     
A            EMPID         10A        
A            EMPNAME       20A      
A            MANAGERID     10A 

Create a Join Logical file (JLF)

JLF1 would have 3 fields namely EMPID, EMPNAME, MANAGERNAM from file PF1.

A                                      JDFTVAL 
A          R RJLF1                     JFILE(PF1 PF1)       
A          J                           JOIN(1 2)         
A                                      JFLD(MANAGERID EMPID)   
A            EMPID                     JREF(1)             
A            EMPNAME                   JREF(1)                             
A            MANAGERNAM                JREF(2) RENAME(EMPNAME)     

Data in PF1

EMPID       EMPNAME               MANAGERID  
1           NAME1                 10         
10          NAME10                20         
20          NAME20                30         
2           NAME2                 11         
11          NAME11                20         
3           NAME3                 12         
12          NAME12                30         
********  End of data  ********              

Data in join logical file (JLF1) - There are no managers selected for EMPID 20 and 12 since MANAGERID 30 does not exist in file PF1.A record for EMPID 20 and 12 is fetched even if the manager row does not exist in the file as we used JDFTVAL keyword.

EMPID       EMPNAME               EMPNAME 
1           NAME1                 NAME10  
10          NAME10                NAME20  
20          NAME20                        
2           NAME2                 NAME11  
11          NAME11                NAME20  
3           NAME3                 NAME12  
12          NAME12                        
********  End of data  ********             

R

R denotes the Record format of the join logical file. Here in this example, JLF1 has a record format name as RJLF1.

JFILE(PF1 PF1)

JFILE keyword accepts parameters as file names. Here you must specify at least two physical files (PF) where the first file is the primary file and the second file is the secondary file. In this case, both the files are the same, So it's a self-join condition.

J

J denotes the beginning of the Join specification. At least one Join specification must be defined in the Join logical file (JLF). The next J would be on the next line.

JOIN(1 2)

JOIN denotes that which two files will be joined using the JOIN keyword. Let me tell you if only two physical files are joined then this JOIN keyword is optional to use in Join logical file (JLF). We can give numbering to the files in the JOIN keyword.

JFLD(MANAGERID EMPID)

JFLD keyword denotes join fields that will join rows from the physical files specified on the JOIN keyword.JFLD keyword must be specified at least once for each join keyword. Let me tell you the join fields are those fields that are common to both the physical files. All the fields must be of the same type, the length specified on the JFLD if they are not character types.

Field Name

At least one field name must be specified from the physical file and you can use keywords such as CONCAT, SST, RENAME here.

Field Name with RENAME

RENAME keyword is used here to define the manager name field using the EMPNAME field in file PF1.

JREF(1)/JREF(2)

JREF keyword is used for the field names which must specify that from which files this field is referred from in Join logical file (JLF). Here, in this example, EMPID and EMPNAME coming from file 1 and MANAGERNAM coming from file 2.

K

Please note that in this example PF1 and PF2 do not have key fields. So Join logical file (JLF) is an arrival sequence file in this case, but in case we have defined any key fields then the Join logical file would be an indexed file(keyed sequence).

Note and Warning

Click the below button to navigate to the Note and Warning page

Notes and Warning

Points to remember

Click the below button to navigate to the Points to remember page

Points to remember

Related Post

Read also :

• Logical file in AS400
• Non-Join Logical file in AS400
• Join Logical file in AS400
• Join two physical files to create Join logical file in AS400
• Join more than one physical file field to create Join logical file in AS400
• Use JDUPSEQ keyword to arrange duplicate records in secondary file in join logical file in AS400
• Join fields with different attributes to create a Join logical file in AS400
• Fields that never appear in Record format in Join Logical File in AS400
• Specify the key fields in the Join Logical File in AS400
• Joining three or more physical files to create Join Logical File in AS400
• Select and Omit criteria in Logical file in AS400