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, you’ll 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, it’s 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 Excel’s Query wizard, which sits on top of the Microsoft
Query editor. While you can do a lot without knowing SQL, you can’t 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 aren’t in department 20. Suppose you wanted to refine your query
such that the DEPT column wasn’t part of the result set, but you still wanted
to restrict the results of your query to those employees that aren’t in
department 20. Using the wizard, you wouldn’t 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 it’s 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 you’ve 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 aren’t 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 wasn’t part of
the result set; however, only those employees that don’t 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
table’s data without writing a single line of SQL. What’s 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, I’ll introduce
you to the world’s 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.
»
See All Articles by Columnist Paul C. Zikopoulos
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: paulz_ibm@msn.com.
Trademarks
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.
Disclaimer
The opinions, solutions, and advice in this article are from
the author’s 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 author’s knowledge
at the time of writing.
» 
Comment and Contribute
