Rational Developer for System z


< Previous | Next >

Specifying the result columns

In this exercise, you will specify the columns to show in the query result. A store employee needs to see the customer name, the title of each video that the customer has rented, and the day of the week that each video is due. To meet these requirements, this tutorial will guide you through including a CASE expression and assigning it an alias for the third result column.

Selecting columns for the result column set

You will add the following columns to the result column set for the customer name and the video title:
  • The NAME column in the CUSTOMERS table
  • The TITLE column in the VIDEOS table

There are two different ways to select the columns for the result set:

  1. In the Tables pane, select the NAME check box in the C (CUSTOMER) table. This adds the NAME column to the result.
  2. Alternatively, select the Columns tab from theDesign pane, double-click the first empty cell in the Column column, and select V.TITLE from the dropdown list. This column is also added to the SQL source code in the SQL Source pane.

Adding a CASE expression to the result column set

The third column for the query result set will be the result of a column expression. The VIDEOS database stores the date that the video is due in DATE format. The DATE format needs to be translated into a character string that contains the name of the day of the week that the video is due.
The database function you will create will retrieve an integer from the date that corresponds to the day of the week. It will then use a CASE expression to evaluate the function and convert the integers into the corresponding character strings.
You will perform the following activities to build the CASE expression:
  • Open the Expression Builder wizard.
  • Create a simple type CASE expression.
  • Add the DAYOFWEEK function for evaluation by the CASE expression. This function lets you get the day of the week from the DATE column and returns an integer that corresponds to the day of the week.
  • Add WHEN clauses to produce the results for the CASE expression.

To create the CASE expression:

  1. On the Columns tab of the Design pane, double-click on the first empty cell of the Column column, scroll down and select Build Expression at the end of the dropdown list. The Expression Builder wizard opens.
  2. Create a simple type CASE expression.
    1. On the Expression Types page, click CASE - searched or simple, and then click Next.
    2. On the CASE Options page, click Simple-WHEN-Clause, and then click Next.
  3. Add the DAYOFWEEK function:
    1. On the Simple CASE Expression page, select Build function expression from the CASE dropdown menu. The Function Expression Builder wizard opens.
    2. For Select a function category, select Date and time from the dropdown menu.
    3. For Select a function, select DAYOFWEEK.
    4. For Select a function signature , selectDAYOFWEEK(DATE) --> INTEGER. The function signature shows that the function requires one argument.
    5. In the Value column of the argument table, double-click the first cell, and select R.DATE from the dropdown menu.
      Note: The R table qualifier for the DATE column you selected is not displayed in this wizard. It will be displayed in the expression when you return to the SQL Query Builder.
    6. Click Finish to return to the Expression Builder window and continue constructing the CASE expression.
    7. Back in the Expression Builder Wizard, for CASE, select DAYOFWEEK(DATE) from the dropdown menu.
  4. Add seven WHEN clauses to the CASE expression (one for each day of the week) to translate the integer returned from the DAYOFWEEK function into character strings. The following table shows the character string that is needed for each value returned from the DAYOFWEEK function:
    Table 1. DAYOFWEEK values and resulting character strings
    DAYOFWEEK value Resulting character string
    1 'Sunday'
    2 'Monday'
    3 'Tuesday'
    4 'Wednesday'
    5 'Thursday'
    6 'Friday'
    7 'Saturday'
    1. While still in the Expression Builder window, double-click the empty cell in the Expression column between the WHEN and THEN columns, and enter 1 into the cell.
      Note: When you enter data in this table and other tables that are associated with the SQL Query Builder, for the entry to take effect, after you type a value in a cell, you must press Enter or change the focus to another cell.
    2. Double-click the empty cell in the Result Expression column to the right of THEN, and type the string 'Sunday'.
    3. For each of the remaining six days of the week, click Add WHEN Clause below the table, and repeat steps 4a-b, so that your table contains all days of the week listed.
    4. Click Finish to close the Expression Builder wizard.
The completed CASE expression is shown in the list of column expressions on the Columns tab in the Design pane, and is also shown in the SQL Source pane.

Adding a column alias

In the customerRentals SQL statement, you will add a column alias for the CASE column expression. You will use the Columns tab in the Design pane of the SQL Query Builder.

To add a column alias:

  1. Select the Columns tab in the Design pane.
  2. Double click the cell in the Alias column next to the CASE column expression and then enter DUEDAY. In the SQL Source pane, the column alias AS DUEDAY is shown after the CASE expression. When you run the query, this alias appears as the title for the column in the result table.
< Previous | Next >

Terms of use | Feedback



This information center is powered by Eclipse technology. (http://www.eclipse.org)