Rational Data Architect and DB2 9: Building an SQL Statement - Page 2
May 8, 2007
Rational DA supports drag-and-drop operations from the Database Explorer view to the SQL builder. Simply right-click and drag the table you want to add to the Tables pane, and release the mouse button to move the table to this region.
This drag-and-drop support replaces the Add Table option outlined earlier in this step; this table selection process provides a more natural query-building experience:
4. Right-click in the Tables pane and select the Create Join option to create the join illustrated by the following figure:
For this example, join the DEPARTMENT and EMPLOYEE tables using the DEPARTMENT.DEPTNO and EMPLOYEE.WORKDEPT columns, as shown in the previous figure.
If you select two join columns that are not compatible, the SQL builder 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 pane by hovering over the desired join column on one table and releasing the mouse button on the join column on the target table:
You can see in the previous figure that when you hover over a column, the underlying data type is revealed in its 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 DA 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. Ensure that your SQL builder looks similar to this by selecting the following columns: DEPARTMENT.DEPTNAME, DEPARTMENT.LOCATION, EMPLOYEE.EMPNO, EMPLOYEE.FIRSTNME, EMPLOYEE.LASTNAME, and EMPLOYEE.PHONENO.
Notice how the SQL statement that you are building in the SQL Source pane dynamically changes as you perform different operations such as adding or removing tables, projecting or restricting columns, identifying join predicates, and so on.
Also, note that Rational DA gives you visual cues as to business relationships encoded within the schema as they relate to each tables columns. For example, in the previous figure, you can see that the DEPTNO and EMPNO columns are primary keys as indicated by their accompanying icon:. The DEPARTMENT tables MGRNO and ADMRDEPT columns, as well as the EMPLOYEE tables WORKDEPT column, are foreign keys linked to other tables, as indicated by their accompanying icon: .
You can also use the Columns tab at the bottom of the SQL builder to add columns to your SQL statement simply click the Column list to select the column or columns that you want to add.
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. For this example, ensure that your Columns tab looks similar to this:
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 (as indicated by the Sort Order column) by FIRSTNME (in the same descending order, as indicated in the Sort Type column).
7. Apply a conditional predicate to your SQL statement (for example, a WHERE predicate) by selecting the Conditions tab and building the condition by clicking each respective column:
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: Dont forget that the EMPLOYEE tables SEX column is a CHARACTER-based column; therefore, the value you specify in the Value column is case-sensitive and must be delimited with single quotes; that why I used F in the previous figure.
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 its good to know its 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. Test the query you just built by clicking Run->Run SQL or clicking the corresponding icon, as shown below:
The output of this action and the associated result set are shown in the Data Output tab at the bottom of the Rational DA workspace: