Read an Excerpt
The iSeries and AS/400 Programmer's Guide to Cool Things
By Mike Faust MC Press
Copyright © 2006 MC Press Online, LP
All rights reserved.
ISBN: 978-1-58347-699-4
CHAPTER 1
Creating Reports with Query Manager/400
To start, we examine how to use Query Manager/400 as an alternative to writing RPG programs or using Query 400 to create custom reports. QM/400 gives you the ability to create printed reports with nearly all the flexibility of writing a program in RPG in only slightly more time than it takes to create a query using Query/400. The best part is that you can easily convert a Query/400 query definition into QM/400.
Let me start out by defining a few terms. Query management queries are a standard part of OS/400. These are created using source files containing an SQL statement defining the data for your query and a separate source file member containing the definition of the report layout. The term Query Manager as used in this chapter refers to DB2 UDB Query Manager for AS/400, which is part of DB2 Query Manager and SQL Development kit licensed program number 5769ST1. This product is a more user-friendly interface to Query Management queries and forms.
There are two basic components used to generate a report using QM/400. These are the Query Manager query, which is used to define what data will appear on your report, and the Query Manager report form, which is used to define the look of the report, including column and page headings and level breaks. This means that you can create one Query Manager report form that can be used by one or many Query Manager queries. This in itself can help to greatly reduce your development time for creating new reports because reports with a common look can use the same Query Manager report form but use unique Query Manager queries to define their data sets.
The flexibility in QM/400 is partially achieved through the ability to insert parameters into your Query Manager query and form. Using these parameters you can pass information such as report criteria (i.e., customer numbers or date ranges) into your report. You can also use these parameters to pass information into your Query Manager report form when you print the headings and footings, including page headings or footings. The ability to insert parameters or field values into headings is a great feature because it allows you to show these inserted values right on the report. We'll get into exactly how to do this a little later in this chapter. Let's start by going over the various ways to create Query Manager queries.
There are three ways to create a report using QM/400:
* Convert a Query/400 QUERY definition
* Use STRQM command to display the QM/400 main menu
* Create a Query Manager query, form a source member, and compile it.
Which of these routes you take greatly depends on your starting point. For example, if you had an existing query definition that was similar to the report that you want to generate with QM/400, you would convert that query definition to a Query Manager query and form. On the other hand if you were generating the report completely from scratch, you would probably want to use the QM/400 menu. In most cases you won't want to use the third option because creating this source can be tedious and doesn't have any advantages over using the QM/400 menu.
To start, let's examine converting a Query/400 query definition into QM/400. This process involves splitting the query into a Query Manager query and a Query Manager report form. Before we can do this we need to create source files to contain the Query Manager query and form source members. Use the Create Source Physical File (CRTSRCPF) command and create two source files, one named QQMQRYSRC, which will be used to store the query source, and QQMFORMSRC, which will be used to store the form source.
CRTSRCPF FILE(LIB/QQMQRYSRC)
CRTSRCPF FILE(LIB/QQMFORMSRC)
We need to create these files because to convert a Query/400 query definition into QM/400 we must first build the source for the query and form from the query definition.
To create the source for the Query Manager query we'll use the Retrieve Query Manager Query (RTVQMQRY) command by entering the following command:
RTVQMARY QMQRY(library/queryname) SRCFILE(library/QQMQRYSRC)
ALWQRYDFN(*YES)
The key parameter here is the Allow Information from a Query Definition (ALWQRYDFN) parameter, which enables the command to derive the Query Manager query source from a Query/400 query definition.
RTVQMFORM QMFORM(library.queryname) SRCFILE(library/QQMFORMSRC)
ALWQRYDFN(*YES)
To build the second part of the puzzle, the Query Manager report form, we must use the Retrieve Query Manager Report Form (RTVQMFORM) command. This command is very similar to RTVQMQRY and is executed by entering:
RTVQMFORM QMFORM(library/queryname) SRCFILE(library/QQMFORMSRC)
ALWQRYDFN(*YES)
Once we have retrieved this source, we must create the QM/400 objects by compiling these source members. We create the Query Manager query by using the Create Query Manager Query (CRTQMQRY) command by typing:
CRTQMQRY QMQRY(library/qmquery) SRCFILE(library/QQMQRYSRC)
Finally, we use the Create Query Manager Report Form (CRTQMFORM) command to compile to form source by typing:
CRTQMFORM QMFORM(library/qmform) SRCFILE(library/QQMFORMSRC)
At this point you may be thinking that this seems like a lot of work just to convert our query definition into QM/400, but when you see what you can do with QM/400 you'll see why it really is worth the effort.
Query Manager Queries
To work with the QM/400 objects, we must display the QM/400 main menu. This is accessed by typing the Start Query Manager/400 (STRQM) command, which has no parameters. Figure 1.1 shows the Query Manager/400 main menu.
Option 1 on this menu is used to work with Query Manager queries. Option 2 allows you to work with Query Manager report forms. Option 3 allows you to create or modify tables to be used within QM/400. Option 10 on this menu allows you define the level of access that individual user profiles have. Enter option 1 to display the Work with Query Manager Queries screen shown in Figure 1.2.
There are two different styles of query that can be created from this screen, prompted and SQL. A prompted query is defined using screens similar to those used to define a query in Query/400. Selecting Specify Tables from the Define Prompted Query screen allows you to select which database files will be used within your query and how they will be joined. The Define Expressions screen is used to create fields within your query whose values are calculated based on fields from your database file, for example, calculating extended value based on unit cost times units sold. Select and Sequence Columns allow you to choose which fields from your database files will be included in the query and in what order they are to appear. The Select Rows screen lets you specify which records to include in your query. Select Summary Functions is used when grouping records to define which type of summary (AVERAGE, SUM, COUNT, etc.) should be used on the columns of your query.
The next option on the Define Prompted Query screen is not an option that is available in Query/400. The Specify Duplicate Rows option allows you to tell Query Manager how to handle duplicate records within your query — that is, those of the fields selected for your query that have identical values on two or more rows. The options are
1. Keep duplicate rows
2. Keep only the first copy of each row.
To define collating sequences for the data in your query, use the Define Sort Sequences screen. The Define Report Formatting screen allows you to state which Query Manager report form is to be used with this query. In general, prompted queries are good for someone who is not familiar with SQL programming or someone who is looking to get their feet wet in Query Manager/400. Prompted queries do not, however, allow you to use parameters.
SQL queries, on the other hand, are created using standard SQL code. For example:
SELECT CUSNAM, CUSAD1, CUSAD2, CUSCTY, CUSSTE, CUSZIP,
FROM CUSTMAST WHERE CUSSTE='PA'
would display list the name and address fields for all customers in Pennsylvania. The fact that this type of query uses the power of SQL, in addition to being able to insert parameters into your SQL code, makes SQL queries much more flexible than prompted queries. The mode you choose to use to create your queries can be changed from the Work with Query Manager Queries display by pressing F19. The default mode for each user is set in the Query Manager profile, which is accessed through option 10 on the Query Manager main menu.
Table 1.1 shows a listing of each of the functions on the Work with Query Manager Queries screen and gives descriptions of their use.
You can convert a prompted query into an SQL query from the Work with Query Manager Queries screen using option 10. It's important to note that this process cannot be reversed and there is no way to convert an SQL query into a prompted query. Also Query Manager queries created from source retrieved from a Query/400 query definition will always be SQL queries.
Inserting parameters into your SQL statements gives you the ability to create SQL queries that are fed their selection criteria each time they are run. This is as simple as replacing a constant in the WHERE clause with a variable name preceded with an ampersand (&) sign. The above example would look like this:
SELECT CUSNAM, CUSAD1, CUSAD2, CUSCTY, CUSSTE, CUSZIP
FROM CUSTMAST WHERE CUSSTE=&STATE
When the query is run you can supply the value on the Start Query Manager Query (STRQMQRY) command using the Set Variable (SETVAR) parameter. In our earlier example this would be:
STRQMQRY CUSTLIST SETVAR(STATE 'PA')
If no SETVAR parameter is used, the user is prompted to enter the value.
When the variable used is being compared to an alphanumeric field, the value fed into the variable must contain the required value enclosed in single quotes. If quotes are not used, Query Manager will misinterpret the value as a field name and an error will be returned. This also means that you can insert field names or entire portions of the SQL code as variables. You could replace the entire WHERE clause above with a variable and build the WHERE clause dynamically at run time. This allows you to create generic Query Manager queries and allow a CL application to control which data is selected for your report.
To do this using our previous example, we replace the entire WHERE clause with the variable &WHERE and then use the CL code in Figure 1.3. In this program, if only one parameter is fed in, the program creates the WHERE clause only for that field. If multiple values are sent in, the program combines them within the WHERE clause using AND.
Finally, after building our entire WHERE clause, we run the query using the STRQMQRY command with the SETVAR parameter setting the variable WHERE to the value of the field &WHEREC. If no parameters are populated when the program is run, the program leaves a blank WHERE clause, which causes the QM query to display all records for the file.
A standard SQL statement takes the format:
SELECT CUSNAM FROM CUSTMAST WHERE CUSNO=1234
One of the concepts behind the SQL language is to be able to get access to a database through "plain English" queries. This makes it easy to recognize that, in the example given earlier, we want to select the customer name field from the customer master table for any records where the customer number is equal to 1234. Additional optional clauses may include the GROUP BY clause, which is used to define record grouping, and the ORDER BY clause, which is used to define record sorting. Although we are not going to spend a lot of time going over SQL functions, we are going to spend a little time examining the CASE function.
The CASE function allows you to define a field's value conditionally. For example, this powerful function gives you the ability to avoid "divide-by-zero" errors on calculated fields using the CASE function as shown here:
(CASE WHERE x = 0 THEN 0 ELSE y/x END)
This example returns 0 as the result if x is 0, otherwise the result will be y divided by x. The CASE operator is also extremely useful in a situation where, for example, you want to summarize sales data into columns for a specific period and year to date. Figure 1.4 shows the SQL code that would be required to do this:
Once again we use parameters to define the period and year for our query. Our selected period field is defined using both the SUM and CASE functions to only include data for the required period and then summarize the data by item number. Our year-to-date sales field again uses the SUM and CASE functions, however, this time we select anything up to and including the selected period. You'll also notice that we are using the &PERD and &YEAR variables in the WHERE clause, which prevents the unnecessary reading of records not required by the query.
This also prevents the query from displaying rows where both the current period and year-to-date values are zero. When we create a Query Manager report form to use along with this query, we can insert the values of the SLSPER and SLSYER values into the page headings. A sample of the page-heading screen for the Query Manager report form required to do this is shown in Figure 1.5. We have used &2 to refer to the period number, which is found in column two of our query, and &3 to refer to the year field found in column three. A sample of the resulting output can be seen in Figure 1.6.
You can also feed variables into your Query Manager query specifically to pass them into the Query Manager report form. This technique can be useful to add job-specific information, such as a company name or user or workstation ID, to the page headings on your report form. The &USERID field shown here is a sample of how a Query Manager query does this:
SELECT CUSNO, CUSNAM, CUSAD1, CUSAD2, CUSCTY, CUSSTE, CUSZIP,
&USERID AS USER FROM CUSTMAST;
Query Manager Report Forms
As explained earlier, if the Query Manager query is used to define what our report is — that is, what data will appear, the Query Manager report form defines the how. The incredible flexibility available with QM/400 is in large part thanks to the functionality available from report forms. When defining a QM report form, the starting point is usually setting up your column formatting. This also just happens to be the first option we are presented with on the Select Report Format display shown in Figure 1.7. This screen not only allows us to define the size and position of the columns of our report, but also allows us to define how the field is used in the report.
If you don't already have an active query — that is, if you have not been working with a query through the Query Manager query display, press F13 to specify a query to be used for the report. You can load the columns from the active query into this display by pressing F19. You can also add columns manually by pressing F6.
The USAGE column specifies how the column is to be used on the report. If usage is blank, the column is simply printed on the report. The OMIT usage keyword suppresses printing of this column. AVERAGE, SUM, COUNT, FIRST, LAST, MIN, and MAX options are all available and perform the functions suggested by their name. The BREAKX usage keyword, where x is a value from 1 to 6, allows you to define up to six levels of report breaks. The Edit Codes column is used in much the same way that edit codes are used in RPG or in Query/400. The exception here is that edit codes can also be specified for non-numeric fields to define how they are printed. A list of valid edit codes and their usage is shown in Table 1.2
The Edit Page Headings screen is used to set up the page headings for our report. If we were using the SELECT statement containing the &USERID parameter shown earlier as an example, we would then insert the value &8, to reference the eighth column of our query, into the page heading on our report form (see Figure 1.8).
(Continues...)
Excerpted from The iSeries and AS/400 Programmer's Guide to Cool Things by Mike Faust. Copyright © 2006 MC Press Online, LP. Excerpted by permission of MC Press.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
