I created a custom query using Microsoft Query to query a schema on an Oracle 10g database. The query works fine in SQLPlusW and in MS Query. But when I try to run the query from Excel (Data->Get External Data->Run Saved Query), I get an error from Excel that "MS Excel could not open or read this Query file. Either the file has been damaged or the file format is not valid." Anybody got a clue? Like I said, if I open the .DQY file using MS Query, it executes just fine. It is a bit complex query using right outer joins and dense_rank() etc., but I am extremely doubtful if Excel cared about that since it only needs data returned from MS Query. Any help is appreciated.
I have had similar funnies with complex queries in Excel. Mine I couldnt even get into MSQuery, but I could run it through PL/SQL developer. Try creating the query as a view on the database and use a simpler sql select from Excel. That worked for me...
Thanks for your reply. The problem is that the query is dynamic and that would mean I will have to recreate the view everytime the query changes (not just the where part, but the no. of columns selected as well). And I don't want to give any other access other than SELECT to the users using Excel or other tools. They can select from SOME table(s), but not create or replace tables views etc.
But yes if I create it as a view, I am able to select from Excel.