Toad Data Point Automation Series Blog #14 – For each row Do….(Loop dataset Activity)

As shown in other videos, variables can be very useful in automation. In this video let’s discuss how to use variables with the LoopDataset activity. This activity allows you to take a result set and execute activities for each row data. This video is using version Toad Data Point 3.3 and the information will apply to version TDP 3.3 and above. There is a separate video for using the loop data set in TDA 2.6 – 3.2. In TDP 3.3 we have streamlined this quite a bit so please see the video that matches the release you are using.

Where would this be useful? If you have a list of regions you might want to generate a report for each region and send it out to the department head of that region. Each report would contain data only for his region.

So how do we do this? First let’s add a loop dataset activity. This activity is located in the system activities section of the toolbox. This activity takes a query as an input and when executes it put the result not into a file but into a dataset variable. Enter your SQL and a name for your dataset.

For each row of the driving query any child activities will be executed. For our example let’s add a select to file activity and use the filtering query shown above. What we do have to change is the bind variable name. It needs to use the loop dataset variable name and column. Go to the Loop data activity and fine the dataset variable name. the default name is Loop_data_1_SQL. Now change your query to bind to the row and column of the driving query. Use a colon in front of your bind variable name as shown below.

SELECT

  ADDRESS.CITY

 ,ADDRESS.STATE

 ,REGION.REGION_NAME

FROM

  QUEST_DEV.ADDRESS ADDRESS

  INNERJOINQUEST_DEV.REGION REGION ON(ADDRESS.REGION_ID =REGION.REGION_ID)

WHERE(REGION.REGION_NAME =

    :Loop_data_1_SQL.REGION_NAME)

You can also use the variable value in the name of the exported file. In this case make sure and surround the variable name with pound signs. IE: C:tempToadExport_#Loop_data_1_SQL.REGION_NAME#.xlsx

Let’s run the script and see what we get. For each row we now see a report containing only the rows for that region. Each report contains the name of the region data it contains. We probably would want to add an email but I think you get the idea.

The loop data set activity gives you the ability to generate a batch of reports based off of a driving query.