Summary
This tutorial teaches you how to use the Loop Dataset activity to loop through rows in a result set and perform a task on each row.
Using the Loop Dataset activity requires an understanding of how to use variables in Automation. It is recommended that you read the Automation and Variables tutorial prior to reading this tutorial.
In this tutorial you will learn:
You will need:
Basically, the Loop Dataset stores a result set in the dataset variable using a query you provide (the driving query). Then, for each row in the result set, it performs the activities that you assign to the Loop Dataset. You can assign any activities from the Automation Toolbox.
As an example of when to use the Loop Dataset activity, suppose that you have a list of departments and you run a report for each department and then distribute each report to the department manager.
For this tutorial, however, let's assume you want to create a report for each region in the REGION_NAME column. You will use the following driving query to return a list of region names and use this to populate the dataset variable.
SELECT REGION_ID, REGION_NAME FROM REGION
Then, you will instruct the Loop Dataset activity to execute the following SQL statement each time it loops through the driving query's result set stored in the dataset variable.
SELECT ADDRESS.ADDRESS_ID, ADDRESS.CITY, REGION.REGION_NAME
FROM ADDRESS ADDRESS
INNER JOIN REGION REGION ON (ADDRESS.REGION_ID = REGION.REGION_ID)
WHERE (REGION.REGION_NAME = :CURRENT_REGION)
Click the Loop Dataset activity in the System Activities category of the Toolbox to add it to the Automation design window.
The Loop Dataset activity uses a query as the input and then saves the result set to the dataset variable. Note that this activity does not automatically create an output file.
In the Activity Input tab, enter a SQL statement (or select a SQL file) to use as the driving query. After the script executes, the result set of the driving query is stored in the dataset variable.
SELECT REGION_ID, REGION_NAME FROM REGION
Enter a new name in the Name field. To follow along, enter RegionsDataset. Click the Activity Input tab. Notice that the dataset variable name changed to match the activity name.
Click
The purpose of the script is to create a report (for each region in the REGION_NAME column), so you need to add this task to the loop. You will use the Select to File activity to execute the original query containing the bind variable and generate a report.
Now click the Select to File activity in the Toolbox to add it to the Loop Dataset activity.
In the Activity Input tab, enter the original query with the bind variable. The name of the bind variable in your query must match the name of the dataset variable. In this tutorial that name is RegionsDataset_SQL. In addition, you want to select the value in the REGION_NAME column of the dataset variable. So use the following as your bind variable: RegionsDataset_SQL.REGION_NAME .
Note: The bind variable must use bind variable syntax.
Click
By using variables and the Loop Dataset activity, you can generate a batch of reports based off a driving query.
See Schedule Your Script to learn how to schedule the Automation script.
© ALL RIGHTS RESERVED. Feedback Nutzungsbedingungen Datenschutz Cookie Preference Center