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!