Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

DB2

Posted Aug 6, 2007

DB2 9 and Microsoft Excel 2007 Part 3: Building Your Own Queries... - Page 2

By Paul Zikopoulos

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.



DB2 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.