DB2 9 and Microsoft Excel 2007 Part 3: Building Your Own Queries... - Page 2
August 6, 2007
Refining and saving your query results
The results returned to your Excel 2007 worksheet are linked to the target database. If you want to refine this query, right-click anywhere in the result set and select Table > Edit Query:
If you click through this wizard, youll see that all of the settings you selected are preserved. For this example, click Next until you reach the Query Wizard Finish page, and save your query so that you can easily use it another time.
As you can see, its easy to define the data you want to return to your Excel 2007 worksheet without having to write a single line of SQL. What I showed you was Excels Query wizard, which sits on top of the Microsoft Query editor. While you can do a lot without knowing SQL, you cant do everything. For example, in our query you may have noticed that I included the DEPT column and restricted the results of the query output to only those employees that arent in department 20. Suppose you wanted to refine your query such that the DEPT column wasnt part of the result set, but you still wanted to restrict the results of your query to those employees that arent in department 20. Using the wizard, you wouldnt be able to do this since the Query Wizard Filter Data page only lets you apply to filters to data that is part of the result set.
To get around this problem, you can use the Query Editor that sits behind this Query wizard. This approach gives you a little more control over the query, but its also a little more complex. To invoke the Query Editor, you can click Cancel at any time as you step through the Query wizard, and you will be prompted to transfer whatever youve done in the wizard directly to the editor:
Note: You can also build your query and refine it using the Query Editor by selecting View data or edit query in Microsoft Query in the Query Wizard Finish page.
You can use the Query Editor to remove columns and filter columns that arent part of the result set (but are part of the table), and more. For example, you can see in the following figure that I used the Query Editor to return the same query, only this time department data wasnt part of the result set; however, only those employees that dont belong to department 20 were part of the result set:
When you are finished altering the query using the Query Editor, simply close it and the results of the query are returned to your Excel 2007 worksheet:
Wrapping it up...
In this article, I showed you how to retrieve a subset of a tables data without writing a single line of SQL. Whats more, I showed you how to work beyond the Query wizard to define a more complex query. Using these tools, you should be able to work beyond all the data in a table, and focus your analysis on the data you want. In my next article, Ill introduce you to the worlds most popular analysis artifact: the pivot table, and show you how to create and leverage pivot tables with live DB2 9 data in Excel 2007.
About the Author
Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than thirteen years of experience with DB2 and has written more than one hundred-fifty magazine articles and is currently working on book number twelve. Paul has authored the books Information on Demand: Introduction to DB2 9.5 New Features, DB2 9 Database Administration Certification Guide and Reference (6th Edition), DB2 9: New Features, Information on Demand: Introduction to DB2 9 New Features, Off to the Races with Apache Derby, DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, including running with his dog Chachi, avoiding punches in his MMA class, and trying to figure out the world according to Chloë his daughter. You can reach him at: firstname.lastname@example.org.
IBM and DB2 are registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Microsoft is a trademark of Microsoft Corporation in the United States, other countries, or both.
Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.
Other company, product, or service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2007. All rights reserved.
The opinions, solutions, and advice in this article are from the authors experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the authors knowledge at the time of writing.