Using Advanced SQL Queries in Microsoft Excel / DQY files

Posted on 2007-10-15
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
    LVL 58

    Expert Comment

    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


    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

    Do I need to us MS Query? Can I use any other medium to query information via?
    LVL 58

    Expert Comment

    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

    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.
    LVL 1

    Accepted Solution

    PAQed with points refunded (500)

    EE Admin

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now