SQL warehousing — Example–Defining a select into operator

In this example you will use a select into operator to retrieve an average value of a column in the CUST_CUSTOMER table, and then assign that value to a variable in a control flow.

Before you begin

Ensure that you have an active connection to the GSDB database in the Data Source Explorer view.
Create a variable group GRP in the Design Studio. Add a variable AVG_CUSTOMERAGE of type integer in this group.

About this example

This example uses the select into operator to find the average value of the CUST_AGE column in the CUST_CUSTOMER table and assigns the average value to a variable. The example also uses the file write operator to write the value of the variable to a file.

Figure 1. Control flow that uses a select into operator

Figure shows a control flow that contains a select into operator.

Procedure

To define a select into operator in a control flow:

Drag a select into operator to the canvas.
Define the properties of the select into operator:
1. On the General page, select the GSDB database connection.
2. Click the SQL statement tab.
3. In the SQL statement field, type the following SQL statement:
```
SELECT AVG(CUST_AGE) FROM GOSALESCT.CUST_CUSTOMER
```
4. Click the Output Values tab, and click the ellipsis (...) button.
5. In the Variable Groups list, select the GRP variable group.
6. In the Variables table, select the AVG_CUSTOMERAGE variable, and click Select.
Drag the file write operator to the canvas.
Define the properties of the file write operator:
1. On the General page, click the Specified file option to write to a user-defined file.
2. In the File name field, click the variable icon, select Use Fixed Value, and then type C:\Program Files\IBM\ISWarehouse\logs\Demo_SelectIntoEg.txt. The file write operator writes the integer value that is contained in the variable to this file.
3. In the Write to file by list, select Overwriting text in existing file.
4. In the Text field, click the variable icon, click the ellipsis (...) button, and select the integer variable AVG_CUSTOMERAGE. The text in the Text field must appear as: ${GRP/AVG_CUSTOMERAGE}.
These settings specify that the file write operator must write the value that is stored in the AVG_CUSTOMERAGE variable to the Demo_SelectIntoEg.txt file.
Drag an email operator to the canvas. Connect the On Success port of the file write operator to the Input port of the email operator. Specify the sender, recipient e-mail addresses, and the subject line.
Drag another email operator to the canvas. Connect the On Failure port of the file write operator to the Input port of the email operator. Specify the sender, recipient e-mail addresses, and the subject line.
Save and validate the control flow.
Note: You might observe a warning sign on the file write operator. The warning sign is displayed because the data type of the variable is an INTEGER and the expected data type for text in the file write operator is a STRING. You can ignore the warning message and continue.
Run the control flow and verify that the Demo_SelectIntoEg.txt file contains the value 33.