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 table’s 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 table’s MGRNO and ADMRDEPT columns, as well as the EMPLOYEE table’s
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:
Don’t forget that the EMPLOYEE table’s 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 it’s good to know it’s 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:
» 
Comment and Contribute