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.
Questions and Answers
In 1988, IBM introduced the AS400® as a server solution for midsize businesses and departments within larger enterprises. Over time, IBM realized that in order to fulfill their vision of replacing PC and Unix servers throughout organizations worldwide, the AS400 would need to be easily integrated with distributed networks and...
Is there still a place for the IBM AS400 in the modern IT landscape? How can this ageing system fit in with a business's complex variety of platforms and technologies?
How does RPG AS400 address the internet? Has it moved on from the green screen? Is it really going anywhere or has it anywhere left to go? Well they said it was dead twenty years ago but it's still going strong and I guess as long as there's life in the Series i or whatever that morphs into then there will be life in RPG.
There are several and well known benefits of modernizing iSeries applications. This article provides an overview of some of the benefits and discusses some of the drivers behind iSeries modernization.
Whenever I start working on an AS/400, iSeries or Power System box at a new company there are a few key areas I like to be sure of. These areas include ensuring basic system level security, job configuration and process management. Let's take a fast look at how you can...
The AS/400 development and testing services are the by products of IBM power systems. These applications are mostly targeted at increasing the efficiency of distributed application development teams. This application features a flexible installation process designed to provide developers with only the functions they need.
An in depth look at the new MMORPG from Blizzard Entertainment known as Diablo III, a long awaited release for Diablo fans.
Most small businesses take advantage of computer technology, and it certainly can make things a lot easier for everyone within the business when you do so properly. Unfortunately, there are also a number of different problems that can occur as a result of the technology that you are using.
The remote desktop software is quite an amazing type of software with plenty of features that can come in handy at work or even at home. The feature lets you obtain remote access to an assortment of other computers, directly from the computer that you are using at the moment. This software is often used by people who work in an office or business setting.
Remote desktop software is a type of software that allows users to get access to a remote computer. Basically, if you have this software, you will have the ability to view the remote computer and also control it with the mouse you are using. A number of these software programs will also enable you to send documents to and from one computer to another.
The second selection of pc ink cartridges is the compatible low-priced cartridges.
When it comes to reporting tools for the AS/400, iSeries and IBM i platform there are many products to pick from. In fact a whole cottage industry has sprang up over the years with a myriad of different data tools for reviewing and extracting data from the OS/400 integrated DB2...
One of the more commonly asked questions I see in forums and by end users is how do you get your data from the AS400 down to the PC in a text file format or as an Excel based spreadsheet. And fortunately with modern tools and software this is a...
Whenever I start working on an AS/400, iSeries or Power System box at a new company there are a few key areas I like to be sure of. These areas include ensuring basic system level security, job configuration and process management. Let's take a fast look at how you can...
One of the nice features of the AS/400 and iSeries platform is it's reliability and administrative requirements. But even in light of this as a system administrator you should also be looking and monitoring key data elements to create a baseline of performance metrics. This data can then be used...
Having worked on and administered IBMs midrange AS/400, iSeries and System i platforms for the better part of ten years I have found that you can boil down the essential administrative functions to a few select commands available from one primary command. Let me explain. One of the major functions of...
Discuss this Article
Comments on this article