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:
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:
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:
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:
Lets make a display that shows the following columns:
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:
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:
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:
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.
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:
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:
You should see:
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:
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)
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!
----------------------------------------------------------------
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.
-----------------------------------------------------------------
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.
--------------------------------------------------------------
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.
----------------------------------------------------------------
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.
cust name
cust number
cust adr1
cust city
cust state
cust zip
---------------------------------------------------------------
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.
-------------------------------------------------------
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?
----------------------------------------------------------
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.
-------------------------------------------------------------
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:
-----------------------------------------------------------------
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.
--------------------------------------------------------------
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.
---------------------------------------------------------------
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.
----------------------------------------------------------------
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.
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