Step 11: Defining a More Complex HTML Batch Job That Extracts Calculated Data

In this step, you will produce an HTML report with a calculated column. For each scheduled tour, the report shows the number of places available and the number of places already booked. In each case, the total of places booked is the sum of the NUM_PART values of child records in table Reservation.

To export calculated data:

1.	Choose Define, Sets, External Sets from the Definer menu, and type "STEP11_ESET" in the Set Name field.

2.	Click the SQL Definer button next to the SQL Statement field.

3.	Accept the default in the dialog window (Select statement) by clicking OK.

4.	Expand the Scheduled Tour node in the Query Catalog.

5.	Drag-and-drop the Destination, Type, Start Date, and Capacity columns from the Query Catalog onto the SQL tab page at the bottom of the window.

The corresponding columns are placed on the output list of your statement.

6.	Expand the Reservation node in the Query Catalog.

7.	As a basis for the computed column, drag-and-drop the Num Part column from the Query Catalog onto the SQL tab page at the bottom of the window.

Your statement now contains a join with table Reservation.

8.	On the Column Definitions tab at the bottom of the window, edit the Column Definition value of the NUM_PART column by adding the SUM function, so that it reads:

SUM(Reservation.NUM_PART)

9.	To produce the correct GROUP BY clause, you need to group by the four non-computed columns in the output list, making sure the columns appear in the right order.

Drag-and-drop the Destination, Type, Start Date, and Capacity columns from the Scheduled Tour table onto the Group by Definitions tab page at the bottom of the window. Enter sequence numbers 1,2,3,4 and 5 in the Position field on the Column Definitions tab page and numbers 1,2,3,4 in the Position in Group By field on the Group By Definitions tab page.

Navigate between the SQL, Results, Column Definitions, and Group By Definitions tab pages until you are satisfied that you have produced the desired statement retrieving the desired results. One of the Island Suntanner tours to Australia is overbooked by 20 %. There is a business rule specifying that tours may be overbooked by a maximum of 20 % of places available.

10.	Save your work.

Here is the SQL statement:

SELECT "Scheduled Tour".DESTINATION

, "Scheduled Tour".TOUR_TYPE

, "Scheduled Tour".START_DATE

, "Scheduled Tour".MAX_PART

, SUM(Reservation.NUM_PART)

FROM SCHEDTOUR "Scheduled Tour"

, RESERVATION Reservation

RELATE "Scheduled Tour" "FOR WHICH ARE MADE" Reservation

GROUP BY "Scheduled Tour".DESTINATION

, "Scheduled Tour".TOUR_TYPE

, "Scheduled Tour".START_DATE

, "Scheduled Tour".MAX_PART

11.	Select the HTML Set check box for this external set, click the HTML Set button and set Layout Style to Table.

12.	Place the cursor on the external set record and choose Special, Compute Elements from the menu bar.

External set elements are computed on the basis of the underlying SQL statement.

13.	Rename the external set element for the last column by changing its default name ("C5") to "BOOKED". Save your work.

To define an export task and a job:

The procedure is the same as in steps 6 and 9.

1.	Create an export task named STEP11_ETASK and associate it with the STEP11_ESET external set. In the Filename field, type "ComputedColumnStep11.htm". Save your work.

2.	Create a batch job named STEP11_JOB. Within this job, specify a task named STEP11_TASK with sequence number 1, and associate it with the STEP11_ETASK export task. Save your work.

Next Step

Step 12: Running the Job and Viewing the Result