Rabu, 03 April 2013

AS400 Showcase Article - Query

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     
                                                                                    
                                                                               
                                     

Tidak ada komentar:

Posting Komentar