fhg
Volume 9, Number 21 -- June 24, 2009

V6R1 Run SQL Scripts: Print and Save Result Sets

Published: June 24, 2009

by Skip Marchesani

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


Sponsored By
HELP/SYSTEMS

                                                 SEQUEL
                                                 IBM® System i® Data Access Made Easy

                                              · Complete management access to critical data
                                              · Easy to use by IT and end users
                                              · Automated data access and display
                                              · Comprehensive BI package: reports, tables,
                                                 key performance indicators, and dashboards
                                              · System i-centric for real-time data analysis
                                              · Expert support and training

Click here for a FREE Information Kit!


Senior Technical Editor: Ted Holt
Technical Editor: Joe Hertvik
Contributing Technical Editors: Erwin Earley, Brian Kelly, Michael Sansoterra
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

Sponsored Links

New Generation Software:  FREE Query Migration Planning Webinar, June 25, 2 p.m. EST
Tri-Delta Resources:  Free 90 Night Disaster Recovery Service For Your IBM i
looksoftware:  Why Service-Enable? View the On-Demand Webinar


 

IT Jungle Store Top Book Picks

Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
The iSeries Express Web Implementer's Guide: List Price, $49.95
The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
The System i Pocket RPG & RPG IV Guide: List Price, $69.95
The iSeries Pocket Database Guide: List Price, $59.00
The iSeries Pocket SQL Guide: List Price, $59.00
The iSeries Pocket Query Guide: List Price, $49.00
The iSeries Pocket WebFacing Primer: List Price, $39.00
Migrating to WebSphere Express for iSeries: List Price, $49.00
Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
Getting Started with WebSphere Express for iSeries: List Price, $49.00
Can the AS/400 Survive IBM?: List Price, $49.00
Chip Wars: List Price, $29.95


 
The Four Hundred
AS/400:Still Kicking After 21 Years

Power Systems i Weather Report: Partly Cloudy Soon

IBM i Manifest Takes Root in Japan

Help Wanted: AS/400 Advocate

COMMON Europe: Doing the Math on Top i Concerns

Four Hundred Stuff
IBM to Offer Free EGL Tool for Web 2.0

Infor Spurs Customer Upgrades with 'Flex' Incentives

Application Maintenance Becomes a Service at vLegaci

Companies Put PHP to Work With BCD's WebSmart

ASCI Adds i OS Support to Job Scheduler

Four Hundred Monitor
Four Hundred Monitor's
Full iSeries Events Calendar

System i PTF Guide
June 20, 2009: Volume 11, Number 25

June 13, 2009: Volume 11, Number 24

June 6, 2009: Volume 11, Number 23

May 30, 2009: Volume 11, Number 22

May 23, 2009: Volume 11, Number 21

May 16, 2009: Volume 11, Number 20

TPM at The Register
VMLogix plugs virt jukebox into Amazon cloud

Platform leaps from grids to clouds

Mellanox cranks up InfiniBand switches

Wall Street hammers for sale sign in Novell lawn

iSuppli: Moore's Law to take a breather

Sun kicks out VirtualBox 3.0 beta

Canucks buy 300 teraflops Blue iDataPlex super

EnterpriseDB revs Postgres database

Dell grows Intel 'Nehalem EP' iron

Canonical certs Ubuntu on ProLiants

IDC: Server market to decline through 2010

Red Hat's standalone hypervisor goes beta

Citrix ships XenServer 5.5

Wind River punts homegrown hypervisor

THIS ISSUE SPONSORED BY:

Help/Systems
Profound Logic Software
CNX


Printer Friendly Version


TABLE OF CONTENTS
V6R1 Run SQL Scripts: Print and Save Result Sets

No More Global Variables!

Reader Input: /QOpenSys Redux, PC5250 Popup Keypads, and Even Farther Beyond Replication

Reader Feedback: Advanced DB2 for i Data Access Techniques with .NET

Four Hundred Guru

BACK ISSUES

From the IT Jungle Forums
PHP CLI Call

Perl, PHP, and/or ZendCore

batch printing PDF files from RPG program

Using db2_connect in PHP on iSeries

How to return value from CL program?

ADO.NET/IBM.Data.DB2.iSeries/ iDB2Connection

Order by alias names




 
Subscription Information:
You can unsubscribe, change your email address, or sign up for any of IT Jungle's free e-newsletters through our Web site at http://www.itjungle.com/sub/subscribe.html.

Copyright © 1996-2009 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement