V6R1 Run SQL Scripts: Print and Save Result Sets

The ability to print a Run SQL Scripts result set and/or save it in Excel spreadsheet format has been on my wish list and that of others for a long time. V6R1 has fulfilled these wishes and by doing so has made Run SQL Scripts more functional and easier to use.

Print capability is now a standard function when a result set is displayed. However the Run SQL Scripts option to “Allow Save Results” must be selected to be able to save a result set in Excel or other file format. To select this option, click on Options in the toolbar and then select “Allow Save Results” in the dropdown menu, as shown in Figure 1.

Figure 1.

Note that that I’ve made selections on the Options dropdown menu to allow quick and easy execution of ad hoc SQL queries. It is not necessary to have the identical options selected. To print or save result sets, the option “Display Results in Separate Window” is not required, however “Allow Save Results” must be selected if you wish to save your result sets in Excel or another file format.

To understand how to print or save a result set, use “Run SQL Scripts” to execute an SQL SELECT statement that will produce a result set and right-click on the result set. The Save Results–Copy–Print menu will appear as shown in Figure 2.

Figure 2.

Next click on Print and the Windows print dialogue will appear, as shown in Figure 3, which allows selection of a printer and printer options.

Figure 3.

To copy all or selected data from a result set to the clipboard, highlight the data, right-click on the result set, and select “Copy” from the Save Results–Copy–Print menu. The data will be copied to the clipboard, where it can then be pasted to the desired document or location.

To save the result set in an Excel or other file format, right-click on the result set, and then click on “Save Results” in the Save Results–Copy–Print menu, as shown in Figure 4.

Figure 4.

The Save Results dialogue will appear, and it allows the attributes (file name and location, and character set) and format of the result set that is being saved to be determined, as shown in Figure 5.

If saving to a spreadsheet or other file type format where column headings are desirable, select “Include column headings.” If the result set should be displayed in the saved format immediately after saving it, select “Display the output using.” In this case, since Microsoft Excel 4.0 Worksheet was selected as the file type, Excel would open the saved file displaying the result set as an Excel spreadsheet.

Figure 5.

Note that the result set can be saved in six different formats: comma separated; text or tab delimited; Lotus 1-2-3; Microsoft Excel XML; Microsoft Excel 4.0; and Microsoft Excel 3.0. See Figure 6.

Figure 6.

When all the file options for the Save Results dialogue have been selected, click OK on the lower right side, and a message will come up stating that the result set information was successfully saved, as shown in Figure 7. If a file with the same name already exists, a message will appear asking if the existing file should be overlaid with the new file.

Figure 7.

Click OK on the “Information successfully saved” message, and the result set saved (in this case) in .xls format will be displayed in Excel, as shown in Figure 8.

Figure 8.

Skip Marchesani retired from IBM after 30 years and is now a consultant with Custom Systems Corporation. He is also a founding partner of System i Developer and the RPG & DB2 Summit. Skip spent much of his IBM career working with the Rochester Development Lab on projects for S/38 and AS/400 and was involved with the development of the AS/400. He was part of the team that taught early AS/400 education to customers and IBM lab sites worldwide. Skip is recognized as an industry expert on DB2 for i and the author of the book DB2/400: The New AS/400 Database. He specializes in providing customized education for any area of the System i, iSeries, and AS/400; does database design and design reviews; and performs general System i, iSeries, and AS/400 consulting for interested clients. He has been a speaker for user groups, technical conferences, and System i, iSeries, and AS/400 audiences around the world. He is an award-winning COMMON speaker and has received its Distinguished Service Award. Send your questions or comments for Skip to Ted Holt via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot