You can use the Computed Column dialog box to add a computed column (expression) to your SELECT statement. The computed column can contain either of the following types of expressions:

•A built-in function supported by DB2

•An arithmetic operator

The procedures and examples in the following sections show how to use the Computed Column dialog box to build each type of expression.

Using Built-in Functions

DB/Assist provides all of the functions supported by DB2/2, as summarized in the following table. For a complete description of these functions, refer to the IBM's Structured Query Language (SQL) Reference documentation.

For example, the following SELECT statement uses the AVG function to retrieve the average salary for all employees in the Staff table:

 

SELECT AVG(STAFF.SALARY) FROM DB2ADMIN.STAFF STAFF

 

To build this statement, follow these steps:

1.Select SQL Statement Ø Tables or the Tables button. The SELECT/FROM Clause dialog box appears.

2.Select the Computed Column push button. The Computed Column dialog box appears, as shown below.

Computed Column Dialog Box

 

3.Select AVG in the Function list box.
The function you selected appears in the Computed Column Text field.

4.Select STAFF from the Table drop-down list. The columns in the table you select appear in the Column list box

5.Select SALARY in the Column list box.
The qualified column name is added to the Computed Column Text field.

6.Select the) symbol from the keypad to end the computed column syntax with a closed parenthesis. (If you prefer, you can type the closed parenthesis in the Computed Column Text field without using the keypad.)
The statement now appears in the Computed Column Text field as follows:
AVG(STAFF.SALARY)
If you make a mistake, select the Clear push button to delete the contents of the Computed Column Text field and repeat Steps 3 through 6.

7.Select the OK push button in the Computed Column dialog box.
The computed column appears in the Selected Columns list in the SELECT/FROM dialog box, as shown below.

Built-in Function as Computed Column

 

8. Select the OK push button in the SELECT/FROM Clause dialog box.
DB/Assist adds the SELECT statement to the SQL Editor window and to the SAM that contains this statement.

Using Arithmetic Operators

The keypad in the Computed Column dialog box provides arithmetic operators that you can use on a numeric column to build an expression.

The keypad also provides the following:

•Single quotation mark key

•DISTINCT key, which applies the DISTINCT keyword to the computed column text

For example, the following SELECT statement retrieves the ID, name, and job of each person in the Staff table, and uses the * (multiplication) operator on the Salary column to display each person's salary after a five percent increase:

SELECT STAFF.ID, STAFF.NAME, STAFF.JOB, STAFF.SALARY*1.05

FROM DB2ADMIN.STAFF STAFF

To build the computed column (shown in italics) in this statement, follow these steps:

1.Select the Computed Column push button in the SELECT/FROM Clause dialog box.
The Computed Column dialog box appears.

2.Select SALARY in the Column list box.
The qualified column name appears in the Computed Column Text field.

3.Select the * operator from the keypad. The * operator is added to the Computed Column Text field.

4.Type the number 1.05 after the * operator in the Computed Column text field to increase each entry in the Salary column by five percent.
The statement now appears in the Computed Column Text field as follows:
STAFF.SALARY*1.05
If you make a mistake, select the Clear push button to delete the contents of the Computed Column Text field and repeat Steps 2 through 4.

5.Select the OK push button in the Computed Column dialog box.
The computed column appears in the Selected Columns list in the SELECT/FROM Clause dialog box.

6.Select the OK push button in the SELECT/FROM Clause dialog box.
DB/Assist adds the SELECT statement to the SQL Editor window and to the SAM that contains this statement.

SQL syntax prohibits having both a distinct result set and a distinct computed column in the same SELECT statement.

Therefore, if you check the Distinct Result Set box in the SELECT/FROM Clause dialog box, and then select the Computed Column push button, the DISTINCT key in the Computed Column dialog box is disabled.