[Last Call] Learn how to a build a cloud-first strategyRegister Now


Using Advanced SQL Queries in Microsoft Excel / DQY files

Posted on 2007-10-15
Medium Priority
Last Modified: 2013-12-07

I have been creating SQL queries in MS Excel using the .dqy extensions created when I go to Data > Import External Data > New Database Query or Edit Database query. As I've been successful till date with my SQL code, the issue is as follows:

I have created a new SQL query as shown below using Oracle Toad. Normally, i'll write my query in oracle toad and copy/paste into either MS Query or insert it directly into the VBA editor.

select ltrim(sys_connect_by_path(fname || ' ' || lname,'<--'),'--<') , stud_id
from pa_student
where emp_stat_id  in ('A','L','P')
start with stud_id ='10189768'
connect by prior stud_id = super

As you can see, in the code, I am utilizing connect by prior and sys_connect_by_path functions to query and retrieve a heirarchal structure from pa_student. How can I take the exact same code and use it in MS Excel? I need to get this done urgently as senior management requires this on a monthly basis running as a batch process.

From my understanding, this is PL/SQL and I am not sure how to incorporate this into VBA. Please assist!
Question by:piyushdabomb
  • 3
  • 2
LVL 58

Expert Comment

ID: 20082777
You will not be able to paste that into MS Query. As far as I know, not a single MS product performs hierarchal queries, let alone poor simple MS Query.

If at all possible, you will have to ask the Oracle engine to perform it. Can you create a query / view in Oracle and connect to that instead of connecting to the raw table? This is what I do when I need some special SQL construct that Jet can handle, but not MS Query. By connecting to the query, I move the load to the better engine.

If that is not possible, you will have to create a temporary table in Oracle, and use that to retrieve the data in Excel.

Good luck!

Author Comment

ID: 20082841

The problem is that since the server is production, I can't TOUCH the database at all whatsoever!! With that being out of the picture, what else can we do?

A couple questions to follow up:

> Where can we get a list of functions MS Query can and can not do? I cant seem to find one but if I have that, i can work with what is possible in MS Query.

> What sort of SQL is MS Query?
> Is "connect by prior..." PL/SQL?

Potential solutions I am willing to incorporate:

> If I can use asp to connect to the database, I am sure I can retrieve the results into an output. Could I potentially use the results and import them into MS Excel?
> Can we incorporate any add-ins?
> If I use Oracle toad to connect, could I somehow use the .sql file and make 'excel' run that .sql file and somehow take the retrieved results into excel? I know I can "save as" in oracle toad to an excel file.

Author Comment

ID: 20082848
Do I need to us MS Query? Can I use any other medium to query information via?
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 58

Expert Comment

ID: 20083079
You should probably ask this question in the Oracle topic area. From plain Excel, MS Query is all you get. I'm sorry I don't have a handy reference, you can try searching the microsoft site:
but nothing spectacular popped up.

You will have better luck with ADO from VBA. I'm quite sure you can push a true PL/SQL query and just retrieve the records, which you can then use to populate a worksheet. If you have Access, you can first experiment with linked tables from Oracle and various queries. Once something works, you can get rid of Access and just use ADO.

And yes, "connect by prior" is sadly missing from the few SQL engines I know...

Sorry I don't seem to be able to help you further. Perhaps a pointer question in the Oracle topic area to this one?

Good luck!

Author Comment

ID: 20511651
I've figured it out. If you use the VBA connect queries function, its easy to connect using any SQL function. I'll be more than happy to share the function and sample code snippets if anyone needs it. Let me know.

Accepted Solution

Computer101 earned 0 total points
ID: 21288078
PAQed with points refunded (500)

EE Admin

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question