Creating an as400 Query

Creating an as400 Query

As400 Query, QUERY/400 as it used to be called or IBM Query for Iseries, which is whats shown these days on the Installed Licensed Programs screen (GO LICPGM Option 10) is probably one of the most widely used tools on the as400. This tool has a very straightforward user interface, so is often used by both technical and non technical staff (with relevant training of course). Its a very easy to use tool for displaying, extracting and reporting on data. Query is a separate chargeable product, but I don’t think I have ever worked at a site that didn’t have it installed on all their systems, so you can be pretty sure almost every AS/400 will have the IBM provided query language QUERY/400.

As an example, we will write a query over a typical database file that almost every business system has, the CUSTOMER master file. After extracting the data we will create a report.
At the as400 command line type in WRKQRY and hit ENTER:

----------------------------------------------------------------
Work with Queries

Type choices, press Enter.

Option . . . . . . 1=Create, 2=Change, 3=Copy, 4=Delete
5=Display, 6=Print definition
8=Run in batch, 9=Run
Query . . . . . . . Name, F4 for list
Library . . . . . QGPL Name, *LIBL, F4 for list
-----------------------------------------------------------------

We will create a query to view the data in the CUSTOMER master file. Type in 1 for the option to create a query and the press ENTER.

-----------------------------------------------------------------
Define the Query

Query . . . . . . : Option . . . . . : CREATE
Library . . . . : QGPL 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
-----------------------------------------------------------------

Next "Specify file selections". This is already selected by default 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 . . . . . . QGPL Name, *LIBL, F4 for list
Member . . . . . . . . *FIRST Name, *FIRST, F4 for list
Format . . . . . . . . *FIRST Name, *FIRST, F4 for list

---------------------------------------------------------------
Enter CUSTOMER (or whatever your customer master file is called) as the file you want to query and YOURLIB (replace with your relevant database library) as the Library and press ENTER. Pressing ENTER again will take you back to the Define the Query screen.

The next option down, the Define result fields option, is described thus: “Select this option either to define one or more new fields to
be used as result fields or to see what result fields are already defined for the query. The new fields will hold the results of arithmetic, character, or date and time operations done on other fields in this query”. In this case we don’t need any result fields so we can ignore this option for now.

We do need to select fields that we want to report on. Type 1 next to "Select and Sequence Fields" and press ENTER
You should now see:

----------------------------------------------------------------
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
CUSTN Customer Number 10 0
CUSTNA1 Cust. Name & Address 1 50
CUSTNA2 Cust. Name & Address 2 50
CUSTNA3 Cust. Name & Address 3 50
CUSTNA4 Cust. Name & Address 4 50
CUSTTOWN Customer Town 10
CUSTREPN Customer Report Name 20
LOCCD Location Code 5


More...
F3=Exit F5=Report F11=Display names only F12=Cancel
F13=Layout F20=Renumber F21=Select all F24=More keys
----------------------------------------------------------------

Since the AS/400 Query knows the file you want to see, as you selected it earlier, it has retrieved the fields in the file and listed them for you to choose from.
Weare going to select the following columns:
cust number
cust name and address 1
cust town
location code
We need to enter a number to select each of the fields that we require, so enter a number next to each field, which will determine the order in which they are displayed. Enter 2 next to CUSTNA1, 1 next to CUSTN, 4 next to CUSTTOWN, and 6 next to LOCCD. Press ENTER twice and then you will be back at the definition screen.

as400 QUERY allows you to view the results of the query while you are modifying it, which is handy as it allows you correct any errors there and then

---------------------------------------------------------------
Display Report
Report width . . . . . : 115
Position to line . . . . . Shift to column . . .
....1....+....2....+....3....+....4....+....5....+....6....+....7
Customer Name and Address 1 Town Location
Number Code
000001 000001 Davis, 2 High St London 010
000002 000012 Charles, 1 High St London 010
000003 000015 Fish, 3 High St London 010
000004 000020 Brompton, 4 High St London 010

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

This is the first steps to creating a query that shows records on an as400 database file.

The standard IBM CUA window keys are available on this screen. F20 will move the data window to the right, F19 will move the data window back to the left. F21 will split the screen into two mini windows, one fixed and one moveable, so you can view 2 parts of the data similultaneously. F12 to cancel returns to the main definition screen

Next is to take option 1 for Column Formatting to modify the column headings to be more descriptive. This allows you to change them to be more meaningful to the out being produced.

To change the sequencing of the query, type 1 next to "Select Sort Fields". Typing a 1 next to the customer number field will ensure that the data is produced in customer number order. Pressing F5 will allow review of the result of applying the sort to the customer number field.

The next step is to decide which records to select. Enter a 1 next to select records. In this case we only want to select records with a location code of '030'. The screen will 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
LOCCD EQ '030'

----------------------------------------------------------
Now the extract will only show customers with a location code of '030'.

Now we need to save the query. Pressing F3 and filling in detals give:

-------------------------------------------------------------
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 . . . . . . . TEMPQRY Name
Library . . . . . QGPL Name, F4 for list

Text . . . . . . . . Display CUSTOMER Master File
---------------------------------------------------------------

And to run the query, from an AS400 command line, key in the command to run a query:

RUNQRY QGPL,TEMPQRY

This should show the results for the as400 query.