Join (INNER JOIN) local Excel table in Oracle SQL Query - VBA

0
votes

I'm having trouble performing a query on a remote Oracle SQL Server via Excel VBA while trying to perform an INNER JOIN in the same query with a local table in a Excel file sheet. Example:

  • Excel Sheet with local table ["LTE_Cells$LTE_Cells_Tmp"]:

Sheet "LTE_Cells"

Oracle SQL Query

SELECT  a.STARTDATE, a.ENODEB, a.EUTRANCELLFDD, (a.COUNTER_1/8/1024)+
a.COUNTER2/8/1024) AS Total_Total_Traffic_TB FROM »»»LOCAL_EXCEL_TABLE«««« 
INNER JOIN REMOTE_DATABASE.LTE_KPI_1 a ON  
((»»»LOCAL_EXCEL_TABLE««««.EUTRANCELLFDD = 
REMOTE_DATABASE.LTE_KPI_1.EUTRANCELLFDD) AND  
(»»»LOCAL_EXCEL_TABLE««««.ENODEB = REMOTE_DATABASE.LTE_KPI_1.ENODEB)) WHERE 
(((REMOTE_DATABASE.LTE_KPI_1.STARTDATE)>=sysdate-3));`

Thanks in advance for the help!

1
sqlexceloraclevba

1 Answers

0
votes

This doesn't answer your question directly, and I may be all wet on this but I don't know that either Excel or Oracle explicitly handles what you are trying to accomplish.

However, MS Access will out of the box. Short answer: I think you are using the wrong tool for this task. You are using the proverbial hammer to saw a board in half. Link the spreadsheet and the Oracle table as linked objects in Excel, and your query should be easy-peazy.

Longer answer: while Access does this simply and easily, it can and probably will leave a path of destruction behind it on the DBMS. Specifically, you can expect to thrash the shared pool in Oracle, as Access will be issuing one query (using literals, no less) for every line in Excel. For 1,000 lines, it probably doesn't matter that much, but if you're going to do this on really large datasets, you will make a fast enemy out of your DBA.

Extended answer: really, the best way to do this is to load the contents of those Excel spreadsheets in Oracle tables and let the DBMS do the heavy lifting. This is bread and butter for the RDBMS.