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!