Write a Query program to print a file

Most AS/400's have the IBM provided query language QUERY/400. If is an easy to use and powerful tool for viewing and printing data. Here, we will write a query to see some of the data in your CUST file. Then, we'll copy it and change it to create a printed report. Then, we'll learn how to view printed reports. Finally, I'll have a few comments on how you could actually print reports on your laser printer (don't get too excited though, its expensive).

Make sure you have read through the earlier topic "Library Lists and Copy File". In that topic you should have copied a file named CUST to your user library. From here on, whenever you see USER999 be sure to substitute your user library.

There are several ways to get to the Query programming screen. My favorite is with the command WRKQRY. After keying in WRKQRY and hitting ENTER you should see:


----------------------------------------------------------------
                               Work with Queries                             
                                                                             
 Type choices, press Enter.                                                  
                                                                             
   Option  . . . . . .                  1=Create, 2=Change, 3=Copy, 4=Delete 
                                        5=Display, 6=Print         
                                        8=Run in batch, 9=Run                
   Query . . . . . . .                  Name, F4 for list                    
     Library . . . . .                  Name, *LIBL, F4 for list             
-----------------------------------------------------------------
This interface is easy to use but is different from PDM. One of the unexpected things is that you can write a query without ever naming it. Of course, you must name it if you wish to save it.

We are going to write a query to view the data in the CUST file. Key in 1 for the option to create a query. We'll name it later so for now just hit ENTER.

You should see:


-----------------------------------------------------------------
                                Define the Query                      
                                                                      
 Query . . . . . . :                Option  . . . . . :   CREATE 
   Library . . . . :     ZET        CCSID . . . . . . :   65535  
                                                                      
 Type options, press Enter.  Press F21 to select all.                 
   1=Select                                                           
                                                                      
 Opt    Query Definition Option                                       
  1     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                                    
-----------------------------------------------------------------                                                                      
If you have ever worked with a database query product (like Microsoft's Access) this will have a familiarity to it. Microsoft Access needs about the same information as the AS/400. It just presents the options in a way that you point and click. With QUERY/400, you must view lists and select by keying in "1" next to each item.

The essential things that a query needs to know are:

The most important question is "what file are you displaying?" This is so important that the AS/400 has already put a "1" next to "Specify file selections". SO, just hit ENTER and you will see:


--------------------------------------------------------------
                            Specify File Selections                   
                                                                      
 Type choices, press Enter.  Press F9 to specify an additional        
   file selection.                                                    
                                                                      
   File . . . . . . . . .             Name, F4 for list          
     Library  . . . . . .             Name, *LIBL, F4 for list   
   Member . . . . . . . .   *FIRST    Name, *FIRST, F4 for list  
   Format . . . . . . . .   *FIRST    Name, *FIRST, F4 for list  
                                                                                                                                              
---------------------------------------------------------------
You are simply specifying which file you want to query. Enter CUST and the file and USER999 as the Library and hit ENTER. Hit ENTER again and you go back to the previous screen.

Now, select fields you want to see. Do this by keying "1" next to "Select and Sequence Fields". Use either the TAB key or the "down-arrow" to get to that line and key in "1" and hit ENTER.

You should now see:


----------------------------------------------------------------
                         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                                       
      CSNBR             CUST NUMBER                                
      CSNAME            CUST NAME                                  
      CSADR1            ADDRESS LINE 1                             
      CSCTY             CITY                                       
      CSSTE             STATE                                      
      CSZIP             9 DIGIT ZIP                                
      CSDLOR            DATE OF LAST ORDER                         
      CSDLPM            DATE OF LAST PMT                           
      CS#OPN            # OF OPEN ORDERS                           
      CS$OPN            AMT OF OPEN ORDERS                         
                                                                    
----------------------------------------------------------------
Since the AS/400 knows the file you want to see, it has retrieved the fields in the file and listed them for you. If there were more fields, you would see the now familiar "more..." at the bottom right. "more..." tells you that if you use the PAGE-DOWN key you will see more.

Lets make a display that shows the following columns:

Cust Name
Cust Number
Cust Adr1
Cust City
Cust State
Cust Zip

So, key in a sequence number next to each field. Key 2 next to CSNBR, 1 next to CSNAME, 3 next to CSADR1, 4 next to CSCTY, 5 next to CSSTE and 6 next to CSZIP. Then hit ENTER twice and you'll be back at the main screen.

One of the great things with QUERY/400 is that you can work on query, then view it, then change it, view it again and change it until it is what you want.

F5 says that F5=Report. Maybe it should say F5=Run. If you hit F5 the query will run. It should look like:


---------------------------------------------------------------
                                 Display Report                               
                                              Report width . . . . . :     115
 Position to line  . . . . .              Shift to column  . . .         
  ....1....+....2....+....3....+....4....+....5....+....6....+....7
        CSNAME                            CSNBR   CSADR1                      
 000001 E LUMPKIN                         1,002   123 MAIN STREET             
 000002 CHISOLM                           1,084   123 MAIN STREET             
 000003 HAGGINS                           1,105   123 MAIN STREET             
 000004 BYRD                              1,109   123 MAIN STREET             
 000005 ROMERO                            1,168   123 MAIN STREET             
 000006 POUNDS                            1,177   123 MAIN STREET             
 000007 HINTON                            1,183   123 MAIN STREET             
 000008 YANCEY                            1,184   123 MAIN STREET             
 000009 MCDOUGLE                          1,186   123 MAIN STREET             
	
-----------------------------------------------------------------
Just like that you have written a query to display data from an AS/400 database file. Notice a few things about the query display: it says "more..." so you can hit PAGE DOWN to go down a page. At the top is an area to position to any line. You could key in 50 and hit ENTER and the query would advance to line 50. You can also enter "B" (for bottom) and hit enter to go to the bottom or "T" to get back to the top.

Notice the F-KEY prompts at the bottom of the screen. If you hit F20 (Shift F8) you will scroll to the right. F19 (Shift F7) scrolls back to the left. You can even split the screen with F21 (Shift F9).

Once you have played around with that for a while, hit F12 (cancel) to get back to the main screen.

Now change the titles of the rows to be description. Do this by keying "1" next to Column Formatting. Key in more descriptive columns like Name, Customer#, etc.. You can use all three lines for the column heading.

Anytime you want, hit F5 to see the data.

You might revisit the "Select and Sequence Fields" selection to add a field or move fields around.

If in the process of working on the query you hit F3 at the wrong time, you will see the EXIT screen which looks like:


-------------------------------------------------------
                                Exit this Query                   
                                                                  
 Type choices, press Enter.                                       
                                                                  
   Save definition  . . .   Y              Y=Yes, N=No            
                                                                  
   Run option . . . . . .   3              1=Run interactively    
                                           2=Run in batch         
                                           3=Do not run           
                                                                  
   For a saved definition:                                        
     Query  . . . . . . .                  Name                   
       Library  . . . . .     ZET          Name, F4 for list      
                                                                  
     Text . . . . . . . .                                         
                                                                  
     Authority  . . . . .   *LIBCRTAUT     *LIBCRTAUT		
					               *CHANGE, *ALL, *EXCLUDE
----------------------------------------------------------
You can hit F12 to get back to your query. Boy that wasn't very obvious was it?

Now key "1" next to "Select Sort Fields". Key a "1" next to CSNAME and hit ENTER. Now hit F5 and your query will sort the data by name when it displays.

Notice that this is real-world data. Some of the names start with a space and are therefore sorted to the beginning.

Finally, key a "1" next to select records. Lets select only the records for accounts that live in "TX". Fill in the screen to look like:


----------------------------------------------------------
                                 Select Records              
                                                             
 Type comparisons, press Enter.  Specify OR to start each new
   Tests:  EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISN
                                                             
 AND/OR  Field             Test   Value (Field, Number, 'Char
         CSSTE             EQ     'TX'                       
                                                             
 ----------------------------------------------------------
Now when you hit F5 you will see only accounts in Texas and in sequence by name.

You can also create totals of columns and break totals. You can specify if you want details (like this) or summary only.

For now, quit and save this query. Hit F3 and fill in the screen to save the query as CSQ001.


-------------------------------------------------------------
                                Exit this Query               
                                                              
 Type choices, press Enter.                                   
                                                              
   Save definition  . . .   Y              Y=Yes, N=No        
                                                              
   Run option . . . . . .   3              1=Run interactively
                                           2=Run in batch     
                                           3=Do not run       
                                                              
   For a saved definition:                                    
     Query  . . . . . . .   CSQ001         Name               
       Library  . . . . .     USER999      Name, F4 for list  
                                                              
     Text . . . . . . . .   Display CUST File                 
---------------------------------------------------------------
Now, from a command line, key in the command to run a query: RUNQRY USER999/CSQ001

Your query should display what you saw earlier when you hit F5.

So, that was pretty incredible. In just a few minutes, you wrote and saved it so that it can be run from the command line. Next topic will show you how to put that on a menu.

Now, copy the query and change it to print a report.

To do that, work with queries again (WRKQRY). Then fill in the screen like:


-----------------------------------------------------------------

                               Work with Queries                  
                                                                  
 Type choices, press Enter.                                       
                                                                  
   Option  . . . . . .   3              1=Create, 2=Change, 3=Copy
                                        5=Display, 6=Print
                                        8=Run in batch, 9=Run     
   Query . . . . . . .   csq001         Name, F4 for list         
     Library . . . . .     USER999      Name, *LIBL, F4 for list  
                                                                  
-------------------------------------------------------------- 
Hit ENTER and you will see the "Copy Queries" screen. Under "To Query" change the query name from CSQ001 to CSQ002 and hit ENTER.

There is now a query named CSQ002 that is identical to CSQ001. Open that query up so you can change it to print. Do this by keying:


--------------------------------------------------------------
                               Work with Queries          
                                                          
 Type choices, press Enter.                               
                                                          
   Option  . . . . . .   2              1=Create, 2=Change
                                        5=Display, 6=Print
                                        8=Run in batch, 9=
   Query . . . . . . .   CSQ002         Name, F4 for list 
     Library . . . . .     USER999      Name, *LIBL, 

---------------------------------------------------------------
Put "1" next to "Select Output Type and Output Form" and hit ENTER.

You should see:


---------------------------------------------------------------
                      Select Output Type and Output Form    
                                                            
Type choices, press Enter.                                  
                                                            
  Output type  . . . . . . . . . . .   1     1=Display      
                                             2=Printer      
                                             3=Database file
                                                            
  Form of output . . . . . . . . . .   1     1=Detail       
                                             2=Summary only 
                                                            
  Line wrapping  . . . . . . . . . .   N     Y=Yes, N=No    
    Wrapping width . . . . . . . . .         Blank, 1-378   
    Record on one page . . . . . . .   N     Y=Yes, N=No    
                                                            
---------------------------------------------------------------
You can see that the AS/400 defaults each query to display detail information without line wrapping. You can experiment with the line wrapping later. For now, change the Output Type form 1 to 2 and hit ENTER.

On the "Define Printer Output", hit ENTER to accept all of the defaults. Also hit ENTER for the "Define Spool Output" screen.

You can try different values here but for now, change the "Print Cover Page" form "Y" to "N" and hit ENTER.

You should now see the "Specify Page Headings and Footings". Fill it in with something like:


----------------------------------------------------------------
                       Specify Page Headings and Footings           
                                                                    
 Type choices, press Enter.                                         
   (Type &date, &time, and &page, or choose standard page headings.)
                                                                    
   Print standard                                                   
     page headings  . . . . .   Y             Y=Yes, N=No           
                                                                    
   Page heading                                                     
 Customer File                                                      
 Texas Only                                                         
 Sorted by Name                                                     
                                                                    
   Page footing                                                     
 CSQ002                                                             
---------------------------------------------------------------
I've put in a three line heading and entered the query name as a footer. Hit ENTER to get back to the main query screen.

Note that if you hit F5 right now, you will see exactly what the other query saw. The headings and footers are not displayed. They will show up in the report though.

Hit F3 to get to the EXIT screen and save the query.

Now run the query from the command line by keying in: RUNQRY USER999/CSQ002

The query has now created a report and written it to the Spool File. The Spool File is a storage area for the report. If you had an AS/400 printer connected and ready to print, the file would print to the report. Since you don't have an AS/400 printer, the report is still sitting in the Spool File.

There are many Spool File areas on the AS/400. Which one is yours in? It depends on the values used to set up your user profile. That is, when a user ID is defined, the printer for that user ID is also defined. In your case, it isn't really a printer... it's a dummy printer.

So, how can you look at the reports in your Spool File. You use the "Work with Spool Files" command. Key:

WRKSPLF and hit ENTER.

You will see the Spool File with all the reports in it. Many of them will have odd names and some may be error logs generated automatically. For example, if you disconnect from the internet before you sign off, the AS/400 may generate a system log report of your "abnormally terminated" job.

Reports created by QUERY/400 are given the messy name of QPQUPRFIL. So you should now see a list of all the reports in your Spool File.

Near the top of the screen you can see the options you can use. The only ones you should need are 4=DELETE and 5=DISPLAY.

By keying the option number "5" next to a report and hitting enter, you can see the report. Here you can see your headings. Similar to the interface for QUERY/400 you can: F20 to scroll right (shift F8)
F19 to scroll left (shift F7)
Key in "B" and hit ENTER to go to the bottom
Key in "T" and hit ENTER to go to the top

Once you've looked at your report, exit out of it by hitting F3. Now put a "4" next to it in the Spool File list and hit ENTER and you have deleted it.

Experiment by making several reports. Do this by running your query several times. Then use WRKSPLF to view your spool file.

Feel free to delete all of the reports in your Spool File. I try to keep mine empty. Especially, feel free to delete the reports you don't understand because they were created by the system.

In conclusion, here is a short discussion on routing print data to your printer.

Not too long ago, all AS/400 printers were connected directly to AS/400's or AS/400 remote controllers. With the internet has come the ability to easily route print data to a remote. Doing so requires the client machine (that is, your PC) to have an AS/400 Telnet Client that is capable of creating virtual print devices.

With such a Telnet Client, you can easily print reports, like this query, directly to your printer. These Telnet Clients are about $150 to $200. I like the one sold by Synapse (www.synapse.com). There are others including IBM's Client Access.

You do not need print capabilities to do this tutorial. I rarely print reports out. I never print programs out. It is important to realize how easily your reports could be printed on a PC printer though. A $200 investment for a company to be able to use a remote AS/400 is not very much money.

Additional reading: There is an article written for Showcase on Queries.     Click here to read it!

 

Back to Basic 400 Skills   |   Main Page