Sorry, I see now. I thought you were generating reports on a periodic basis and that the reports (and the regions for which they apply) were known in advance. In your case the report generation has no advance knowledge of which report will be generated, but if the conditions are met to generate a report then the region may be any one of them and only known after the fact?
The Export Dataset action has no support for output variables so you cannot extract the region from your dataset query to use downstream. I'm coming up short on a different solution that doesn't require the Export Dataset to either accept Query Iterator variables in its SQL or for it to support output variables.
1 Like
If the region comes from a report generated by a query, then can't a similar query be used in the query iterator?
Hi John,
I did try to put the second query iterator after export dataset. But no luck yet. Could you suggest the right way to set it up?
I was thinking you could modify the query in the existing iterator (not add a 2nd one) so that it returns rows that you'd want to send the report to.
But without knowing or having access to your data, I can't write the query for you.
If you need to pass a variable from your query iterator into your export dataset action, you can do it like this. If it's a string value, you may need single quotes around the substitution variable in the SQL
Hi John,
I do have the set parameters button but it’s not responding..
I’m using Toad 17 (should be the latest version already?)
Do you have a substitution variable defined in your SQL? The Set Parameters button won't do anything if there is no parameter to set.
Hi John,
I forgot to define substitution variable first.
Now I’m able to set up the variable.
However, it returned no data ( but I’m sure there’s data in my result). I also put % signs next to query iterator variable. Could you suggest where may go wrong?
Take the % signs out of the "variables" dialog. You don't need them there. Also, you can always use main menu -> database -> spool sql -> Spool to screen to see the SQL that Toad is running (to make sure your variables are getting substituted the way you expect them too)
1 Like
Hi John,
It’s working now! Thank you so much for your help!
1 Like
Hi John,
I was wondering if there’s anything else I need to consider about the substitution variable setting.
Since I used the same way to set up the substitution variable in different apps, only some of the values were substituted ( most of the time were not working). Thank you for your patience for helping me out!
Hi Irene,
Can you provide an example of a case where a variable doesn't get substituted?
Examples and screen shots are helpful.
FYI, I have this publicly available dropbox folder with several documents I've written about automation designer. You may find them helpful.
-John
Hi John,
I have several set of queries need the variable setting. I noticed that when there are more than 3 tables join together and with sub query, the variable is not substituted. I checked your Dropbox already, it’s very helpful( thank you!). But unfortunately I was not able to figure out the variable setting in some of my queries.
Hi Irene,
I'll be happy to help, but please make it easy on me. 
Send me the SQL Text (not a screen shot) and CREATE TABLE statement so I can run the SQL. I don't need any data in the table. If you prefer to not post this info, you can email it to me at john.dorlon@quest.com.
-John
1 Like
Thanks for the email.
I couldn't get the variable working here as a substitution variable when the variable is inside of quotes like that (It's a bug, see update below). It does, however work as a bind variable. Try this:
Update: I've discovered that if you have substitution variables and you make any edit to the SQL in the Export Dataset dialog, then you need to click "Set Parameters" again so Toad can resync them. Otherwise, they just don't get substituted. I'll make a change so that this is more obvious.
1 Like
Hi John,
Thank you so much for looking into it.
But I did set parameters again after the change, no difference being made.
I also tried the bind variable setting.
It looks like the variable is being substituted in Spool, but didn't retrieve any data.
Is it possible?
If it's being substituted in the Spool SQL, then that is the query that Toad is executing.
So if data is not being returned, then either
a) your SQL is wrong
b) the tables do not contain the data that you expect
c) maybe the data hasn't been committed yet.
You can run the grab the SQL from Spool SQL, then run in the editor to experiment.
Forgot to mention, I did run the query in editor, it returns result from my query.
That's why I find it really odd in automation designer.
I know you've got a query iterator driving an export dataset. So when you say "no data was returned" which one are you talking about?
Does Spool SQL have the Row Count option enabled, so you are sure which SQL is returning rows and which isn't?
Also, Are you sure the Editor and Automation Designer are running on the same connection?
Hi John,
I sent you the screenshots of Spool through email.
There was no data returned from the export dataset.
Also, row counts were enabled.
The Editor and Automation Designer were indeed running on the same connection.
Thanks!
