Toad World® Forums

Loop Dataset Function Not Switching Variables


#1

We are trying to utilize variables in the loop dataset to either loop multiple criteria or database names. The loop dataset appears to work only for the first variable listed in the code of the query. For example, if we have a spreadsheet we use with multiple statuses or database names, we will tie that in as the loop data. So for example, I have 10 database names that need to be used in the query, which should produce 10 different outputs with 10 different database names. When trying this, it only finds the first database name, does work correctly and outputs as it should, but the other nine, also use only the first variables information, so it’s essentially duplicated 9 more times instead of switching to each new db name. I’m going to attach the log file to show what it is doing. It appears to be going to the new row, but for some reason not switching the DB name. We had some trainers in our office yesterday that were running into the same issue, so not sure if this is a known issue or not. This could be an extremely important function if used the way we think it can be in our company and would love to see if we’re either doing something wrong or if there’s something wrong with the loop dataset function. We’ve tried on both 3.4 and 3.5.

The SQL script that is used in the loop is:

select AutoRange_Databases.DB

from

AutoRange_Databases AutoRange_Databases

This is a the variable query which is calling it the dataset variable Loop_data_1_SQL, which looks simply at an excel sheet called Databases that looks like this below:

ETX
GOK
ARK
ALE
WTX
EUR
NCA
WVA
CEN
NAT
The Export function then just looks at the variable below and runs the query:

select distinct CUNROV

from

#Loop_data_1_SQL#LIVFILE.CUMSTPF

Where

CUSTA = ‘A’

Again, I’m attaching the log file that shows how it shows to switch to each number of the row, but somehow is not switching the variable name and only using the top variable. We’ve tried this in a bunch of different ways and have the same problem.

Any help you could give would be appreciated.

Chad King
Script_DB_Variables.log (38.2 KB)


#2

Please disregard! I was missing the column name and apparently that HAS to be there on the variable to make it switch. When I added this, the loop did the switching to each DB name. Sorry for the confusion. Should have watched the tutorial video further! :slight_smile:

Chad


#3

Thank you! I was making the same mistake. Had just seen the Help file and not the video assuming they were the same, but in the video Debbie clarifies the use of the column!

Thanks again.