Specifying the result columns

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.

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).

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:

      

      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:

      

   4. Click Finish to close the Expression Builder wizard.

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.

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.