Showcase Article - Query/400
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