DB2 Universal Database:Generating SQL Statements Using the SQL Builder, Part 5 - Page 2
May 30, 2006
If you select two join columns that are not compatible, the SQL Builder tells you this and prevents the join because it would result in an error at run time. An example of such an error message is shown below:
You can also use the drag-and-drop method to create a join directly from the Tables window by hovering over the desired join column on one table and releasing the mouse button on the join column on the target table, as shown below:
You can see in the previous figure that when you hover over a column, its underlying data type is revealed in the hover help this enriches the design time experience and minimizes potential join errors. If you try to create a join between two data types that are incompatible, Rational AD changes the mouse icon to an error indicator and stops the operation.
5. Click the check boxes that correspond to the columns that you want to include in the result set for this SQL statement from both tables, as shown below:
For this example, select the following columns:
Notice how the SQL statement that you are building in the SQL Source pane continues to evolve as you perform different operations such as adding or removing tables, projecting or restricting columns, identifying join predicates, and so on.
You can also use the Column tab at the bottom of the SQL Builder to add columns to your SQL statement simply click the empty Column column in the Columns tab and select the column that you want to add, as shown below. (Repeat this process for all the columns that you want to add to your SQL statement.)
6. Using the Columns tab, apply any additional sorting order using the Sort Type and Sort Order columns by clicking them and selecting the corresponding options, as shown below:
In the previous figure, you can see that the output of this SQL statement will be sorted first by LASTNAME in descending order from Z>A (a personal bias of mine), and second by FIRSTNAME (in the same descending order, as indicated in the Sort Type column). The sort order gives a hierarchal order to the different sorting definitions for your SQL statement.
7. Apply a condition for your SQL statement (a.k.a. a WHERE predicate) by selecting the Conditions tab and building the condition by clicking each respective column, as shown below:
For this example, use the condition shown in the previous figure. To enter a value in the Value field, simply click the field and type the value instead of building the expression.
Note: Don't forget that for the EMPLOYEE table, the SEX column is a CHAR-based column; therefore, the value you specify in the Value column is case-sensitive and must be delimited with single quotes.
8. Apply any grouping options that you want to add to your SQL statement using the Groups and Group Conditions tabs at the bottom of the SQL Builder.
For this example, you can skip this step, but it is good to know it is there if the SQL statement you are building requires it.
9. Press Ctrl+S to save your query. You query should now look similar to this:
10. To run the query, select it from the Statements folder, right-click it, and select Execute. The output of this action and the associated result set are shown in the bottom of the Rational AD IDE, as shown below:
Once you have your SQL statement, you can right-click to see a number of other actions that you can perform on it (aside from just running it to see if it returns the results you are expecting).
For example, with the click of a button you can use the SQL statement to automatically generate a Java- or SQL-based stored procedure, as shown below:
At this point, you can use all the features provided in Rational AD to work with stored procedures. For example, the following figure shows how to test the stored procedure:
You can also use Rational AD to automatically generate a Java bean from your SQL statement in order to present Java developers with the appropriate methods and a more natural programming experience when interacting with your database.
The following figure shows the results of selecting the Generate Java Bean option from the pop-up menu that appears when you right-click an SQL statement and change to the J2EE perspective:
Using a Java bean, you can automatically create a Web service with mere clicks of a button, further extending the rapid application development features that the Rational AD IDE provides developers.
Wrapping it all up
In this article, I showed you how to use the built-in SQL Builder in Rational AD to build SQL statements that can be used as stand-alone statements in your development projects, converted into SQLJ artifacts, used in your routines, and more.
So far in this series, you have learned how to work with database connections, create database schema objects and stored procedures, how to generate UDFs, and how to use the SQL Builder.
In Part 6 of this series, I will show you some really cool features that are part of the SQL Builder such as formatting, syntax colorization, content assist, content tips, and a whole lot more! Having a sound understanding of these capabilities can really boost a developer's productivity.
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 ten years of experience with DB2 UDB and has written over 100 magazine articles and several books about it. Paul has co-authored the books: 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, running with his dog Chachi, and trying to figure out the world according to Chloë his new daughter. You can reach him at: firstname.lastname@example.org.
IBM, DB2, DB2 Universal Database, Rational, and WebSphere are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Java is a trademark of Sun Microsystems, Inc. in the United States, other countries, or both.
Windows is a trademark of Microsoft Corporation in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Linux is a trademark of Linus Torvalds in the United States, other countries, or both.
Other company, product, and service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2006. All rights reserved.
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.