AS400 Join Logical File and SQL View tutorial

In this tutorial we are going to learn how to create a Join Logical File and a SQL view between two tables(physical files). You can create a view based on more than two tables. The beauty of this newly created file or the view is that you can query it to get your data in a single IO rather than programmatically accessing each file individually. That's much efficient programming practice. Before we create a Join Logical File or a SQL view lets create a few Physical files.

Source for Product Master File - PRODUCTS

      * Product Master File                                            
     A                                      UNIQUE                     
     A          R PRDREC                                               
      *                                                                
     A            COMPANY        3S 0       COLHDG('Company')          
     A            ITEMNO        20A         COLHDG('Item Number')      
     A            DESC1         50A         COLHDG('Description 1')    
     A            DESC2         50A         COLHDG('Description 2')    
     A            PRICE         11S 2       COLHDG('Price')            
     A            WEIGHT         9S 0       COLHDG('Weight')           
     A            CATEGORY      10A         COLHDG('Category Code')    
     A            MFG           10A         COLHDG('Manufacturer Code') 
      *                                                                
     A          K COMPANY                                              
     A          K ITEMNO 

Source for Product Categories Master File - CTGTABLE

      * Product Categories Master File                             
     A                                      UNIQUE                 
     A          R CTGREC                                           
      *                                                            
     A            COMPANY        3S 0       COLHDG('Company')      
     A            CATEGORY      10A         COLHDG('Category Code') 
     A            CATNAME       50A         COLHDG('Category Name') 
      *                                                            
     A          K COMPANY                                          
     A          K CATEGORY                                         

Source for Product Manufacturer Master File - MFGTABLE

      * Product Manufacturer Master File                               
     A                                      UNIQUE                     
     A          R MFGREC                                               
      *                                                                
     A            COMPANY        3S 0       COLHDG('Company')          
     A            MFG           10A         COLHDG('Manufacturer Code')
     A            MFGNAME       50A         COLHDG('Manufacturer Name')
      *                                                                
     A          K COMPANY                                              
     A          K MFG                                                  

Now lets create a Join Logical File combining the information available in all our tables so we don't have to fetch them one by one. A join logical file is a logical file that combines (in one record format) fields from two or more physical files. In the record format, not all the fields need to exist in all the physical files.

Source for Product Information Join Logical File - PRDINFO

      * Join between Product, Category and Manufacturer Master Tables 
     A                                      JDFTVAL                  
     A          R JOINREC                   JFILE(PRODUCTS +         
     A                                            CTGTABLE +         
     A                                            MFGTABLE)          
      * Join between Product and Category Tables                     
     A          J                           JOIN(PRODUCTS CTGTABLE)  
     A                                      JFLD(COMPANY  COMPANY)   
     A                                      JFLD(CATEGORY CATEGORY)  
      * Join between Product and Manufacturer Tables                 
     A          J                           JOIN(PRODUCTS MFGTABLE)  
     A                                      JFLD(COMPANY  COMPANY)   
     A                                      JFLD(MFG      MFG)       
      *                                                              
     A            COMPANY                   JREF(PRODUCTS)           
     A            ITEMNO                                             
     A            DESC1                                              
     A            DESC2                                              
     A            PRICE                                              
     A            WEIGHT                                             
     A            CATEGORY                  JREF(PRODUCTS)           
     A            CATNAME                                            
     A            MFG                       JREF(PRODUCTS)            
     A            MFGNAME   
      *                     
     A          K COMPANY   
     A          K ITEMNO    

Now instead of a Join Logical lets create a SQL View that will give us the exactly same information

Source for Product Information SQL View

CREATE VIEW {Library_name}/PRODUCT_VIEW as (                              
    SELECT  PRODUCTS.COMPANY, PRODUCTS.ITEMNO, PRODUCTS.DESC1, 
        PRODUCTS.DESC2, PRODUCTS.PRICE, PRODUCTS.WEIGHT,       
        PRODUCTS.CATEGORY, CTGTABLE.CATNAME,                   
        PRODUCTS.MFG, MFGTABLE.MFGNAME                         
    FROM PRODUCTS left outer join CTGTABLE on                  
        PRODUCTS.COMPANY  = CTGTABLE.COMPANY  and              
        PRODUCTS.CATEGORY = CTGTABLE.CATEGORY                  
    left outer join MFGTABLE on                                
        PRODUCTS.COMPANY  = MFGTABLE.COMPANY  and              
        PRODUCTS.MFG      = MFGTABLE.MFG                       
)        

Please note: You cannot specify a key when creating a SQL View. In case of Join Logical you can define key fields from the Primary file. Usually SQL views preform better but it all depends on what indexes you have defined over the Physical file(TABLE).