Use JDUPSEQ keyword to arrange duplicate records in secondary file in join logical file in AS400

Read duplicate records in secondary file in join logical file in AS400
Use JDUPSEQ keyword to arrange duplicate records in secondary file in join logical file in AS400, JDUPSEQ, dds keyword, LF, JLF, join logical file, logical file, as400,ibmi,iseries
Use JDUPSEQ keyword to arrange duplicate records in secondary file 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 more than one physical file field having duplicate data in the secondary physical file.

Create first physical file PF1

PF1 has 3 fields namely ROLLNO of 10 chars, CLASS of 2 numeric and STUNAME of 15 chars respectively, and record format name as RPF1.Use the CRTPF command to create the object for file PF1.


A          R RPF1                     
A            ROLLNO        10A 
A            CLASS          2P 0
A            STUNAME       15A        

Create second physical file PF2

PF2 has 4 fields namely ROLLNO of 10 chars and CLASS of 2 numeric and AGE of 2 numeric and SUBJECT of 20 chars respectively and record format name as RPF2.Use the CRTPF command to create the object for file PF2.


A          R RPF2               
A            ROLLNO        10A  
A            CLASS          2P 0 
A            AGE            2P 0
A            SUBJECT       20A  

Create a Join Logical file (JLF)

JLF2 would have 5 fields namely ROLLNO, STUNAME, and CLASS and AGE and SUBJECT from files PF1 and PF2, where the ROLLNO and CLASS fields are common in both the physical files PF1 and PF2.


A          R RJLF2                     JFILE(PF1 PF2)       
A          J                           JOIN(PF1 PF2)         
A                                      JFLD(ROLLNO ROLLNO)
A                                      JFLD(CLASS CLASS) 
A                                      JDUPSEQ(SUBJECT) 
A            ROLLNO                    JREF(PF1)
A            CLASS                     JREF(PF1)  
A            STUNAME                                         
A            AGE                                               
A            SUBJECT

Data in PF1 (Primary file)


ROLLNO      CLASS  STUNAME     
1              1   NAME1       
2              1   NAME2       
********  End of data  ********

Data in PF2 (Secondary file)


ROLLNO      CLASS  AGE   SUBJECT   
1              1     6   PHYSICS   
1              1     6   CHEMISTRY 
1              1     6   MATHS     
2              1     5   BIOLOGY   

Data in join logical file (JLF2)


 ROLLNO      CLASS  STUNAME          AGE   SUBJECT    
 1              1   NAME1              6   CHEMISTRY  
 1              1   NAME1              6   MATHS      
 1              1   NAME1              6   PHYSICS    
 2              1   NAME2              5   BIOLOGY    
 ********  End of data  ********                               

There are 3 records for ROLLNO 1 and CLASS 1 in secondary physical file PF2 i.e. ROLLNO1 in CLASS1 has opted for 3 subjects.
The records for ROLLNO 1 and CLASS 1 are sorted in ascending order by the field SUBJECT because the JDUPSEQ keyword sorts in ascending order until and unless you specify *DESCEND as the keyword parameter. Please see the below DDS for reference.

Create a Join Logical file (JLF) using JDUPSEQ *DESCEND

JLF2 would have 5 fields namely ROLLNO, STUNAME, and CLASS and AGE and SUBJECT from files PF1 and PF2, where the ROLLNO and CLASS fields are common in both the physical files PF1 and PF2.


A          R RJLF2                     JFILE(PF1 PF2)       
A          J                           JOIN(PF1 PF2)         
A                                      JFLD(ROLLNO ROLLNO)
A                                      JFLD(CLASS CLASS) 
A                                      JDUPSEQ(SUBJECT *DESCEND) 
A            ROLLNO                    JREF(PF1)
A            CLASS                     JREF(PF1)  
A            STUNAME                                         
A            AGE                                               
A            SUBJECT

Data in join logical file (JLF2) in descending order by SUBJECT


ROLLNO      CLASS  STUNAME          AGE   SUBJECT   
1              1   NAME1              6   PHYSICS   
1              1   NAME1              6   MATHS     
1              1   NAME1              6   CHEMISTRY 
2              1   NAME2              5   BIOLOGY   
********  End of data  ********                                                   

R

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

JFILE(PF1 PF2)

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.

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(PF1 PF2)

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).

JFLD(ROLLNO ROLLNO)

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.

JFLD(CLASS CLASS)

Joined based on CLASS field in both the physical files PF1 and PF2 as well.

JDUPSEQ(SUBJECT)

Join duplicate sequence keyword default sorts in ascending order. So the data would be arranged in JLF based upon the JDUPSEQ keyword.

JDUPSEQ(SUBJECT *DESCEND)

Join duplicate sequence keyword sorts in descending order as *DESCEND as parameter keyword specified for JDUPSEQ.

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.

JREF(PF1)

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, ROLLNO fields are present in both the files PF1 and PF2 and JLF must know in advance that from where the field comes from.

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

Post a Comment

© AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain