Step 9: Defining More Complex HTML Batch Jobs That Extract Parent-Child Data

In this step you are going to specify a more complex export job. Data stored in a parent-child table constellation is to be exported. In order to get this done you need to define new external sets, a new export task and a new job.

To define an external set that extracts data from the TOUR (parent) table:

1.	In the Definer application choose Define, Sets, External Sets from the menu.

2.	Type "STEP9_TOUR_ESET" in the Set Name field.

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

The SQL Definer (a specialized graphical editor for SQL statements) appears.

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

5.	Expand the Tour node on the Repository tab of the catalog.

6.	Drag the Destination, Type, Maximum Age, and Number Of Days columns from the Repository Catalog onto the SQL tab page displayed at the bottom of the window.

7.	Choose Statement, Save from the menu to transfer the SQL text to the calling window. Close the SQL Definer window.

8.	Save your work.

9.	Check HTML Set, click the HTML Set button, and then choose List from the Layout Style dropdown list.

10.	Place the cursor on the external set record. Choose Special, Compute Elements from the menu.

External set elements are computed based on the underlying SQL statement, and displayed in the child box.

11.	Save your work again.

To define another external set that extracts corresponding data from the SCHEDULED_TOUR (child) table:

You can take the statement you used for the SCHEDTOUR table in Step 6, but this time you want a join with the TOUR table, you want the PRICE column as well, and you don't want the DESTINATION and TOUR_TYPE columns because they already appear in the parent table:

1.	Retrieve the External Set you defined in step 6.

2.	Make a copy of this record by pressing DOWN ARROW when focus is on the STEP6_ESET External Set record and clicking the New and Duplicate buttons on the ribbon bar.

3.	Change the Set Name of the copy to "STEP9_SCHEDTOUR_ESET".

4.	Choose Special, Compute Elements from the menu, and save your changes.

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

6.	Click on the Column Definitions tab page at the bottom.

7.	Click on the DESTINATION column, click the right mouse button and then choose Remove. Repeat the procedure to remove the TOUR_TYPE column.

8.	Drag-and drop the PRICE column from the window in the From List pane (top right area) onto the Column Definitions tab. By doing this you add this column to the query's output list.

9.	Drag-and-drop the Tour table from the Query Catalog onto the From List pane.

Note the relationship lines drawn between the table windows. Explore the statement on the SQL tab page at the bottom. This statement now contains a join (represented by the RELATE keyword).

10.	Choose Statement, Save from the menu, close the SQL Definer window, and save your work.

The SQL Statement you just built should look like this:

SELECT "Scheduled Tour".START_DATE

, "Scheduled Tour".RETURN_DATE

, "Scheduled Tour".MAX_PART

, "Scheduled Tour".PRICE

FROM SCHEDTOUR "Scheduled Tour", TOUR Tour

RELATE Tour "IS SCHEDULED AS" "Scheduled Tour"

11.	In the External Sets window, check HTML Set, click the HTML Set button, and then choose Table from the Layout dropdown list.

12.	Choose Hyperlink from the Navigation Style dropdown list.

13.	Choose Special, Compute Elements from the menu.

14.	Save your work.

To create a child set entry linking parent to child data:

1.	Retrieve the STEP9_TOUR_ESET External set definition in the External Sets window.

2.	Click on the fifth record (empty) in the Sequence field, then type 5.

3.	Type "RELATED_SCHEDULED_TOUR_DATA" in the Element Name field.

4.	In the Child Set field, type "STEP9_SCHEDTOUR_ESET", or choose this value from the lookup window.

5.	Save your changes.

To create an export task:

1.	Choose Define, Export Tasks from the menu.

2.	Type "STEP9_ETASK" in the Export Name field

3.	Type "STEP9_TOUR_ESET" in the External Set field (the set containing a child set).

4.	Type "TourScheduledTour.htm" in the Filename field, then save your work.

To create the batch job:

1.	Choose Define, Jobs from the menu.

2.	Type "STEP9_JOB" in the Job name field.

3.	Type "STEP9_TASK" in the Task Name field.

4.	Type "1" in the Sequence field.

5.	In the Export field, type "STEP9_ETASK" or choose this name from the lookup window.

6.	Save your work.

7.	Validate the job.

Next Step

Step 10: Running the Job and Viewing the Result