Write a Query program to display a file, using multiple files with matched records

In the expanding world of data processing, it is usually impossible to learn all details of every system. Often, programmers are asked to research information or produce reports from application systems that are too vast to comprehend easily. Even if the system is documented, reading the documentation may take longer that the timeframe allows.

Imagine, that on your third day as an AS/400 programmer, your boss tells you that the sales department has an emergency need for a list of all customers who have ever ordered a Widget123 from your company. Here are the three steps to take.

Step 1 - Show Your Libraries

Use the command "WRKLIB *ALLUSR" to see a list of the user libraries on your system; see Figure 1. Hopefully, you will see a library that looks interesting. In this case, the library "SLSFILES" looks promising.

Step 2 - Show the Files

Now, to see the files in the "SLSFILES" library, use the command:

WRKOBJ SLSFILES/*ALL *FILE

You now see a list of all files in this library; see Figure 2. There may be quite a few, so page down through the list. In our example, the order detail file, ORD looks like it could be what we want.

Step 3 - Query the Data

Now, use Query/400 to view the data. There are several ways to start query. I use the command WRKQRY. Use option 1 to create a query.

Query insists that you first choose a file to use, so it puts a "1" next to the "Specify file selections". Hit enter and fill in the file name "ORD" and the library "SLSFILES". Every time you finish a selection, you will return to the "Define the Query" screen; see Figure 3.

If you key "1" next to "Select and sequence fields", you see a list of the fields in the file. If the field descriptions are not visible, use "F11" to change the view so that you see the field names and their descriptions; see Figure 4.

Query says that "F5=Report" but it really means "run the query now". This is a good time to hit "F5" to get a formatted view of the file. You may need to use "F19" and "F20" to shift the view left and right.

From the "Define the Query" screen, key "1" next to the "Select records". Now key in the selection criteria to select only records in which "ODPRDS EQ WIDGET123". Run the query again and you now have a list of orders that include WIDGET123.

Query/400 has a simple and intuitive interface. Work through the selections to choose which fields to display, which records to select, how to sort the records, accumulate totals, change column headings and format numbers. The output can be a display query, a report or a database file. You can join files so that, in our case, you could join the "CUS" file to the "ORD" file to include the customer name and customer salesman in the query.

You are now armed with a powerful approach for quickly researching problems. You will be amazed at how powerful Query/400 is and at how easy it is to use.



Figure 1 - Show Your Libraries

Results of "WRKLIB *ALLUSR"
                                                                    
 Opt  Library    Attribute   Text                                  
      
  _    GLPGMS    PROD        G/L Programs                                                                
  _    GLTEST    PROD        G/L Test                                       
  _    GL123199  PROD        G/L files from end of year 1999                                       
  _    MANFILES  PROD        Manufacturing Files                                              
  _    MANPGMS   PROD        Manufacturing Programs                  
  _    SLSFILES  PROD        Sales Files                                               
  _    SLSPGMS   PROD        Sales Programs                                              
  _    TEST      PROD                                              
  _    WDOE      PROD        William Doe Test Library                                                      
                                                                     


Figure 2 - Show the Files

Results of "WRKOBJ SLSFILES/*ALL *FILE"
                                                                           
 Opt  Object    Type    Library   Attribute   Text                       
  _   ADM       *FILE   SLSFILES    PF        Admin Masterfile
  _   ADM01     *FILE   SLSFILES    LF        Admin by Name             
  _   CTY       *FILE   SLSFILES    PF        City Masterfile    
  _   CUS       *FILE   SLSFILES    PF        Customer Master  
  _   CUS01     *FILE   SLSFILES    LF        Customer by name               
  _   CUS02     *FILE   SLSFILES    LF        Customer by city           
  _   INV       *FILE   SLSFILES    PF        Invoice Master             
  _   ORH       *FILE   SLSFILES    PF        Order Header 
  _   ORH01     *FILE   SLSFILES    LF        Order Header by name       
  _   ORD       *FILE   SLSFILES    PF        Order Detail               
  _   ORD01     *FILE   SLSFILES    PF        Order Detail by part#      
                                                                        More... 
Figure 3 - Query Screen Define the Query Query . . . . . . : ORDLOOK Option . . . . . : CREATE Library . . . . : SLSFILES CCSID . . . . . . : 65535 Type options, press Enter. Press F21 to select all. 1=Select Opt Query Definition Option > Specify file selections Define result fields Select and sequence fields Select records Select sort fields Select collating sequence Specify report column formatting Select report summary functions Define report breaks Select output type and output form Specify processing options F3=Exit F5=Report F13=Layout F18=Files F21=Select all Select options, or press F3 to save or run the query. Figure 4 - Select and Sequence Fields Select and Sequence Fields Type sequence number (0-9999) for the names of up to 500 fields to appear in the report, press Enter. Seq Field Text Len Dec ODSTAT Status 1 ODCUS# Customer # 10 0 ODORDT Order Date 8 0 ODPAR# Part# 7 0 ODPRDS Part Description 30 OD#ORD # Items Ordered 5 0 ODWHLC Warehouse Location 3 ODBKOR Back Order Flag 1 ODSHDT Ship Date 8 0

 

Back to Basic 400 Skills   |   Main Page