| One of my all time favorite tools for creating | | | | second fields listed will be from the secondary |
| reports, data lookup inquiry screens or custom file | | | | files. |
| downloads for the AS/400 and iSeries platforms | | | | Now that you have the files joined together and |
| is IBMs very own Query/400. This is amazing tool | | | | the matching fields defined it is time to create the |
| is an additional licensed program but I find the | | | | constraints by taking the option for select |
| cost worth it since it is a user friendly menu | | | | records. Once on the select records screen simply |
| driven application and it makes creating custom | | | | enter in the field you want to build the constraint |
| reports a snap. | | | | on along with the test condition such as equal, not |
| The first step is to actually create a Query/400 | | | | equal, greater than, data range and so on and the |
| query definition by issuing the command | | | | value. The value can be a constant, field name, |
| WRKQRY. And the first option is to specify the | | | | number or more. |
| file selections you will use for the query. This is | | | | One feature I absolutely love about Query/400 is |
| where you tell query which file or files you will be | | | | that if you are using multiple files it will |
| using to create your report, so you will specify | | | | automatically prefix all of the field names T01, |
| each file, library and member if the file has | | | | T02 and so on. This makes it very easy to find |
| multiple members. If you need to add another file | | | | the file and field name combination you wish to |
| simply press the F9 key. | | | | use and it completely eliminates problems with |
| Once you have specified the files it is time to let | | | | duplicate field names between files. |
| query know how it is going to join them together, | | | | Now that you have defined the query constraints |
| if you have any experience with SQL then this | | | | it's time to actually run the report and see some |
| step should come naturally but if you aren't | | | | data results, this can be done any time you are in |
| familiar with SQL let me explain. The join | | | | query by using the F5 key. A word of caution |
| operation tells Query/400 which fields match | | | | though that if you are doing joins with multiple |
| between the selected files so it can query them | | | | files the system may need to build a temporary |
| together at the same time and present the | | | | index and this can take awhile depending on the |
| resulting data set as if it is one large logical file. On | | | | data files in use and other factors used by the |
| the join field you will typically select either | | | | AS/400s query optimizer engine. |
| matched records or matched records with | | | | The last step I usually take after running the |
| primary file. In all my years of writing Query/400 | | | | query a few times using F5 and making sure the |
| queries I have not once needed to use the | | | | report looks corect on the screen is to take the |
| unmatched records option. | | | | option to select output type and output form. |
| After choosing the matching type it is time to tell | | | | Depending on the desired output of the query I |
| query what fields you are going to match by. | | | | will either select a printable report or display |
| Simply type in the first field with the test | | | | output type. If you choose printer there will be |
| condition followed by the second matching field. If | | | | some additional options to configure like the |
| you are using multiple files and matching records | | | | addition or removal of a header page, which I |
| with the primary file the first set of match fields | | | | typically remove to save the wasted sheet of |
| will always be from the primary file and the | | | | paper. |