After reading a lot of posts regarding appending information into the same excel sheet, it doesn’t appear that this is something that can be done at this time. Debbie, I noticed on one of your latest postings that this was an enhancement request you were going to submit. I was going to check and see if this was submitted and if this would be a feature of a future product such as 3.6. With the ability to run the loop dataset, we would like to utilize this but allow each pull of the query to append to a single worksheet or .csv file, instead of either creating new worksheets or new workbooks. This would be extremely beneficial for us, because we utilize around 10 different databases and the loop data set could make things run a lot quicker and essentially, this would work in place of a union operation in the SQL combining the data into one worksheet.
It seems like you have had a lot a questions on it, so I hope it is some type of enhancement to come.
Thanks!
Chad King
Did you read my mind? We just added this feature and it is available in the current TDP 3.6 Beta. Please down load and try. It was only implemented for Excel.
Debbie
Thanks Debbie I’ll test ASAP. So I’m sure, it’s only available through the data export wizard, not select to file activity right?
Thanks!
Chad
Debbie, what link do I go to for the TDP 3.6 beta version?
Debbie,
I downloaded the latest release. Here’s the problem we have now. Does the export wizard allow loop variables to be used in the SQL query? Basically, before when I played with the loop dataset, I used the select to file activity utilizing the variable name in the loop dataset for the schema and it worked. When I paste this same SQL (select distinct CUNROV
from #Loop_data_1_SQL.DB#LIVFILE.CUMSTPF
Where CUSTA = ‘A’) in the query portion of the export wizard, I get the message “Schema Read Error: Object reference not set to an instance of an object.” From there, I cannot proceed. If I replace the variable information with the actual schema it lets me continue through and I do see the append option now.
This is why I asked if it was only going to be used in the export wizard, because I was worried if the loop dataset’s variable would work in the SQL of the export wizard, which it appears it cannot do, while the select to file can. If the select to file activity is the only activity that can work using the loop data variable, could it be updated to use the “append data” function?
Thanks!
Chad
The export wizard can use true bind variables that use the colon syntax (:varname). You seem to be using the literal syntax (#) and this is not supported. If you change your SQL to use the column and just name the variable name to match you should be good.
define var a in automation script.
set value of a to [tag:datatable].column#
select * from table where col = :a;
Debbie,
I’m able to get a set variable to show the variable being created from the loop. I’m still having a hard time getting even this variable to work in the export wizard. So for example, I have the variable created in the loop called Database. So if the Query used in the data export wizard is select * from CHADFILE.Table1, how would I use that variable to resolve the “CHAD” part? Like I need it to essentially say :DatabaseFILE.Table1 and resolve that variable each time from the loop. Again if I was using the select to file activity, I would say select * from #loop_data_1_sql.Database#FILE.Table1 and it works perfectly. I need it to very similar to this. I hope it can work, if not, the only solution I can think of is to enhance the select to file to utilize that append feature since we know it already resolves the variable like we need it to.
Thanks!
Chad,
What database are you connecting to (Oracle, SQL server, Sybase)? Oracle does not let you use variables for Table/Column names, not sure about the others. You have to trick it by building dynamic SQL and executing it. Take a look at this:
stackoverflow.com/…/dynamic-sql-passing-table-name-as-parameter
Exec('SELECT * FROM ' + @tableName)
Use :variable instead of @variable Hope it helps.
It’s an ODBC connection that queries an IBM DB2 database. I find it interesting that the export wizard will not allow the loop variable, but the select to file does. With that being the case, we’re perfectly content with using the select to file action to do it, but would like to have the append feature added to it so we can utilize this loop process to populate a single worksheet. Chad
I like the select to file also, works better than the wizard for Excel files, wish it did pipe delimited and other features of the wizzard like append. You can write out to a csv file and then call run program and use a DOS batch file to append the current file to the main file inside the loop passing the argument [tag:variable]# to the batch.
You might check the 3.6 beta regarding the pipe delimiting. They just updated it for us to use the " delimiters and I bet you could use | as well if you chose too, so that may be an option that can now be done. Hopefully we can figure out the way to get the loop variable to work in the export wizard, or they can set up the append feature on the select to file. We are the same, the select to file is much quicker and simpler for a large amount of our reports.
Chad
Hi, I was going to see if there was any decisions made on this issue?
Thanks
Chad
What version are you using? Bind vars do work with the export wizard in TDP 3.5 but only BIND with colon (
not literal replacement (#).
Here is example.
Loop SQL:
select region_id from region;
Dataset var name = Loop_data_1_SQL
Set Var:
a = #Loop_data_1_SQL.region_id# )
Export Wizard SQL:
seledt * from address where region_id = :a
Debbie, I just cannot get that to work. Again, I need the library changed. I get where you set up the variable on the loop data set and I even used a log to see that it’s switching through the libraries, but don’t understand how I code for this bind variable referencing the library name. We don’t have one table with multiple “ID’s” to reference, rather we have multiple libraries with the same tables to reference. Therefore for example, say I have two libraries used on the loop data set variable called Library1.Table1 and Library2.Table1. And say the bind variable is LIB. I’d like the query to say something like select * from :LIB.Table1. This is where its having difficulties and wont even allow it to move forward cause its not recognized for the template. Again if I use the select to file activity, I can do just literally use the bind variable itself. Lets say its also called LIB.LIB. Therefore the query works just fine when I say select * from #LIB.LIB#.Table1 and it works exactly like we’d like. Maybe I’m still not getting you and this is still something I can do in the export wizard. If you could just possibly show me how this might be worded for that, I’d appreciate it. Lastly, this is why I was saying if this type of thing couldn’t work for the export wizard, what would it take to allow the select to file activity to utilize the append feature, because we like the select to file activity just as it is and the append feature for combining all the libraries into one file would work perfectly for how we conduct our business.
Can you send me your automation script and all dependent files. I will take a look. debbie.peabody@quest.com
Debbie, I just emailed you the dependent files and the automation script. Hope it helps. Thanks again.
Chad
I can reproduce error and have entered QAT-3203 to fix.
Chad,
Have you tried using the Exec SQL command? You should be able to pass in the Toad variable say #VarTable# as :VarTable in your sql in the export wizzard. The only problem is that sql does not like variables as table names. So you put the entire SQL statement into an Execute command which treats the sql statement as just string and then you can concatonate the :VarTable variable in that sql string. Then the Execute command will parse the entire sql string, variable table name included, and run it.
