< Previous | Next >

Specifying the result columns

In this exercise, you 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. You include two of the columns and add a CASE expression for the third result column, and then assign an alias to the CASE column expression.

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

To select columns for the result column set:

  1. In the Tables pane, select the NAME check box in the C (CUSTOMER) table.
  2. On the Columns page in the Design pane, double-click the first empty cell in the Column column, click V.TITLE in the list, and then press Enter. The columns are 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 video store 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 that the video is due. A database function will get an integer from the date that corresponds to the day of the week. You will use a CASE expression to evaluate the function and convert the integers that are returned from the function into character strings for each day of the week (for example, 1 will be changed to "Sunday" in the query result).

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. The DAYOFWEEK function lets you get the day of the week from the DATE column. This function returns an integer that corresponds to the day of the week.
  • Add WHEN clauses to produce the results for the CASE expression.

To add a CASE expression:

  1. On the Columns page in the Design pane, double-click the third cell in the Column column (the first empty cell), click Build Expression at the end of the list, and then press Enter. 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, in the CASE list, click Build function expression. The Function Expression Builder wizard opens.
    2. In the Select a function category list, click Date and time.
    3. In the Select a function list, click DAYOFWEEK.
    4. In the Select a function signature list, click DAYOFWEEK(DATE) --> INTEGER. The function signature shows that the function requires one argument.
    5. In the Value column of the argument table, double-click the cell, click R.DATE in the list, and then press Enter. The syntax of the function expression is DAYOFWEEK(DATE), as shown in section 5 of the following image:

      Function Expression Builder wizard

      Note that the R table qualifier for the DATE column that you selected is not displayed in this wizard. It will be displayed in the expression when you return to the SQL builder.
    6. Click Finish to return to constructing the CASE expression.
    7. In the CASE list, select DAYOFWEEK(DATE).
  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. In the table, double-click the empty cell in the Expression column to the right of WHEN, and then enter the integer 1. When you enter data in this table and other tables that are associated with the SQL 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 enter the string 'Sunday'.
    3. For each of the remaining six days of the week, click Add WHEN Clause, and then repeat steps 4a and 4b, so that your table looks similar to the image shown here:

      Completed WHEN clause table

    4. Click Finish to close the Expression Builder wizard.
The completed CASE expression is shown in the list of column expressions on the Columns page in the Design pane, and is also shown in the SQL statement source.

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 page in the Design pane of the SQL builder.

To add a column alias:

  1. Click the Columns tab in the Design pane.
  2. 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.
The Columns page now looks like the following image:

Design pane - completed Columns page

< Previous | Next >