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:
- 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.
- 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,
select Build function expression from the CASE dropdown
menu. The Function Expression Builder wizard
opens.
- For Select a function category,
select Date and time from the dropdown menu.
- For Select a function, select DAYOFWEEK.
- For Select a function signature ,
selectDAYOFWEEK(DATE) --> INTEGER. The function signature shows that the function requires
one argument.
- 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.
- Click Finish to return to the Expression
Builder window and continue constructing the CASE expression.
- Back in the Expression Builder Wizard,
for CASE, select DAYOFWEEK(DATE) from
the dropdown menu.
- 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' |
- 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.
- Double-click the empty cell in the Result
Expression column to the right of THEN,
and type the string 'Sunday'.
- 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.
- 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.