I have TDP 3.8. I am trying to create a process to run a Oracle query based on a specific field within my Excel connection file and am not sure how to join.
Where [oracle field] =[Excel field]
I’ve attempted to do via a cross connection query but the Oracle query is very complex with many tables, joins and where conditions… Any guidance is appreciated! Thanks
Yes, the cross connection query is the right tool for that. Basically you should be able to do something like:
… WHERE ALIAS1.COLUMN1 = ALIAS2.COLUMN2
where ALIAS1 is alias for the Oracle table and ALIAS2 is alias for the Excel table (sheet).
I would suggest you to use the Cross-Connection Query Builder. This will at least give you a hint, how to build the query.
If your Oracle part of the query is already complex and it is time consuming a cross connection query may not be your best option. Use Toad automation to set up a two step process. 1. Load the excel data into a table in the Oracle database (even if it is a truncate and load each time). Then join to that table for your Oracle query. I have found many times after starting a cross connection query I could create the new table, design and implement the foriegn data load automation, write the new join sql, excute it, and get the results while the cross connection query was still running. If it is a simple cross connection query you should be fine, otherwise, good luck.