Using Advanced SQL Queries in Microsoft Excel / DQY files
Posted on 2007-10-15
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
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!