Rational Developer for System z


< Previous | Next >

Adding joins, a query condition, and a GROUP BY clause

Since our query focuses on returning the due day of rented videos for a particular customer, the query needs to be modified to restrict results to customers who are currently renting videos and to videos that are currently being rented. Results may be further restricted to a specific customer whose name you specify when the query is run. Finally, for readability, you will modify the query results by the day of the week the video is due, then by video title, and then by customer last name.

Joining tables

A join operation lets you retrieve data from two or more tables based on matching column values. The data in the tables is linked into a single result. Two joins are needed for this query. The query result needs to include the RENTALS and CUSTOMERS table entries that have matching CUST_ID column values. The query result must also include the RENTALS and VIDEOS table entries that have matching video IDs (VID_ID column values).

To join tables:

  1. In the Tables pane, right-click the header of the R table, and then click Create Join from the context menu.
  2. In the Create Join window under Source, make the following selections:
    1. In the Table (alias) list, select RENTALS (R).
    2. In the Column list, select CUST_ID.
  3. Under Target, make the following selections:
    1. In the Table (alias) list, select CUSTOMERS (C).
    2. In the Column list, select CUST_ID.
  4. Click OK. A join connector appears between the two columns.
  5. You can also create joins tagging the VID_ID attribute in the R (RENTALS) table in the Tables pane and dragging your pointer to the VID_ID column table to the column in the V (VIDEOS) table. Again, the join connector appears between the two columns.
You should see the SQL source change to include the join in the SQL Source pane:
    FROM
       XMLTEST.CUSTOMERS AS C JOIN XMLTEST.RENTALS AS R ON C.CUST_ID = R.CUST_ID 
       JOIN XMLTEST.VIDEOS AS V ON R.VID_ID = V.VID_ID

You can change the join type (for example, from the default inner join to a left, right, or full outer join) in the Tables pane by right-clicking the connector, clicking Specify Join Type from the context menu, and selecting the join type that you want in the Specify Join window. For the purposes of this tutorial, keep the defaults.

Creating a query condition

Next, the query needs a query condition so that the query extracts only result rows that have the customer name that you want. You add conditions to the query by using the Conditions tab in the Design pane.

To create a query condition:

  1. Click the Conditions tab to see the Conditions page.
  2. In the first row, double-click the cell in the Column column, and select C.NAME from the dropdown list.
  3. In the same row, double-click the cell in the Operator column, and select the = operator.
  4. In that row, double-click the cell in the Value column, and type :CUSTNAME. A colon followed by a variable name is the SQL syntax for a variable that will be substituted with a value when you run the query. You will see how this works when you run the SQL query.

Adding a GROUP BY clause

You will group the query by the day of the week, then by title, and then by customer name.
To create a GROUP BY clause in the SQL Query Builder, use the Groups page in the Design pane. In this view, you can also create more advanced groupings in your query result by using column expressions, nested groups, grouping sets (in DB2 only), and the ROLLUP and CUBE grouping functions (in Oracle and DB2).

To add a GROUP BY clause:

  1. In the Design pane, click the Groups tab.
  2. In the right hand pane, enter theDAYOFWEEK function as a result column.
    1. In the Column table, double-click the first row, click Build Expression in the list. The Expression Builder wizard will open.
    2. On the Expression Types page of the wizard, click Function, and then click Next. The Function Expression Builder page opens.
    3. For Select a function , select Date and time from the drop down menu.
    4. For Select a function , select DAYOFWEEK from the drop down menu.
    5. For Select a function signature , select DAYOFWEEK(DATE) --> INTEGER from the drop down menu. This function signature shows that the function requires one argument.
    6. In the Value column of the argument table, double click on cell with the null value, and select R.DATE from the drop down menu.
    7. Click Finish. The DAYOFWEEK function is shown in the first cell of the Column table.
  3. Back in the Groups tab, double click on the next empty cell of the Column table, and select the V.TITLE column from the drop down menu.
  4. Double click on the next empty cell of the Column table, and select C.NAME from the drop down menu.
The query is now complete.
< Previous | Next >

Feedback