After setting the constraints, the user selects which fields will be displayed in the query results. The next screen, shown in Figure 4.19, adds a tremendous amount of flexibility to the front end, allowing end users to create an almost infinite variety of custom reports. The screen determines the objects of the SQL SELECT and ORDER BY statements.

Figure 4.18  Choosing the display values and sort order.

By interrogating the values from the screen, the appropriate mainframe Job Control Language (JCL) and SQL are created and shipped to the internal reader of the IBM mainframe. Users are notified that JOB xxx HAS BEEN SUBMITTED, and they are notified of the completion by using the NOTIFY JCL statement. Users are then free to browse or print the results of their SQL statement. As is true with most decision support systems, the answer to one query often stimulates additional questions. Users can swap screens, thereby viewing output on one screen while creating a new query on another.

REXX routines are very simple to generate, and an understanding of the structure of SQL allows for very quick development of an SQL generator that can display any of the data items in the desired form or sequence. The screens are generated very quickly with SDF in ISPF, and, as, mentioned, the REXX routines are very straightforward.

It is not enough to have SQL used only as an access tool. End users demand simple, check-the-box access to their data, and they are often unwilling to use ad hoc SQL tools such as QBE. By creating this simple front end for SQL, the best possible outcome for the IS project is achieved. Listing 4.2 shows the REXX code used to generate the SQL. It is in three sections: the SELECT portion, the FROM portion, and the WHERE clause. Note that this system has two levels of aggregate details, and the SQL generator points to either detail or summary tables depending on the type of end user query.