ComputerTechnologyBlog.com

AS/400 Queries Are Easy As Pie

One of my all time favorite tools for creating reports, data lookup inquiry screens or custom file downloads for the AS/400 and iSeries platforms is IBMs very own Query/400. This is amazing tool is an additional licensed program but I find the cost worth it since it is a user friendly menu driven application and it makes creating custom reports a snap.

The first step is to actually create a Query/400 query definition by issuing the command WRKQRY. And the first option is to specify the file selections you will use for the query. This is where you tell query which file or files you will be using to create your report, so you will specify each file, library and member if the file has multiple members. If you need to add another file simply press the F9 key.

Once you have specified the files it is time to let query know how it is going to join them together, if you have any experience with SQL then this step should come naturally but if you aren’t familiar with SQL let me explain. The join operation tells Query/400 which fields match between the selected files so it can query them together at the same time and present the resulting data set as if it is one large logical file. On the join field you will typically select either matched records or matched records with primary file. In all my years of writing Query/400 queries I have not once needed to use the unmatched records option.

After choosing the matching type it is time to tell query what fields you are going to match by. Simply type in the first field with the test condition followed by the second matching field. If you are using multiple files and matching records with the primary file the first set of match fields will always be from the primary file and the second fields listed will be from the secondary files.

Now that you have the files joined together and the matching fields defined it is time to create the constraints by taking the option for select records. Once on the select records screen simply enter in the field you want to build the constraint on along with the test condition such as equal, not equal, greater than, data range and so on and the value. The value can be a constant, field name, number or more.

One feature I absolutely love about Query/400 is that if you are using multiple files it will automatically prefix all of the field names T01, T02 and so on. This makes it very easy to find the file and field name combination you wish to use and it completely eliminates problems with duplicate field names between files.

Now that you have defined the query constraints it’s time to actually run the report and see some data results, this can be done any time you are in query by using the F5 key. A word of caution though that if you are doing joins with multiple files the system may need to build a temporary index and this can take awhile depending on the data files in use and other factors used by the AS/400s query optimizer engine.

The last step I usually take after running the query a few times using F5 and making sure the report looks corect on the screen is to take the option to select output type and output form. Depending on the desired output of the query I will either select a printable report or display output type. If you choose printer there will be some additional options to configure like the addition or removal of a header page, which I typically remove to save the wasted sheet of paper.

John Andersen is the marketing manager and editor for the AS400 Tutorials website. To get your free AS400 and iSeries courses check out AS400 Tutorials.Article Source:http://www.articlesbase.com/computers-articles/as400-queries-are-easy-as-pie-1156192.html