linked server query on DB2 and excel file via TOAD Data Analysis?

Toad Data Point & Toad Intelligence Central
1

Using Toad Data Analysis 3.0. I am connected to a DB2 database via ODBC which I have limited (mostly read only) rights to. I also have an Excel file with a subset of about 700 part numbers listed. The task is to look up a count of these part numbers that were returned during a date range. Both my ODBC and EXCEL have open connections and I can see/query the local data with no issues.

The SQL is fairly straight forward, but I don’t want to have to copy/paste each part number to get the count. Also, since I don’t have write privledges, I cant import the excel file to a temp table and do a WHERE table1.partno IN temp.partno.

I tried to export my Excel connection info to a Toad XML file, but when I try to import this connection, the add/remove/next buttons are greyed out and I can’t go forward (see attached image). Also, I DO NOT have an instance of SQL server on my machine…so not sure why this shows up under selected server/Groups.

Is there a way to do a linked server query? Something like…

*** USING ODBC CONNECTION ***

SELECT DISTINCT t1.partno, count(*)
FROM table1 t1
WHERE t1.partno IN [EXCEL].[ExcelFile].[Sheet1].[PartNumber]
GROUP BY t1.partno

If so how? Please be detailed, since I am new to TOAD. Thanks!

Toad Import Server.jpeg

2

BTW, I did read up on this, but want to clear up some confusion on my part. Thanks!

http://toadfororacle.com/thread.jspa?threadID=35137

3

Why not use the cross-platform query builder to build the appropriate query?

4

It is not letting me drop the sheet1 ‘table’ into the Diagram window. I can drop tables from the ODBC connection…just not my excel sheet.

Message was edited by: jondspen

5

I also tested pulling in an Access table to a cross-connection query, and got the same thing. Is this normal, or should you be able to pull in Excel and Access tables to a cross-connection query. If it is normal, is there a work around? Perhaps loading MySql, import the Excel to a table, then CCQ against my data store and this temp table with my subset of part numbers?

Thoughts anyone?

6

You can join Db2 and Excel in the same query using Cross-Exceution query in the pro edition. This is the newly written cross-conneciton engine. I just verified this. Are you using the Pro edition?

The Base edition uses the old method and does have some restrictions. One of the known issues is that DB2 is not working properly (Cr91605). I suspect this is what you are running into. Can you download the Trial and give it a try?

Debbie