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:
- 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.
- Create a simple type CASE expression.
- On the Expression Types page, click CASE - searched
or simple, and then click Next.
- On the CASE Options page, click Simple-WHEN-Clause,
and then click Next.
- Add the DAYOFWEEK function:
- On the Simple CASE Expression page, in the CASE list,
click Build function expression. The
Function Expression Builder wizard opens.
- In the Select a function category list,
click Date and time.
- In the Select a function list, click DAYOFWEEK.
- In the Select a function signature list,
click DAYOFWEEK(DATE) --> INTEGER. The
function signature shows that the function requires one argument.
- 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.
- Click Finish to return to constructing
the CASE expression.
- In the CASE list, select DAYOFWEEK(DATE).
- 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 stringsDAYOFWEEK value |
Resulting character string |
1 |
'Sunday' |
2 |
'Monday' |
3 |
'Tuesday' |
4 |
'Wednesday' |
5 |
'Thursday' |
6 |
'Friday' |
7 |
'Saturday' |
- 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.
- Double-click the empty cell in the Result Expression column
to the right of THEN and enter the string 'Sunday'.
- 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:

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