July 7, 2014, 10:35pm
I’m almost a newbie in Toad data point, and i need your help to build an automation.
I’ve a Toad report (.tdr) with a query of the type " select XXX,YYY from schema.table where XXX= a variable".
I woul like to create an automation with a loop dataset that will execute and save the report for each value returned by the Loop dataset query as XXX in the report query.
How can i link the variable in the report’s query to the variable generated by the query of the loop ?
Thanks a lot for your help.
July 9, 2014, 5:34am
As a complement :
In my Toad Report’s query i have a clause like “where table.column= :myvar”
“myvar” is the name of loop activity variable.
So normally i would write in the report’s query “where table.colmun= :myvar.column” but i got an error with it.
My loop query return 1 column, 15 rows.
If i write “where table.column= :myvar” in my report’s query the loop will correctly proceed 15 times but the report will always take the first result (first row of the loop’s query results).
You need to set the variable inside the loop. You can create/initialize the varriable outside the loop but set its value inside the loop. Then use the variable in your query. I have attached an example 3.5 version automation setting a variable inside a loop creating 3 excel spreasheets pulling a different row from a table each time based on the MyVar variable.
Example.tas (12.6 KB)
July 9, 2014, 11:06pm
Many thanks for your answer, it works good !!! I didn’t had the idea to combine a variable with the variable of the data loop.
Thanks, thanks, thanks …